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.

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?

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.

Thanks mate this is all really useful. I'll look into it all. Appreciate your help.

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.