Microsoft Access with other data sources
December 12, 2005 2:02 PM   Subscribe

Database filter: I have a Microsoft Access application that is split into two .mdb files for the interfaces and data. This app is distributed on a CD to be used on computers that have no internet access. When someone needs an updated dataset the backend .mdb is refreshed from a load of Oracle tables and sent on CD so that the user only needs to replace a single file. The front end links to the tables in the back end.

The problem is that the amount of data required is steadily growing and has now surpassed the 2GB file size limit for Access. I need to leave the front end in Access as this is a big nasty dog of an app written by someone else and I don't have time to tackle a rewrite right now (as much as I'd love to). What I'd like to find, ideally, is an open-source database that can be distributed as a file or set of files that Access can link to. As I have no control over machine configurations I'd prefer to stay away from creating DSNs, installing software or registering DLLs if possible. I've been playing with Firebird and it looks like it would work admirably if I could work out how to get Access to link to its tables but haven't had much luck so far. Any suggestions?
posted by NsJen to Computers & Internet (9 answers total)
You can export the Access data to MySQL, which is open-source, or you could download the SQL Server from MSDN for free and it should be plug-'n-play. I'm not sure I quite understand exactly what you want to do, so I don't know if that will help you.
posted by Civil_Disobedient at 5:46 PM on December 12, 2005

Response by poster: Thanks for your reply, Civil_Disobedient. I wish I could draw diagrams in here to clarify my structure, but I'll try to verbalize it better. I need something that can be distributed as a standalone data file that Microsoft Access can use to retrieve data. The data should live in this data file and be "linked" from Access, the same way that you can link tables from other Access containers or ODBC data sources. I've also been looking at MySQL and PostgreSQL but it appears, unless I'm missing something (likely) that neither can be distributed as a standalone file db. Neither can the desktop SQL Server (MSDE ...or SQL Server Express now).

The reason I'm looking so closely at Firebird is that those folks do provide an "embedded" version that can be distributed as a data file and a .dll, but I can't work out how to make that work in Access without registering the .dll on the machine.

I'm extremely hesitant to try to install anything on user machines because I don't have access to or control over them. I know that some of them are still running Win98, which locks out SQL Server as it requires, at minimum, Win2000. Some of them are also locked down administratively so that the users can't install software or have access to machine configuration, which means that trying to create a DSN or register a DLL could be tricky.

I welcome any and all suggestions. Gratefully.
posted by NsJen at 6:06 PM on December 12, 2005

Well, this would be an extremely ugly way to go about it, but you could export the Access data to a text file that auto-generates and auto-populates via CREATES and UPDATES. I don't think you'd need to run any services, but I could be mistaken.

Here is a link to a module you can include in the Access database that will export the data into an SQL text file. You can then do what you wish with the SQL. Open it up in Access, click on the "Modules" section, and open it up to take a look at it. It saves whatever tables you have into a default text file (c:\temp\add.txt and del.txt) but you can change the parameters.

Alternatively, you can use Access link-tables that point to a delimitted text file, or further spread out your database's tables into individual MDB files. Then have a master Access database that's comprised of links to all the separate MDB files that contain, say, a single table each.

And actually, you can "export" an MS SQL database--kinda. Through the Enterprise Manager you can create a backup of the database that can then be imported on to the user's machines.

For something this big, I cannot stress heavily enough that Access is really, really not the ideal solution, but it sounds like you already know this. Don't know how much help any of this will be; hopefully someone else will come along and offer a better solution.
posted by Civil_Disobedient at 9:09 PM on December 12, 2005

Response by poster: CrayDrygu: Agreed. Wholeheartedly. Not my decision to make, unfortunately, although I'm arguing for it.

Hmmmm ... saving Oracle data to text rather than an .mdb. Not a bad idea, actually. Thanks! The data dump is actually accomplished via SQL Server DTS so it wouldn't be difficult to change the destination to text, I don't think. And thanks for that module ... very clever.

Yeah, I looked at splitting them into separate MDBs but unfortunately here the primary culprit is one massive table that is continuously growing, so anything I do in that direction would only be a temporary fix. For the SQL Server/MSDE solution I think that MSDE still has to be installed on the user's computer, doesn't it?

Just for the record, I'm not a fan of using Access for any sort of production application beyond the most basic. There's things that it's very good for, but not something like this. Sadly, this is something that I inherited (along with many others) and it's fairly low on my list of apps to refactor in the immediate future. I'm slowly porting everything to SQL Server/C#/Winforms but it's a long process and tackling this one is probably two or more years down the road.
posted by NsJen at 5:12 AM on December 13, 2005

Hey, NsJen, I think that Civil_Disobedient kind of nailed all the alternatives for you.

Regarding linking the tables, you'll probably need ODBC in any way (and if your frontend app doesn't access data through code, but rather as linked tables, and you want no trouble, this means that you'll have to create DSNs).

Also, considering the restrictions that you've stated your clients might have, you might run into some problems with the users having the right to write in a folder, so this is one other thing to consider when going for a 'file' approach.

My best advice would be separating the data MDB into several data MDBs (thus, avoiding the size of the MDB file being more than 2Gb), just as Civil_Disobedient told you. But it seems that this might not be the best solution for you, since we are talking about one single table being the large one.

Well, I have a good experience with Access (I have worked with Access since version 1.0) and with different database technologies, so if you want to lay down some options directly to my e-mail (see my profile), maybe I can help you out a bit more (I won't be able to check this thread much, but my e-mail is on)

Currently, I'm working with a project where there's a MDB file that performs several tasks over linked data. Since I'm working remotely, I'm using data dumps (importing and exporting to access), but it's kind of cumbersome.
posted by rexgregbr at 6:51 AM on December 13, 2005

I seem to recall that Win98 has problems across the board all files at the 2 GB limit. Maybe that was 4 GB, I don't exatcly recall. But it's something you may wish to consider while you're looking at changes.
posted by kc0dxh at 7:07 AM on December 13, 2005

For the SQL Server/MSDE solution I think that MSDE still has to be installed on the user's computer, doesn't it?

Yeah, that's true.

I'm just surprised you don't have any machines available to throw an MS SQL database on. With really large tables, Access positively grinds to a halt on queries. Although, if your client machines don't have network access, it won't do them any good. But really... in this day and age... well, you already know so I won't belabor the point. :) Good luck to you.
posted by Civil_Disobedient at 3:34 PM on December 13, 2005

Response by poster: Ahhh, very good point, kc0dxh.! It would appear that it's related to the filesystem type with FAT16 being constrained to 2GB and FAT32 to 4GB. Oh, bugger! Hadn't thought of that ...

I appreciate all the great advice, fellas. I'll follow up when I work out how to handle this. My current plan is to sit the design team down tomorrow and clear my throat loudly until they see the wisdom of changing the rules for using this software ... dump Win98 and let us create a DSN and everyone will be happy again. We'll see how it goes.
posted by NsJen at 6:52 PM on December 13, 2005

Response by poster: Sorry about the delay responding to this, guys, but I did want to come back in and let you know the resolution. I went back to work and built a proof-of-concept using the Firebird standalone to hold the data, then held a meeting with the team that involved much throat clearing on my part until they agreed to let me drop Win98 support and install the Firebird ODBC driver on the user machines. The standalone is quite nice because the only thing I have to actually install is the driver - the rest of it is simply a data file and a .dll that doesn't even have to be registered on the machine. Just drop it in the same folder as the data file and go. Access links to the tables just like any other ODBC source, so life is good once more.

Once again, thanks for the great thoughts and ideas!
posted by NsJen at 9:03 PM on December 24, 2005

« Older HL2:Lost Coast most realistic game?   |   Can I really change my local government? Maybe. Newer »
This thread is closed to new comments.