[Solved] Opening and MS Access Database in AOO

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
JaOOBu7
Posts: 3
Joined: Mon Mar 02, 2015 1:50 am

[Solved] Opening and MS Access Database in AOO

Post by JaOOBu7 »

Hi! I'm trying to use OpenOffice to open a n Access database (.accdb).

I follow the instructions given in https://www.openoffice.org/FAQs/ms-acce ... ccess.html (there's a video with similar instructions on YouTube) but at step 3 ("click the 'Add' button and select the "MS Access Database line" from the drop-down list") the only entry in the dropdown list is "SQL Server".

Where am I going wrong?

Regards.
Last edited by Hagar Delest on Thu Mar 12, 2015 10:44 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.1 / Windows 8.1
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Opening and MS Access Database in Oo

Post by Greengiant224 »

JaOOBu7:
Where am I going wrong?
The m$ Jet v4.0 db engine is not installed by default on Win7/8 64 bit OS.
IIRC it is superceded by the SQL Server driver instead. All is not lost however.
If you can follow a few simple instructions you can be up and running in a few minutes.
Read, and re-read, if necessary the info in the following link:
https://answers.microsoft.com/en-us/win ... 3e467795a6

Does this help?

Greengiant224

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
JaOOBu7
Posts: 3
Joined: Mon Mar 02, 2015 1:50 am

Re: Opening and MS Access Database in Oo

Post by JaOOBu7 »

Hi Greengiant224 and many thanks for your advice. It's no use pretending - I'm way out of my depth here, and jsut fiddling around in the hope that I end up with 'Hamlet'. So far what I've done is gone to Control panel > Admin tools > ODBC sources and changed the shortcut from <%windir%\System32\odbcad32.exe> to <%windir%\SysWOW64\odbcad32.exe> in both the ODBC souces (32 bit) and (64 bit) in turn.

That has moved me on a bit (I think); what now happens is that when I try to connect to my database in either LibreOffice or OpenOffic[Microsoft][ODBC database manager]e I get the message "The connection to the database "<database name>" could not be established. The specified DSN contains an architecture mismatch between the driver and application".

One further - and probably dumb - question. It appears as if what happens in the early stages of connecting to my colleague's Access Database is that an <.odb> version is created, and that what I would end up working with / veiwing / updating (if I ever get that far!) is the odb database. Does this mean that any updates I make won't be visible to my colleagues if they have MS Access? Or is there an 'export' option in either of the office suites which allows me to export what I've done in MS Access format?

Again, many thanks for your help.
OpenOffice 4.1.1 / Windows 8.1
dani5
Posts: 1
Joined: Tue Mar 10, 2015 8:05 pm

Re: Opening and MS Access Database in Oo

Post by dani5 »

JaOOBu7:

Were able to fix the problem. I seem to be having a similar issue and have no idea what to do next. I'm in way over my head here. I have a Microsoft Access database that I need to open with Open Office, I followed the links mentioned above and all attempts have failed. If you've made any progress, I'd really appreciate your help.

Thanks!
Open Office 4.1.1, Windows 8.1
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Opening and MS Access Database in Oo

Post by rudolfo »

JaOOBu7 wrote:changed the shortcut from <%windir%\System32\odbcad32.exe> to <%windir%\SysWOW64\odbcad32.exe> in both the ODBC souces (32 bit) and (64 bit) in turn.
Some clarirfication is needed, because the path names that Microsoft has used for the 32bit subsystem on 64 bit windows machines is contrary to what common sense would expect. The statement from the above mentioned resource
https://answers.microsoft.com wrote:There are two ODBC locations on a 64 bit machine. C:\windows\System32\odbcad32.exe (this is for the 32 bit) C:\windows\SysWOW64\odbcad32.exe (for 64 bit)
is wrong!
As I said it is contrary to common sense: The path system32 is for 64 bit processes and SysWOW64 is for 32 bit processes. You can easily check this if you start the Task Manager (Ctrl-Alt Delete and chose TaskManager), use File menu and New Task. Type in C:\windows\SysWOW64\odbcad32.exe and hit enter.
Depending on your OS you will see the newly started application as ODBC-Administrator (32 Bit) (Windows 8) or odbcad32.exe *32 (on the processes tab of Windows 7) indicating that you have just started a 32bit process.

Probably someone at Microsoft decided it would be very cool to have a touch of "World of Warcraft" in their system path. And that's why they came up with the strange Acronym W(indows)O(n)W(indows). Which wants to say that there is a Windows System (32bit) embedded into another Windows (64bit).

Conclusion: Always start C:\windows\SysWOW64\odbcad32.exe if you want to configure ODBC drivers that are relevant for OpenOffice.

If you are lucky and have Windows 8 or you installed Powershell 3.0 on Windows 7, you can use Powershell to check your configuration with Get-OdbcDsn. The following are the results on my Windows 8 (64bit) system (Notice that both 32 and 64bit drivers appear, as well as hybrid once (SQL-Server Client):

Code: Select all

PS C:\Users\rudolfo> Get-OdbcDsn

Name       : LocalAmaretto
DsnType    : User
Platform   : 32/64-bit
DriverName : SQL Server Native Client 11.0
Attribute  : {Description, Server, Trusted_Connection, Database}

Name       : SQLite3 Datasource
DsnType    : System
Platform   : 32-bit
DriverName : SQLite3 ODBC Driver
Attribute  : {Database}

Name       : alsun
DsnType    : System
Platform   : 32-bit
DriverName : Oracle in instantclient_11_2
Attribute  : {Password, StatementCache, NumericSetting, Description...}

Name       : platon
DsnType    : System
Platform   : 64-bit
DriverName : Oracle in OraClient11g_home1
Attribute  : {Password, StatementCache, NumericSetting, Description...}
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
JaOOBu7
Posts: 3
Joined: Mon Mar 02, 2015 1:50 am

Re: Opening and MS Access Database in Oo

Post by JaOOBu7 »

Hi rudolfo, and many thanks for your input.

I think I've followed your suggestion ("Always start C:\windows\SysWOW64\odbcad32.exe if you want to configure ODBC drivers that are relevant for OpenOffice.") but still end up in the same place - i.e. when I open OpenOffice and try to connect to my database, there's nothing in the list of databases.

Step by step:

0. Some time ago I downloaded AccessDatabaseEngine_x64.exe and ran it. I think that downloads more database drivers than were installed on my system at purchase.

1. Control panel > Administrator tools > Change the shortcut in both ODBC Datasource (32 bit) and (64-bit) to C:\windows\SysWOW64\odbcad32.exe

2. Control + 'R' / "odbcad32.exe" and 'Enter. That gets me to the ODBC Datasource Administrator (64-bit) window.

3. In tab 'User DNS' click 'Add' . In the "Create new database source" window select the 'Microsoft Access Driver (.mdb.accdb) line and click 'Finish'

4. In the next window (ODBC Microsoft Access Setup) click on 'Select'

5. In the next window (Select Database) navigate to where the Access Database is (desktop) and select it.

6. Back at the ODBC Microsoft Access Setup window give the database a name (in the Data Source name). I gave it the same name as the Access database. Click OK.

7. Back at the 'ODBC Datasource Administrator (64-bit)' window I click 'OK' which closes the window.

8 Now I open up OpenOffice, and select 'Database'.

9. I choose 'Connect to an existing database', select 'ODBC' from the dropdown list, and click 'Next'.

10. On the next screen I browse, and select the databse source (it's the only one in the list) and Click 'Next'

11. I click 'Finish' at the next screen (Authentication) but get the 'architecture mismatch' when the Oo attempts to connect.

I have a hunch that the problem starts at step 2 - I should be connecting to an ODBC Datasources Administrator (32-bit) window, not the ODBC Datasource Administrator (64-bit) one.

But, when I remove the database from the 64-bit window list and try to execute the 32-bit version (by going Control Panel > Administrative tools > double-click on the "ODBC Datasource Administrator (32-bit)" line, I can't see an Access database driver with an 'accdb' extension - only ones with 'mdb'.

!!!!!!!******* Flash of common sense !!!!! (1) I need to be using ODBC Datasource Administrator (32-bit) not 32-bit and (2) I should have downlaoded the 32-bit equivalent of AccessDatabaseEngine_x64.exe at step 0.

So, quick revision:

1. Uninstall AccessDatabaseEngine_x64.exe

2. Download and run AccessDatabaseEngine.exe

3. Go to Control Panel > Administrative tools > double-click on 'ODBC Data Sources (32-bit) ... and follow the same steps as described above from 3 onwards.

.... and, blow me down with a feather .... it actually works!!!


Thanks guys for all your help - having to spell it out step by step helped so much - why had I downloaded _x64 for a 32-bit database.....
OpenOffice 4.1.1 / Windows 8.1
Post Reply