How to connect Calc to ODBC tables?

Albireo · April 4, 2019, 12:20pm

Hi!
(Hope I can describe my wish clearly enough.)
I have Win10 as OS, LO 6.0, and the database tables on the same computer…

I don’t know if the tables is in SQL format or… (but SQL-questions work with other programs…)
I want to be able to read these tables in to LO Calc. What is required? What to do?

Perhaps my problem is that the database is 32-bit and the ODBC driver 64-bit?
Is the only way to make a new installation of 32-bit LO?

Albireo · April 4, 2019, 5:59pm

If the database is 32-bit, and the ODBC driver is 32-bit, - should LibreOffice also be 32-bit? (now is LibreOffice 64-bit)

Albireo · April 9, 2019, 9:25pm

Now I have tried to do the same with OpenOffice Calc and connect the tables works for me.
Why does it not work in LibreOffice?
Is it not possible in LibreOffice?
Is it a bug?

Albireo · April 10, 2019, 11:09pm

I don’t know if I do something wrong. but…
(I try freely translated commands and information from Swedish)

Open Calc
Press Ctrl+Shift+F4 (The data source window opens)
My database is not registered (I can’t see the database in the upper left box)
Right-click on the mouse in the upper left box and a new menu is opened.
Select “Edit database file”
The Base opens (Java must be installed)

Albireo · April 10, 2019, 11:15pm

To register a new database (in Base)
5) Press Ctrl + N (The window “database wizard” is opened)
6) Select the radio button “Connect to an existing database” and select “ODBC”
6a) Press the button “Next”
A new window is opened (Name on the ODBC-Data source)
7) Press the button “Browse”
A new window is opened .: “Data Source” (my window is empty)
8) Press the button “Organize”
A new window is opened “ODBC - Administrate Data Source (64 bits)” with many tabs.
9) Select the tab “System-DSN”
A message is shown “You are logged in without administrator rights. Unable to create or modify system DSN”

Ratslinger · April 11, 2019, 4:35pm

Hello,

There are items you fail to mention. You say nothing about which database you are attempting to connect to. Also, you fail to explain what/how the connection is to be used - Data source access, access via macro or something else.

You should review the LO documentation found here → LibreOffice Base Handbook. Please look in Chapter 2 Creating a Database under the section ‘Accessing external databases’ for more information. There is also another guide further down on that post → How to Access and Use Data Sources with Base which may be of help.

Albireo · April 11, 2019, 8:16pm

Thanks!
Thought this information doesn’t matter, because I’m using ODBC. The database I am trying to connect to is Providex (from SAGE Canada) and the ODBC- driver is “pvxodb32.dll (32-bit)”. It is possible to use SQL queries to the driver. The first wish is to be able to read the database tables with Calc, but if it works well, other wishes may arise. Think I have done right (have followed the instructions in the manual). Right now, I think the problem is that LibreOffice is 64-bit and I need an 32-bit ODBC-driver in LO. Can I install a 32-bit ODBC driver for LibreOffice? How? (The driver version in OO is 32-bit and the LO is 64-bit).

Ratslinger · April 11, 2019, 8:38pm

You may be surprised to know some problems arise only because of the LO version, bitness, database used, connection type or the OS. Also depending upon this combination there may be different solutions.

Sorry, don’t know anything about that DB. Would recommend you trying LO 32-bit so as to use the 32-bit driver. First item is to insure Base connection. Then register Base. Then accessible in Calc.