Microsoft Access
Microsoft AccessMicrosoft's version of build-your-own-database and CrudScreens in a box.
Far inferior to their barely advertised VisualFoxPro database.
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.
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 <grin>.
(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 WhyCurrentAttemptsToEvolveAccessWithoutJet
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:
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
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
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
EditText of this page
(last edited April 10, 2012)
or FindPage with title or text search