Is it possible for 32-bit pyodbc and 32-bit Python to talk to 64-bit MS access database?

Question

Is it possible for 32-bit pyodbc and 32-bit Python to talk to 64-bit MS access database? i searched a lot but could be able to find a specific solution. I can neither change the 64 bit version of MS-ACCESS and nor change 32 bit version of Python and Pyodbc. Please help me..

score 2 · Answer 1 · answered May 17 '20 at 09:21

2

Nope.

You need to match bitness between the ODBC driver and application.

A half-alternative would be an alternate driver, such as UCanAccess + JayDeBeApi

answered May 17 '20 at 09:21

Erik A

31,639
12
42
67

Can you please provide me more information about how to use alternate drivers in my code. Some specific examples would be a great help. – OO7 May 17 '20 at 09:26
That's a bit too broad for here. An alternate ODBC driver would just be _download the driver, use a different connection string, no code adjustments needed_, an open-source but limited alternative is mdbtools. For using UCanAccess + JayDeBeApi, you can Google both and review the samples there. – Erik A May 17 '20 at 09:29
1

@OO7 re: UCanAccess & JayDeBeApi - See [this answer](https://stackoverflow.com/a/25614063/2144390) – Gord Thompson May 17 '20 at 11:38
There is no such thing as a access x32 bit database or a x64 bit database. Both versions of Access are free to open + consume + use a data file created with either version. What is important however if YOUR software is running as a x32 bit process, then of course it needs a working x32 bit version of the Access data engine installed. I explain this problem, and outline your solutions in my post below. – Albert D. Kallal May 17 '20 at 16:41
@Albert That much is obvious, but the question actually describes _I can neither change the 64 bit version of MS-ACCESS and nor change 32 bit version of Python and Pyodbc_, which seems enough information for me that the application is the problem and not the database file, and the OP likely knows it. Since you can't install 32-bits ACE with 64-bits Access (outside of doing unsupported tricks that may work or may ruin your installation), I think this answer stands. – Erik A May 18 '20 at 06:11
Don't really matter if you use ACE, or some 3rd party "ucan-access" driver. Bit size has to match. And yes, I quite often have installed a x32 bit version of ACE - even on machines say running x64 bit office. So, at the end of the day? Well, the fix is to install a correct bit size version. And the tip/trick to do this is to install ACE (not Access), and using the /quiet option gets you past the installer complain about having other (say x64) bit versions of ACE/office. Installing JayDeBeApi not going to get you past having the correct size bit issue for ACE - you still need to match – Albert D. Kallal May 18 '20 at 14:16
The other issue? Are not the VAST versions of web servers running x64 bits? (strange that poster wants x32). Now, I fully admit installing JayBeBEAPi as x32 bits will work but THEN the ACE driver still has to match. I suppose if one adopts u-canacess, then again the chances of the install working is far higher - but the bit size still has to match even in that case. I don't think it was all that clear to the poster that the concept of a x32 and x64 bit size database does not exist - especially given the wording used, so that issue really had to be hammered out. Its the driver you match. – Albert D. Kallal May 18 '20 at 14:24
UCanAccess doesn't use ACE, so bypasses that barrier. You don't use ACE at all if you go that way, and can even run Linux/MacOS. This avoids compatibility issues. Also, I've seen users ruin their Office installation following the `/quiet` switch advice, requiring a full deinstall/reinstall to get the OLEDB drivers working again. I really do not recommend it, Microsoft didn't introduce that check just to spite us but because simultaneous installs lead to trouble and are not supported. – Erik A May 18 '20 at 14:29
The poster was under the impression that there is a x64 bit database format. Without this Rosetta stone concept made clear, then offering x32 running code still not going to solve the issue in the posters mind. As for the /quiet switch - not I not seen it cause an issue. Since Access 2013, installing it does not expose a working copy of ACE. However, due to so much confusing and that people often installed access (or runtime) to get a working copy of ACE (they didn't care about Access). The newest version of 365/Access now has reversed this policy - it exposes ACE again. So its confusing! – Albert D. Kallal May 18 '20 at 14:37

Albert D. Kallal · Accepted Answer · 2020-05-17T16:57:55.230

Well, first of all, there is no specific x32 or x64 bit version of the DATA files, or database. In other words, a Access database created with x32 Access, or the x64 bit version of Access results in a data file that can be freely opened by either x32 access, or x64 bit access.

Hence you cannot ask how can I open a x64 bit database, since this issue is NOT relevant and in fact there is no such "specific" version of the database files.

So, what this means is that the bit size of the database does NOT exist as a concept, and either a x32 running process can open a database created with Access x32, or one created with Access x64.

And in fact a x32 bit running process can open a access database, and then a x64 bit process can ALSO open the database AT THE SAME time!!! (we assume two different computers here - one running the x32 bit version of the data engine, and the other workstation ruing say the x64 bit version. So, in fact TWO different processes (a x32 bit process and a x64 bit process) can BOTH OPEN and use the database at the SAME TIME!!!

Ok, now that we cleared up the above?

The issue then centers on your software process. If you are running a x32 bit process (which can and does run fine even on a x64 bit OS), then of course your software will require used of an installed x32 bit version of the Access data engine (used to be called JET, but the newer version is now called ACE).

So, Python running as a x32 bit process, or as a x64 is 100% FREE to open and use that database. The bit size does not matter. However, what will matter is that your running process has a installed copy of the ACE data engine of the same bit size. So, you don't even have to have Access installed for this to work, but you WILL NEED an installed and working copy of the ACE data engine. This data engine can be installed on your computer and thus you not even require Access to have been installed. And of course the CORRECT VERSION (bit size) of that data engine will be required (that matches your in-process running as x32, or as x64).

So, if you are running Python as x32? Then you need to install an x32 bit version of ACE.

You can find a working copy of the ACE data engine here:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

You simply need to download the correct bit size version and install that. The problems of this issue is that if you already installed say a x64 bit version of Access (that also installs a copy of ACE), or if you already installed a copy of the X64 bit version of ACE.

So, in most cases the solution is to remove the x64 bit version of Access (or ACE if you not installed Access), and then install the x32 bit version of ACE.

The problem is further compounded that for the most part, you can't install a x64 bit version of ACE along side a x32 bit version. And if you have existing versions of Office that include Access, then that install also included ACE. And the windows installers will again often complain, since those parts of office are dependent on a working + installed copy of ACE, and of course it has to be the correct bit size to match office.

Bottom line: You simply have to install a x32 bit version of ACE for this to work with a x32 bit running process. (computers worked this way since the first one was turned on!! you can't for example run x32 bit windows software say on windows 3.1 which is 16 bits. So our industry have done this kind of transition for 30 years now - nothing new).

The fact of the access having been created with Access x32, or x64 bits Access does not matter, and never did, and is not even relevant to this discussion.

So, what is important that you get a working copy of the ACE data engine installed on your computer, and it must match the in-process bit size of the code using that data engine. Really simple, and is no different then your software consuming any other code library - it has to match the bit size.

So, I strong suggest you use this link to get a working copy of ACE installed: https://www.microsoft.com/en-us/download/details.aspx?id=54920

Make sure you download your correct bit size (x86 for x32 bits), or x64 for the x64 bit version.

Now the installer DOES check (and complain) if you have existing x64 bits and parts of the data engine intalled. (and this will often include parts of office that use ACE data engine). There are two solutions: Try installing ACE from the windows command line, and include the /silent switch (I been told that previous versions use the /quiet switch - so it going to be either /silent, or the /quiet switch (can't remember right now - just know it one or the other). This switch ALSO causes the installer to "more ignore" other versions of ACE having been installed, and it will often even install if you have x64 bit versions of ACE installed when installing a x32 bit version of ACE (but, if you installed x64 ACE, you are a million times better off to un-install that x64 bit version of ACE, and Access x64 if installed).

The fact of other computers, other workstations etc. having Access x64 bits will still allow those computers to open and consume that data file, since as I pointed out there is NOT and NO such a thing as a x32 or x64 bit version of data files.

So, either x32 ACE/Access or x64 ACE/Access is most free to open a given database file. What you are NOT free to do is mix YOUR x32 bit software with x64 bit software. So, all kinds of x32 bit and x64 software programs will be installed on you computer, but if your development and running code is running as a x32 bit in-process, then that in-process code will be limited to running and consuming all software as x32 bits.

Thanks, Albert for very detail information. Still working on your information it. — OO7, May 18 '20 at 02:57

Is it possible for 32-bit pyodbc and 32-bit Python to talk to 64-bit MS access database?

2 Answers2