Microsoft's version of build-your-own-database and CrudScreen''''''s in a box. Far inferior to their barely advertised VisualFoxPro database. * I agree * ''You have to admit though that Access is quicker to learn for newbies used to a click-and-drag mouse world. (Quick-to-learn and productivity are not necessarily the same things, I would note.) VisualFoxPro has so much baggage from vendor changes in direction over the years that it could be daunting for newbies. The flip side is that there are multiple ways to do the same thing, depending on your preference or best-fit-to-task.'' Pretty good for building standalone apps - you can force it to do some things, but you'll probably regret it. (For example, ever try right-aligning numbers in a multicolumn listbox? That was "entertaining"...) -- DanNovak ''Has anyone found a good way to get Squeak to let one use Access files? I have an existing database that I need to add features to, and I wanted to be able to prototype those features in something more flexible than Access, like Squeak, or Lisp. But, since those appear to hard to use with Access, maybe I'll just stick to Python. Not as easy to use, but it does support COM controls.'' While it still under development, there is an ODBC project for Squeak in the works (http://minnow.cc.gatech.edu/squeak/2480). Dolphin Smalltalk has an ODBC extension already. As for Lisp, Allegro CL has full support for ODBC, COM, OLE, etc., and has a solid FFI that you can use to adapt most Windows libraries (or at least that is my understanding). If you have any interest in Scheme, DrScheme has add-on support for COM, ODBC and embedded SQL, while Bigloo is said to work well with most C libraries. -- JayOsako ---- Just don't try multiuser access with it unless you're actually accessing a real database behind the scenes. Too many companies have tried to sell a product with an Access backend, and most are now reselling MicroSoft's SqlServer as an option to try to make all the bad data corruption and etc. go away. ''Why do you think MS does not fix it? They are mostly just competing with themselves, so they "encourage" you to go with the more expensive product.'' Except versions of SqlServer that target user loads that match what Access is capable of are free - so by that measure ''Access'' is the more expensive product. ''If you mean Sql Server Express, you are partly correct, but for non-trivial projects it has some annoying limitations, such as difficulties in backing up and exporting/importing. An MS-Access file can be backed up just like any other other file, but in the SQL-Server line of products, the data files are often locked such that a file-backup system cannot make a copy. I find that MS-Access has this lock issue far less often for some reason. One common way of making backups in mid-range RDBMS to avoid locks and mid-change pointer corruption[1] is to make a dump file(s) of all tables with record INSERT statements or a CSV file, and then back up the dump like any regular file. However, the Express edition lacks such mechanism. (If MS has since changed this, I am not aware.)'' ---- We encountered that a while back on another project - apparently Access' Memo types gets corrupted quite easily in multiuser environments. -- DanNovak ---- And be careful about the Access 97 ==> Access 2000 transition. Tables with more than about 20000 records tend to corrupt the setting of Autocount-columns. Produces interesting errors if you expect this to be unique. -- HelmutLeitner ---- All of the multi-user problems described above are real, but Access is still by far the best tool out there for many common multi-user database scenarios. I've used MS Access on and off since version 1.1, mostly for small workgroups, and serious data corruption and performance problems were the exception, not the rule. The one shop that had really serious problems was trying to support 50+ simultaneous active users. If the cost of data loss is really high, you can use one of the many open-file backup solutions, and run backups multiple times per day. The main advantage to using Access/JET is that there is no IT infrastructure to install and to maintain beyond a file-system share, any novice can make a backup or snapshot simply by copying the file. A non-technical manager can figure out how to take a copy home at 8PM without having to get expert help, etc. I cannot overstate the benefit of this kind of simplicity! The suggested solution of using a database server behind Access is good one, and I've done that in cases where it was called for, but be aware that an Access UI design that works well with a JET back-end will not usually work well with a server back-end, and a naive attempt to just swap out the storage system frequently results in an actual performance decrease, along with multi-user contention problems and deadly embraces that the server can't automatically resolve as it can with ordinary deadlocks. If you aren't comfortable using a database profiler and query analyzer, this is not your best strategy. Also, although Access can be made to play nice with non-MS back ends (e.g. PostgreSQL), not everything just works, so you may have to engineer some work-arounds. -- SteveJorgensen ---- * Does Microsoft Access support commit/rollback? (i.e. proper transactions) * Exactly what support does access provide if any for multi-user environments? * How do other people (i.e. not Microsoft) handle easy-to-deploy, minimum maintenance (hopefully zero maintenance) database systems? I have to admit that one of my major hangups about Microsoft products is the documentation. I just can't get used big, puffy books with 1.5" margins, silly quotes, and (worst of all) assume that we don't know how to program. Can anyone recommend a decent book on Access? The O'Reilly book [ISBN: 1565926269] looks decent, but I haven't seen it yet in a bookshop. ---- '''On Access' transaction support:''' The JetDatabaseEngine (what Access databases are built on, usually) supports transactions, but you can't do it with just SQL. You have to use a DataAccessObjects (DAO) Workspace object, and use methods such as .BeginTrans , .CommitTrans, and .Rollback (from memory, these may not be the right method names). Suffice to say, it's rather tedious... '''On books:''' Out of all the books I've seen, anything in the "Developers' Handbook" series by Sybex is the best. In a nutshell, you'll only find stuff you ''don't'' usually find in the documentation, help, or other books. The most dog-eared book in my collection is the "Access 97 Developers' Handbook" by PaulLitwin, KenGetz and MikeGilbert. [ISBN: 0782119417] If you do any work with Access, I highly recommend this as it covers most everything you could possibly need. I've only glanced at the 2000 edition, but with 2 volumes each over 1000 pages, you'll probably never need anything else . (by the way, how does one properly cite books/authors in Wiki?) '''On Access in multiuser environments:''' You can, but as I'm slowly finding out - it is even less fun than dealing with transactions. The JetDatabaseEngine is file-based, which means all the work must be done on the client. There's nothing running on your server that provides the data except the file server itself. What I've been trying to find out is 'what' is being sent over the wire when this happens. Is it the full database, or is Access smart enough to request the indexes and what it needs, 'then' retrieve the database if it needs it? ''Access (the JET database engine, actually) definitely only reads the file blocks it needs. There is practically no difference in how Access uses database files vs how a server process would except that each JET instance is single-client, and JET has to handle multi-user contention for the back-end file.'' If you're going to have a very small scale, in-house thing, or if it's something where performance is not important (is there such a thing), then Access is OK. ''Access/JET programmers get really tired of hearing that (note that JET and Access are somewhat separate issues). In part, what you're saying is quite true - JET is good only where (concurrency x load = small) and 24x7 is not a requirement, but it's amazing how many situations that applies qutie nicely to. I have personally seen lots of cases where people chose the server route, and ended up in far worse shape than if they'd just stuck with Access/JET (note the need to perform minor app maintenance/enhancement in-house without staff programmers).'' ''JET will not have a problem with 50 users, if they are all light-load, and it won't have a problem with 4 or 5 users hitting it pretty hard. Also, where JET is appropriate to use, it has a lower IT overhead than a database server. There there are no extra processes to maintain, and a data backup is simply copying a file.'' ''Finally, even in systems that are "large", there may be several subsystems with separate data storage requirements that are better handled with JET than with a server. JET vs Server does not need to be an either-or choice.'' The MicroSoftDataEngine (MSDE) is basically a scaled down version of SqlServer. It comes with Access 2000 and is a better choice than Jet for multiuser apps. It still has a limit of 5-15 users from what I've heard, more than that, and you really need a RealDatabase like Oracle or SqlServer. ''Perhaps in some cases, but in most of those cases, I think MySQL, Firebird, or PostgreSQL would be a better choice. Certainly, if Access is the front-end, you're better off sticking with JET unless you have a developer who's -very- good at both Access and C/S. Otherwise, you end up using creating A LOT more problems than you solve by trying to switch to a server back-end.'' -- DanNovak (in a very verbose mode today for some reason) ''Actually, the MicroSoftDataEngine *is* SqlServer with some PerformanceBrakes in it to make it less than useful for more than a few users. And it doesn't come with any of the SqlServer ClientSideTools. -- CurtHagenlocher'' I've used Access as a database for VB apps, using Active X servers running on the same machine as the database files and putting all the data acess through them. Never had corruption problems as their is only one conection to the database. -- AndyMorris ''Still, this can't be terribly scalable. Why not try something like PostgreSql?'' In short, we used Access because it was either that or Oracle, with a small army of DBAs, accessed thru tuxedo via Cobol transactions. For me, the problems with multi users access and performance over a network, combined with the desirability of keeping persistence logic separate from other layers leads to a data-access layer that runs on the same machine that has the disk with the .mdb file. You then make business objects that have a state property so you can pass the ObjectByValue in and out of Get and Save methods in the persistence layer. You then have to tackle marshalling the objects state in the state property. For simple classes with no sub (child) classes you can store the state in a user defined state and Lset it to a string. For more complex classes the property bag class comes in handy, or to make the ?messages?more readable XML works well. -- AndyMorris ---- Although I wouldn't admit this in my CV ;-), I did some Access programming, too. My impression was: productive and almost pleasant if what you want to do is simple and fits their model; quite nasty if not. -- FalkBruegmann That sounds like _every_ Microsoft product I've ever had to use. -- Rob Bain Or any other framework for that matter. Anybody remember Forms 2 on Oracle *before* they introduced PL-SQL? ---- This Access Wiki set of pages is actually quite sparsely populated. If you want to see a lot of traffic, go to the NNTP newsgroups - http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access and the http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.access Access documentation is publicly accessible on www.microsoft at MSDN. Access developers are quite concerned that the JetDatabaseEngine has been put out to pasture because the behaviour of Access (as we know it) is very closely tied to Jet. -- AnandaSim ---- ''Moved from W''''''hyCurrentAttemptsToEvolveAccessWithoutJet'' The whole issue can get quite emotionally charged because not evolving Access without Jet is not a simple matter of technology, it drastically affects the livelihood, joie de vivre of some very passionate people. Since Jet is intertwined with the .mdb format, let's discuss the new format - the .adp - which has no Jet. After some development experience with .adp, here are some points of concern: * Jet always tried to make Forms editable and Reports filterable. Even in the grey areas where Primary Keys or relationships might have at first glance, prevented this. Without Jet, the .adp let's you "fall to the floor" - there is no high level transparent abstraction - much too easily Forms record edits, filters etc... don't work anymore. Yes, there are ServerFilters but they don't work with Stored Procedures etc... * In recognition of such problems in Access 2000, Access 2002 has a thing called CDM (ClientDataManager). This resolves some issues but only a few of them. * The problem is that Jet had Queries - full stop. MicrosoftSqlServer has traditional things like T-SqlViews, T-SqlStoredProcedures, T-SQLFunctions. That means the architecture is more complex (more powerful). The End Result is that the Forms which hid all the nuances in .mdb are now unable to in .adp. The closest info I could get to understand anything about these breaks in transparency is "Microsoft Access Projects with Microsoft SQL Server" by Microsoft Press (there is a story about why "Microsoft" has to be in every name ) by Albrecht and Nicol. ---- '''Porting MS Access result set to Wiki''' I am currently working on a task to convert a MS Access result set into a Wiki page. I have a Wiki built and working on our internal network, but I am struggling with how to port the result set. Any ideas/suggestions? Thanks. -- RB ---- ''Using Access for empirical analysis rather than typical DB work'' Empirical analysts (economists in my case) often work with flat data files of numeric values - basically spreadsheet pages that can be imported or copied and pasted into a stats program. Our data often come from multiple sources and are often TimeSeries. Working with such data in spreadsheets is always frustrating because you have to * match observations from source A on date 1 with observations from sources B, C, ... on the same or different dates, * make simple data transformations (y = (a + b)/c perhaps, or lny = nlog(y)), * combine (aggregate?) monthly observations into quarters, quarters into annual, etc., and * select particular variables (fields) and dates (rows) for analysis. * ''Sorting sometimes risks putting columns out-of-sync if you accidentally don't select the entire sheet.'' I'm experimenting this summer with using Access for such work - it seems like it should be a no-brainer - but I'm having trouble getting the SQL for the arithmetical and aggregating steps to work. I have found very little documentation (with examples) on doing it, and Access's design view is not transparent to this user. Is this a DumbProgrammer problem, an Access problem, or an SQL problem? Suggestions or doc sources? Thanks! -- GeorgeBrower ''I got it - the problem was looking for answers in the wrong places. RafeColburn's ''Special Edition Using SQL'' (ISBN 0789719746) did it for me.'' -- GeorgeBrower In general, spreadsheets have poor CollectionOrientedProgramming capabilities. They are great for referencing individual cells, mediocre for single column-wise or row-wise operations, and poor for record-oriented or multi-dimensional operations. They are also poor at automated cross-referencing. ---- MS Access is not ACID compliant. Do not use it over a network if you care about your data. Having been burned badly, I would recommend using it only as a front end to a real database engine. In addition, it encourages writing a StovepipeAntiPattern so as you can guess I do not like it use it for anything. -- pjl ''Using a DesktopDatabase for critical web applications is generally a no-no. If you do it, backup daily knowing that you may lose a day's changes every now and then. Anyhow, it appears that Microsoft purposely leaves it crippled because otherwise it would cut into MS-SQL-Server sales. If you call to complain about an Access scaling problem, MS will happily suggest that you "upgrade" to MS-SQL-Server. They either wiped out or bought the competition, so now they are only competing with themselves. The few competitors left now charge an arm and a leg because they feel they cannot chase new customers due to MS, and thus bilk existing ones instead. The DesktopDatabase market used to be vibrant with multiple ExBase dialects, Paradox, nascent MS-Access, and others competing heavily. Suddenly, they fell out of favor and MS took what was left by bundling it with Office. MS's Office bundling is a category killer.'' ---- '''Annoyances or Oddities''' * VBA: Many arrays can only be one-dimensional, causing tons of duplicated code (since Redim only works on the first dimension) * VBA: Inline-if does not short-circuit, causing unnecessary errors and worse performance * One cannot group sql operations/views how one see's fit. It is put in an alphabetical list whether you want to or not. It would be nice if at least one could put multiple SQL commands in the same view box, similar to MS-DTS commands. * It guesses what column should have an index based on the name. This can cause surprise performance problems if you don't notice the auto-guess. (Microsoft's auto-guess features in many of its products are an IT nightmare. It might help drunk newbies with small lists, but is a pain if you are computer literate.) ** ''This can be easily turned off with an option setting.'' ** Okay, but I don't think it should be the default. * One gets "undo buffer full" during some operations even if you don't want to use undo. I could not find a way to disable this feature for interactive operations. * One cannot easily "compact" a database via code. Thus, if you use it for a nightly batch operation, either you make a fresh copy of the MDB file, or it fills up over time. Other DesktopDatabase''''''s allowed one to at least compact individual tables in code. ** This one-line batch script does the trick for Access 97, if MSACCESS.EXE is in the expected location, and if the .mdb file is closed. Call the batch script with the name of the .mdb as the argument: *** "C:\Program Files\Access97\Office\MSACCESS.EXE" %1 /Compact ** ''Yes, but it has to be called outside of the program, and the program needs to be closed first. For example, I often want a yes/no prompt when a user shuts down asking them if they want to run the "clean-up" (compacting) process. Doing it without asking often makes remote trouble-shooting and other things difficult, and slow.'' * Cannot import delimited files if the source file has fewer columns than the target table. In other words, one cannot have blank fillers. (There are indirect work-arounds, like intermediate files.) ** ''You can write your own importer in VBA. It's pretty simple to do." ** If you don't have a sample to use, such takes some time. Enough re-coding can "fix" anything, but why not just fix Access to be more flexible? * The SQL editor reformats your SQL in screwy and verbose ways. It can make nicely formatted SQL into an unreadable blob. * The font on the SQL edit screen is too damned small and it is not configurable. * Cannot write SQL to other databases without putting quotes around each line in code. In other words, it has no in-line SQL option (with variable substitution) outside of Jet SQL. * SQL has no comment ability. (For example, many dialects accept "--" as a comment character.) The query builder interface also has no comment slots. There's plenty a room for a "comment" row in it. (The "Properties" box for queries is the only place I've found for query comments, but it's granularity is often too large and its kind of "in another place".) * Certain compound queries cannot use table aliases such as A.foo or B.bar, only the actual table name, resulting in verbose queries. ** and this is a bad thing why exactly? ** ''Readability. I find a long table name before each column makes it much harder to read. Your eyes may work different. Syntax colorizing would perhaps help if it existed. Still, I'd rather have aliases.'' * Format lists (DataDictionary-like lists) are not actual tables, so one cannot do regular SQL processing/editing on them. * If you don't use actual column names for the displayed column titles, you'll spend a lot of time retyping in the same alias name over and over. For example, I don't want to put spaces in column names because that makes converting to another DB difficult. But then it displays underscores or CamelCase in the reports etc. unless you override it. Ideally the data dictionary should contain the display alias. Next time maybe I'll give in and put spaces in. Gates: 1 Me: 0 * If you want to avoid putting spaces and punctuation in column names to conform to common RDBMS conventions, then you have to keep stating the friendlier aliases over and over for each view definition. It would be nice if the data dictionary (schema) had a default alias item. And if you go the route of putting funny characters and spaces in the column names to avoid alias repetition/tedium, you are still limited because it will not take periods. Thus, you cannot have "Emp. No." as a column name. "Emp No" is as close as you can come without using aliases. [EditHint: Perhaps this and the prior item can be combined somehow.] * No easy way to query other databases if you need more than an as-is table view, which is only what "linked tables" gives you. You cannot easily write SQL to query a remote database, at least not without Access re-interpreting it in funny ways. ** [Create a new Query, then switch to SQL view. You can write anything you like.] ** It often does not understand native syntax, and often not clear whether things such as joins are happening on the client or the server. It can make a big performance difference. * Terse or misleading messages. For example, "data type mismatch" often doesn't say which column or code snippet is the culprit. Mistyped column names will often give you a misleading "missing parameter" message, and without telling which column it is. In my opinion, parameters should require a special character or function wrapper. Regardless, giving the name of the "bad" column or parameter would simplify debugging. * Slow "IN" function. * Very difficult to dump, list, or query the various "objects" into delimited or parse-able form (such as XML). For example, if you want to export all the SQL code in each query without copy and paste for each one. Some information is in the systems tables, but most of it is locked up in the "Access''''''Object" binary blobs. Gotta love encapsulation. * Right-clicking on a memo field does not show "Zoom" in the menu to see a multi-line window view-port. One has to remember Ctrl-F2. This is a case of "missing obvious features" as found in ControversialMicrosoftPhilosophies. It's been in the documentation for a while, such that one cannot claim it is an undocumented feature. (Version 2003) * Linked tables are difficult to make "read-only", creating big risks of accidentally clobbering original data. (One alleged work-around is to link to a query that uses "select distinct".) * Difficult to have or produce "modal" screens. Thus, the user can press a "Run" button multiple times and trigger multiple instances. Other tools had "modal" screens or processes that would lock out (or optionally queue) GUI events while a process was running. Generally, the default should be modal in my experience. Non-modal should be the exception that requires an explicit setting. ** This problem can be solved at the code level. Instead of running the non-thread-safe code directly from the button, call a VBA routine. Have the VBA routine store a Static variable, for whether it is in use or not, and reject attempts to run the non-thread-safe code while the code is already running. This code is similar to the SingletonPattern. After the GuardClause, run the non-thread-safe code. When the non-thread-safe code is done, restore the Static variable to allow calling the code again. ** ''Everything is solvable at the code level by writing one's own gui/database tool from scratch :-) It's just that Access's default abstractions are often a poor choice for the targeted domain. Multi-threading is so rarely needed for typical CRUD that it should not be the default.'' * There's '''too many properties''' in the GUI items. If you are not a speed-reader, you can easily get lost. Control over such minutia is nice, but some if it is overkill. I'd suggest having styles be defined separately and then just reference the style name. Styles would include fonts styles, sizes, background colors, foreground colors, panel colors, etc. Most apps will only have a few styles. * The "parameters" of schemas and macros are not visible in a tabular format. For example, you have to move through each item one-at-a-time in schemas and macros to see things such as column size and query name. (Or use the convert-to-VBA option for macros.) Their design interfaces in general are "property-oriented" instead of table-oriented. ** Partially fixed in Access-2007. It displays macro parameters in the tabular view. * While "update-able queries" are a powerful concept, they also can be the source of "gotcha's". Some way to "lock" them to make them read-only would be nice. (Similar but not identical to the "linked table" issue above.) * Difficult to make '''relative paths''' for linked tables. In general MS seems to make indirection and meta-ability difficult, or a second priority. ** ''Indeed. Coding or obtaining a "linked table manager" is a necessity for producing deployable Access applications.'' * Each window is treated as an independent window in Windows "task manager". This can create lot of confusion and is non-standard behavior. It would be nice if Access had a "window pad tool bar", which would be kind of like tabs in tabbed panes, but without the need for all windows fitting the same size. ** ''Access 2007 (at least) does not treat each window as an independent window in Windows "task manager".'' * File name disappears from the window title bar (Access-2007 only). This increases chance of accidentally modifying the wrong file. A partial work-around is to manually set the Application Name, but this makes version testing difficult. * The new "multivalue" type is anti-relational. Some speculate it's done for compatibility with SharePoint's underlying database. Others feel it may be easier to grok for newbies than one-to-many tables. * It's always asking if you want to save something, but it's often not easy to tell what you changed. Perhaps formatting configurations should be a separate kind of thing/object/entity, split off from query-space, and optional. That way there's a save prompt only if you explicitly first selected a format to edit. ** ''That's the way it is with a lot of M$ Office software, most times what is being changed is the time accessed, or if it is opened on a different computer, then it updates the new registered author and computer name, etc... Sucks all to h*ll...'' ** They really needs some serious competition. *** ''My daughter who is only aprox. 1 1/2 years away from her own medical practice, begged me to find her something better 1 1/2 years ago. I introduced her to OpenOffice, and she says she prefers it now (learning curve is a little steeper on some things, but easier on others she says, and more powerful overall). I personally still us M$ Office for the simple stuff I do.'' *** Do you mean OpenOfficeBase? I've also found it has a steeper learning curve due to poor UI design in my initial trials. I'd give MS-Access a "B-" in UI design and OO-Base a "D+". "B-" may sound good, but I'd expect more for a product that's been around so long and from a company that is so wealthy. **** ''This one: http://www.openoffice.org/ It's the complete "Free and Open Productivity Suite". M$ seems to be about dominance and profit, not quality and perfection.'' **** That ''is'' OpenOfficeBase. I've found its UI unpolished. Its day is not here yet. * Often get message, "Cannot do A because B '''is open'''." Why not a prompt that asks if one wants to close B? Example: "Cannot do A because B is open. Do you want to close B and continue? [yes] [no]". It's been on the fricken market almost 2 decades and MS had plenty of time and money to fix annoyances like this, but did squat. No competition will do that. * Versions prior to 2007 opened sub-windows in an MDI interface. 2007 uses tabs. However, for some uses, such as comparing side-by-side, MDI is better. It would be nice if the user had the ability to switch back and forth between the viewing styles as needed. (GuiMarkupProposal learned that lesson already.) ** ''Select the "Office Button" in the upper left-hand corner of the Access window. Press the "Access Options" button, then press the "Current Database" button. Under "Document Window Options" select "Overlapping Windows" for MDI mode. Follow the same steps and select "Tabbed Documents" to go back to tabbed mode.'' ** Hey, thanks much! I couldn't find a solution on Google; probably tried the wrong search words. Ideally a small corner marker should be in the viewing area that would provide a display format. (See "Divorced Controls" in ControversialMicrosoftPhilosophies.) Or a standard View menu sub-option to change it, if they didn't kill menus. And/or, right-click in the empty view area when nothing is open and select the format. ---- '''Corruption, problems and counter measures ''' Apparently sometimes for Autonumber fields the Seed counter can be "affected". If this is used as a primary key then subsequent DB operations can become "interesting". There is an article discussing this at http://www.everythingaccess.com/tutorials.asp?ID=Fixing-AutoNumbers-(AutoNumber-bug-in-Jet-4.0) ---- Does anybody know of a tool or technique to get a "dump" (serialization) of Access's objects and configurations? I inherited a BigBallOfMud for maintenance and would like to take inventory and search all the queries, macros, etc. It doesn't use a lot of VBA, so that's not really the issue. Thanks. --top ''Years ago, my company wrote a utility that did something like this for upsizing purposes. As I recall, it only extracted table and query definitions -- no macros, reports, or forms, I'm afraid. If you want it, and '''if''' I can still find a copy of it, you can have it.'' I'd be happy to take a look if you still have it. Does it extract SQL text from queries? Thanks. ---- I found this on usenet. Buyer beware. It does not produce direct SQL, but rather a meta-form of it. Public Sub DocDatabase() '==================================================================== ' Name: DocDatabase ' Purpose: Documents the database to a series of text files ' ' Author: Arvin Meyer ' Date: June 02, 1999 ' Comment: Uses the undocumented [Application.SaveAsText] syntax ' To reload use the syntax [Application.LoadFromText] ' Modified to set a reference to DAO 8/22/2005 '==================================================================== On Error GoTo Err_DocDatabase Dim dbs As DAO.Database Dim cnt As DAO.Container Dim doc As DAO.Document Dim i As Integer Set dbs = CurrentDb() ' use CurrentDb() to refresh Collections Set cnt = dbs.Containers("Forms") For Each doc In cnt.Documents Application.SaveAsText acForm, doc.Name, "D:\Document\" & doc.Name & ".txt" Next doc Set cnt = dbs.Containers("Reports") For Each doc In cnt.Documents Application.SaveAsText acReport, doc.Name, "D:\Document\" & doc.Name & ".txt" Next doc Set cnt = dbs.Containers("Scripts") For Each doc In cnt.Documents Application.SaveAsText acMacro, doc.Name, "D:\Document\" & doc.Name & ".txt" Next doc Set cnt = dbs.Containers("Modules") For Each doc In cnt.Documents Application.SaveAsText acModule, doc.Name, "D:\Document\" & doc.Name & ".txt" Next doc For i = 0 To dbs.QueryDefs.Count - 1 Application.SaveAsText acQuery, dbs.QueryDefs(i).Name, "D:\Document\" & dbs.QueryDefs(i).Name & ".txt" Next i Set doc = Nothing Set cnt = Nothing Set dbs = Nothing Exit_DocDatabase: Exit Sub Err_DocDatabase: Select Case Err Case Else MsgBox Err.Description Resume Exit_DocDatabase End Select End Sub ---- To make the object viewer in Access 2007 somewhat resemble that of version 2003: 1. Right-click on any title bar within the left panel and select "Show all groups". 2. Right-click on the "All Access Objects" bar and select "Sort By", and then "Name". 3. For "View By", select "List". 4. Use the chevron markers (">>") to open or close each bar group. Scroll bars will appear if the group does not entirely fit on a screen. ----- Here is an interesting perspective on MS-Access's usage within a larger organization. I don't necessarily agree with all of it, but it does raise some interesting issues to weigh, especially when balancing "do it properly" versus "get it done". http://blogs.techrepublic.com.com/10things/?p=386 -------------- Footnotes: [1] The pointer corruption can happen because during the copy process the context of a pointer in one part of a file may not match properly in another part at the time of copy for a given segment. For example, let's say during a sequential file copy, table segment A (of disk) is copied at 3pm. At this time, segment A has a reference to a record in segment Z. At 4pm, the pointer to segment Z in A is removed via a data transaction, along with the pointed-to record in Z. When our backup scan finally gets to segment Z at 5pm, it copies segment Z without our record of interest, since it was deleted at 4pm. However, if we restore this table based on our file copy, then we restore segment A with a pointer to a record in Z that is no longer there, and have a dangling reference. (Backup times exaggerated for illustration.) Ideally backups are done during a down-time or a "write-lock" period after-hours to avoid these kinds of things. A record-based copy may also have this problem for cross-table keys, but at least not at the sub-record level, which you may get if a direct file-level copy of the table is made. Because records may be split internally on disk, you can have sub-record corruption doing a direct file copy, which can crash the database unexpectedly. A missing record is usually a lessor evil than crashes due to internal structure corruption. ---- See also: DesktopDatabase, PowerfulAdHocDataProcessingTools, TestDrivenHeroism, ControversialMicrosoftPhilosophies, OpenOfficeBase, MicrosoftAccessQuestions ---- CategoryMicrosoft MicrosoftOffice