32 bit vs 64 bit access to Access

[Kyudos]

We are developing a plugin, and naturally we feel we'll need to have both a 32bit and 64bit build. The complication arises because our plugin needs to interface with Access databases (accdb), and user may have installed either 32bit or 64bit versions of Office (or the database driver). Further, it is difficult to make both the 32bit and 64bit versions of the access database driver co-exist.

Simplistically, we need our user's "bit-type" version of ACAD to match their "bit-type" version of Office - but enforcing this might not be popular if it involves relicensing etc.

Does anyone have any suggestions on how to handle this? Or workarounds?

[owenwengerd]

What exactly do you mean by "database driver"? There are many ways to access a database file; why do you want to use Office?

[Kyudos]

I mean the ODBC Microsoft Access Driver. We use Access databases partly for historical reasons (lots of legacy data) but also because they are easy for users to deal with / modify (they are user configurable databases of semi-user-specific data). It is not practical to change our database architecture at this stage.

I've managed to make this work on my dev machine by 'tricking' my 32bit Office installation into accepting the install of the 64bit driver - but doing that in the field sounds like it could be a bit of a nightmare!

[owenwengerd]

When I see "it is not practical to change database architecture at this stage", I read it as "this is the only way we know, and we're afraid of the unknown". Many a developer before you has wasted a lot of time and money trying to use what they know before finally figuring out that the simple solution is to learn somethig new.

If the database is only used by your app, then you should change to a better format (like SQLite). Your end users can still use Access to edit the SQLite database via ODBC if they prefer, but it would be better if you provide a custom UI for them.

[Kyudos]

No, I mean it really isn't practical - not for technical reasons - for resource reasons (I'm perfectly conversant with other database systems). We have 2 FTEs supporting (and developing) our current product and redeveloping it for ACAD (and its complex - like 20+ years old complex...). We don't have the time or budget to change the database stuff for no real gain - SQL based systems offer us no tangible benefit over sticking with Access and only headaches for our users (many of whom struggle even to use Office)

We do provide a custom editor for our databases, but that compounds the resource problem because we'd have to redevelop that too if we changed.

We could change, but it would cost more than we can afford to get us back to where we started.

Like I said...not practical.

[owenwengerd]

There is no need to defend your position here, as I'm not attacking you and in any case I'm not paying the bills. It's apparent from your reply that you could benefit from some expert assistance.

[nick83]

not so long ago, we worked with Firebird in a direct way (ibpp library). but Firebird for x64 was unstable with our ibpp, because it was 32bit ))). and it was impossible to rebuild library to x64 (that's why we used 32bit Firebird in both systems 32/64bit. that was the only way to force it work). so, we desided to change "driver". in 1-2 months we ported everything we had to ADO. now, programm works pretty fine with Firebird, MSSQL. And we canadd any database our customers want in 15 minutes (the difference is just in connection string) + code rebuild ))).

there is no longer a difference in win32 and x64 for us. it's not our problem (as long as ADO live). i know, that ado is not the best way, but it's the one of the easiest (we all are little bit lazy here)

[Anonymous]

==== EDIT ====

reading nick83's post, I remember I read this a while ago:

http://through-the-interface.typepad.com/through_the_interface/2010/04/64-bit-driver-for-microsoft-a...

I hope this helps

============

Hi,

I don't know any straightforward way to do what you need but you may consider using COM to brige the plateform gap by creating a COM Win32 local server that connects to Access database (COM proxy/stub communication bridges the gap for you).

The goal is to develop a COM wrapper object (or a set of com objects) in this local server (similar interface is required to minimize changes in your client code).

There are many serious drawbacks you have to consider, mainly:

• this is COM
• Third party components that require raw odbc objects (UI grids, ...) may be an issue (depending on access odbc driver implementation, it can even be blocking)
• performances: this really depends on operations done on database (proxy/stub communication is quite lazy)

• maintainability
• deployment

However, depending on your code, your resources constraints, ... it might be an acceptable solution, the purpose of my answer was just to give you an alternative.

Loic

[Norman_Yuan]

<QUOTE>

we need our user's "bit-type" version of ACAD to match their "bit-type" version of Office...

</QUOTE>

Are you saying that you need choose 32 or 64 bit AutoCAD for the user because of MS office he uses (32 or 64bit), just because of the need to access data in MS Access DB? That would be very bad and definitely "not be popular" as you expected, and very likely you do not have to.

Firstly, let's make it clear that MS Access is both data source and frontend user desktop app that could be in the same file (*.mdb/*.accdb). Ideally, if there is user interface, the UI app and data source should be separated as 2 files (*.mdb/*accdb). The *.mdb/*.accdb file as data source, can be accessed by either 32 or 64 bit application (MS Access fronend, or AutoCAD, in your case) via MS Access Database Engine, 32 or 64 bit respectively. Since in most cases MS Office suite of 32-bit os used, MS Access Database Engine 32-bit is usually installed with MS Office (whether MS Access is included or not). In older MS Office (2007), the computer can only have 32 MS Access database engine, or 64 bit one, but not both. With later office (2010, 2013), it is possible to have both (32 and 64 bit) MS Access database engine installed (but the installation might be tricky, expecially for MS Office 2010).

So, as long as you are not stuck with older MS Office (2007, since it was the first version of MS Acesss that dropped MS Jet Engine and used MS Access Database Engine), you are free to choose AutoCAD 64 bit, regardless MS Office being 32 or 64 bit. Most likely, you'd use AutoCAD 64-bit. Therefore the only thing you need to make sure what your plugin requires is to install 64-bit MS Access Database Engine, which is the prerequisite of AutoCAD (64-bit), not of your Plugin (so, you do not have to feel sorry if the data access does not work in your plugin due to missing 64-bit MS Access Database Engine).

With all said, using MS Access as data sourse isn't a good choice. If your user likes the UI available in existing MS Access, you can separate the data into other type of database (SQLite, SQL Server Express...) and stiil let user to use the same MS Access UI, and AutoCAD can access the data direcctly to the back end. Better yet, the backend data may be wrapped into a backend web service, so the 32/64 (or future "128 bit" :-)) issue can be gone forever.

[Kyudos]

The main probelm bit wise is if the user has 64-bit AutoCAD and 32-bit Office. Office installs the Access Database Engine, so you end up with the 32-bit version. You can't then install the 64-bit Access Database Engine without resorting to "trickery" (using the /passive command line parameter and fiddling with a registery entry). I'm not that comfortable relying on trickery - even if it does seem to work...

[Norman_Yuan]

---

@Kyudos wrote:

The main probelm bit wise is if the user has 64-bit AutoCAD and 32-bit Office. Office installs the Access Database Engine, so you end up with the 32-bit version. You can't then install the 64-bit Access Database Engine without resorting to "trickery" (using the /passive command line parameter and fiddling with a registery entry). I'm not that comfortable relying on trickery - even if it does seem to work...

---

That is the consequence of having to, or choosing to, use MS Access database: you must installed 32 or 64 bit MS Access Database Engine according to the AutoCAD being 32 or 64bit.

If you cannot change the fact that the data has to ne in MS Access database and be "ordered" to deal with it, as a customization programmer, you can wrap the data source in a service hosted in your network, or even the cloud, so that AutoCAD will access data data via the service and knows nothing the data source format (be it MS Access, Excel worksheet, Sql Database...).