Jump to content
  • Advertisement
Dave Haylett

C# Need to replace JET4 access to Access databases

Recommended Posts

Hi everyone. I need some help with my project. It's a 2D-graphics-heavy WPF front-end app written in C#, which talks to two Access 2000 databases (yes I know, it's all I've got). It will be distributed freely on the internet, and so will be being used by Windows users of various installations/versions of Windows, Office, etc.

One of the two databases (let's call it A), is intended to be read-only, and will be distributed with the app. It has half a dozen relational tables which I as the developer have populated, and is connected to in the app via OleDB Jet 4 with SQL querying the data now and then as the user uses the front-end. The database will be replaced whenever I release an update to the app.

Database B is read/write, and contains end-user preferences, for example when they favourite something in my front-end, a Favourites table in here gets appended to. This database is not distributed with my app, and should not be overwritten, as it will lose user prefs, etc. and annoy my users.

Whenever my app is run by a user, during initialisation database A will suck in the user data from database B (using simple SQL SELECT * INTO...), so that all the tables can be joined together by the SQL in database A (to include user prefs/favourites in SQL queries), and whenever the user favourites something, a record is created both in A (for the short-term session) and B (permanently). Database B isn't just about holding favourites, there is other user data in here as well, so there are 3 or 4 tables in B.

So far, this is all working fine and I'm happy...

Unfortunately my app is currently 32-bit, and it now needs to break the 32-bit memory barrier what with the size and volume of the graphics I'm pulling in (using the HDD is not really an option, as different graphics are needed kind of instantly and the hard disc would be being hosed and the app dog-slow otherwise, I suspect even off an SSD).

I'm using VS2015, and switching to 64-bit will probably fix the memory problem, but it breaks Jet 4.0. I'm sure this is old news to most of you.

To try to keep with 32-bit (and Jet4) but get the memory I need I've tried the -largeaddressaware toggle, and I've tried the editbin suggestion, but I just can't get these solutions to work in VS2015 no matter how hard I try. Are these definitely 100% solutions to 2gb memory limit in 32-bit applications? Should they always work? Am I dumb in being unable to get this to work?

So otherwise I'm resigned to migrating to 64-bit, and having to get around the database issue, not the memory issue.

My users will be using a variety of Windows versions (probably 7 and 10), and I'm sure various versions of Office, and so my solution for querying my two Access databases needs to be pretty open if possible.

Googling has suggested I switch from JET4 to ACE12, but this is apparently requiring me to uninstall Office 2000 and install a 64-bit version (which I don't have), so I can't use it, and I suspect any users who also have an old version of Office installed won't be able to use it either?

Googling has also suggested I use MS SQL Server. This sounds fine if there's such a thing as a "lite" local version which can manage database access, but I still need to somehow get the data from the databases (A.mdb and B.mdb) into the SQL Server each time the users fire up my app.

The only solution I can think of at the minute, is to export all the tables from database A into CSVs every time I update the data in there, and have the app import them in a lame way, and also convert database B into some crappy text file which gets written to whenever the user changes a preference. I'd much rather use SQL to do all this if possible, as when the user browses around the app, queries involving joining several tables in A are regularly created and executed to adjust the user's experience/return search results/etc.

So to summarise my misery, is there either an easy reliable way for me to keep with 32-bit/Jet4 and be able to address >2gb. Or is there instead an easy reliable way for me to switch to 64-bit and successfully query two Access databases without requiring all my users to have 64-bit Office installed?

Thanks for reading and I hope someone can help.

Share this post

Link to post
Share on other sites

Hi Tape_Worm, thanks for your responses. I'm tempted to get away from Access, but my question would be how do I get the data content which I as the developer provide from an .mdb file into the SQLite client on other users' machines?

What I mean is, when a user starts up my app on their machine, and SQLite starts up with it, how does my app populate the tables in the client with data which I'm currently holding on my machine in Access? How do I distribute the data with the app?

Share this post

Link to post
Share on other sites

OK, well, I've read your original post a bit closer and I need clarification.

You have an .MDB being packaged with your app as a read only database (Database A).

You have another .MDB (Database B) which is located.... elsewhere?  Are you creating this secondary database from your application? You say it's not distributed along with the app, and I'm guessing you don't mean you're accessing it over the internet (very bad idea with Access). So I can only assume you're creating the database from scratch on the initial run of your application.

If this is true, then SQLite will serve you quite well as it's just a file on your harddrive like Access.  Only SQLite is far superior in that it supports huge databases (Access has a 2GB limit if I recall), and does not require special drivers like JET or ODBC or whatever.  We use SQLite at work as a staging database for our SQL server and it's been awesome (especially for performance).

If I'm wrong, and you're accessing database B remotely... then you're out of luck.  Using Access for this is a bad move. While Access is technically multi-user, it's not very good at it. In this case your only bet is to use SQL Server or another type of remote database (Postgres, MySql, etc...).  That said, for the sake of security, you should never access a database directly through the internet and should at the very least put it behind some sort of WebAPI (Web Services, WCF, etc...).

Just in case you do want SQLite, just add a reference to it in your project using NuGet:


Edited by Tape_Worm
Added nuget link for SQLite

Share this post

Link to post
Share on other sites

Hey Tape. You've pretty much got it - sorry it was probably not the best explanation. Database A is just full of records which I maintain, which the user queries via the app, and is distributed with the app. So you're saying I can essentially convert A from an .mdb file to some SQLite file and distribute that instead? (sorry I've never used SQLite before). Does SQLite have a front-end like Access, as I actually key in the records myself (don't worry it's much more fun than it sounds!)

B will be created on the user's machine if it doesn't already exist there, somewhere in their My Documents or the like, and will hold their prefs. This is currently an .mdb as well, but I'm guessing I can make that a .SQLite too?

If both A and B are SQLite files, then my app should be able to query them to its heart's content?

Thanks for the NuGet link.

Share this post

Link to post
Share on other sites

I use a program called DB Browser for SQLite for my front end.  Is it good?  Meh, it's good enough.  There's probably better out there if you look.

I don't know of anything that'd convert from MDB to SQLite for you (but I haven't looked either, haven't had need to), but if you can't find anything then you could write yourself a little one-off console app (in 32 bit to keep Access happy). This app would just create the SQLite database, and transfer the data from your access database into SQLite.  Then you can discard the Access stuff for good and use that SQLite database going forward. That really shouldn't be too difficult to do.

You can totally make a SQLite database for Database B at runtime. It's super easy to do.  Hell, if you want, you can attach the two SQLite databases together (Access provided a similar functionality with linked tables if I recall) at run time and transfer data using just SQL queries without having to write code to loop through a record at a time or whatever. Or, you could just read from those tables in A, and write to B, using the same database connection if you attach the databases. Whatever you choose is up to you, but that's some of the capabilities you can take advantage of.

One tip though.  To get maximum performance out of SQLite when adding/updating a lot of records, you should do it in a transaction.  It's blazingly fast this way.  If you don't do this, then it can pretty slow doing massive inserts/updates.

Share this post

Link to post
Share on other sites

Hi Tape_Worm, just wanted to follow up on this and say I've successfully migrated to SQLite in my project, so no more worries. The ATTACH functionality helped me join the two separate databases together which is a really nice function. Thanks again for your help.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!