MS Office 2010 and remote MySQL ODBC Data Source

Questions about Wine on Linux
Locked
mike_o
Newbie
Newbie
Posts: 4
Joined: Wed Feb 23, 2022 11:11 am

MS Office 2010 and remote MySQL ODBC Data Source

Post by mike_o »

Hello, I'm trying to run an old MS Access Application using a MySQL database for a Customer.

I am running Ubuntu 20.04 and wine-stable (version 7.0.0.0) with a 32 Bit WinXP wine bottle, MS Office 2010 and MySQL Connector 5.3.13. Both MS Access and MySQL Connector seem to be working, but not together.

To narrow down the error I first tried to connect an empty MS Access Database to the MySQL ODBC Data Source. No error messages from MS Access, but I could not see any table to connect or import.

Next I tried to use MS Excel. In MS Query I could see all tables, their structure and even their content, but at the moment I tried to insert it into the spreadsheet Excel reported a SQL syntax error. I tried to modify the queries in the MS Query Editor and always successfully got data in the MS Query preview, but again returning the data to Excel gave me a SQL syntax error:

Code: Select all

[MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.5.12-MariaDB-0+deb11u1]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ODBC' at line 1
(The database is on another Linux machine, a Debian Buster Server.)

The console log contains no messages marked with ":err:" for MS Excel during the database connect and only one for MS Access:

Code: Select all

01a0:err:shell:IShellLinkW_fnQueryInterface -- Interface: E_NOINTERFACE
Is this a known bug? If yes, how can I fix it (perhaps using another WinE version)? If not, which additional info should I provide?
mike_o
Newbie
Newbie
Posts: 4
Joined: Wed Feb 23, 2022 11:11 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by mike_o »

OK, I have to add more information.
First, my approach was to open MS Excel, then from there MS Query, and return the data to MS Query first, then close MS Query and return the data to Excel.
In this scenario I have now made a SQL query log on the MySQL server. The queries from MS Query look completely normal. But when I close MS Query, I get this:

Code: Select all

220226  0:57:24	   139 Connect	[email protected] on hai using TCP/IP
		   139 Query	SET NAMES utf8mb4
		   139 Query	SET character_set_results = NULL
		   139 Query	SET SQL_AUTO_IS_NULL = 0
		   139 Query	set @@sql_select_limit=DEFAULT
		   139 Query	ODBC
What the heck? "ODBC" only instead of "SELECT ... FROM ..."? No wonder the server reports an invalid query.

But when I choose to NOT return the selected data to MS Query for preview, but instead to Excel directly, Excel crashes, and it crashes BEFORE sending a query to the server. The console output is this:

Code: Select all

01dc:fixme:ddeml:WDML_DataHandle2Global Unsupported format (c051) for data 002847CA, passing raw information
01dc:fixme:ddeml:WDML_DataHandle2Global Unsupported format (c051) for data 00284AE2, passing raw information
01f8:fixme:ntdll:NtQuerySystemInformation info_class SYSTEM_PERFORMANCE_INFORMATION
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp_dwarf:dwarf2_read_range no entry found
01fc:fixme:dbghelp:MiniDumpWriteDump NIY MiniDumpWithDataSegs
01fc:fixme:cryptasn:CRYPT_GetBuiltinDecoder Unsupported decoder for lpszStructType 1.3.6.1.4.1.311.2.1.4
01fc:fixme:cryptasn:CRYPT_GetBuiltinDecoder Unsupported decoder for lpszStructType 1.3.6.1.4.1.311.2.1.4
01fc:fixme:cryptasn:CRYPT_GetBuiltinDecoder Unsupported decoder for lpszStructType 1.3.6.1.4.1.311.2.1.4
01fc:fixme:cryptasn:CRYPT_GetBuiltinDecoder Unsupported decoder for lpszStructType 1.3.6.1.4.1.311.2.1.4
01fc:fixme:advapi:RegisterEventSourceW ((null),L"Microsoft Office 14"): stub
01fc:fixme:advapi:ReportEventW (CAFE4242,0x0001,0x0000,0x000003e8,00000000,0x0008,0x000000e4,2E0C2BAC,0260DAE4): stub
01fc:err:eventlog:ReportEventW L"excel.exe"
01fc:err:eventlog:ReportEventW L"14.0.6024.1000"
01fc:err:eventlog:ReportEventW L"4d83e607"
01fc:err:eventlog:ReportEventW L"excel.exe"
01fc:err:eventlog:ReportEventW L"0.0.0.0"
01fc:err:eventlog:ReportEventW L"4d83e607"
01fc:err:eventlog:ReportEventW L"0"
01fc:err:eventlog:ReportEventW L"000156ef"
01fc:fixme:advapi:DeregisterEventSource (CAFE4242) stub
01fc:fixme:richedit:editor_handle_message EM_SETMARGINS: stub
01fc:fixme:richedit:editor_handle_message EM_SETMARGINS: stub
Now to MS Access. When I open the ODBC data source for linking a table, Access sends this to the server:

Code: Select all

220226  1:12:52	   141 Connect	[email protected] on hai using TCP/IP
		   141 Query	SET NAMES utf8mb4
		   141 Query	SET character_set_results = NULL
		   141 Query	SET SQL_AUTO_IS_NULL = 0
		   141 Query	set @@sql_select_limit=DEFAULT
		   141 Query	SELECT Config, nValue FROM MSysConf
		   141 Query	SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM ( SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = DATABASE() AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) ) TABLES
		   141 Query	select database()
Which to me looks completely valid, but differs a little from the (working) MS Query approach, which sends this:

Code: Select all

220226  0:55:20    138 Connect  [email protected] on hai using TCP/IP
                   138 Query    SET NAMES utf8mb4
                   138 Query    SET character_set_results = NULL
                   138 Query    SET SQL_AUTO_IS_NULL = 0
                   138 Query    select database()
                   138 Query    SHOW DATABASES LIKE '%'
                   138 Query    set @@sql_select_limit=DEFAULT
                   138 Query    SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM ( SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA LIKE 'hai' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) ) TABLES
But valid or not, none of the database tables is visible in the MS Access dialog.
mike_o
Newbie
Newbie
Posts: 4
Joined: Wed Feb 23, 2022 11:11 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by mike_o »

I could narrow down the error for Excel. It is definitely not the ODBC Connector, but inside the communication between MS Query and Excel.
If I store the (working) MS Query statement inside a .dqy file, that file is valid. But when I return this statement from MS Query to Excel, it is garbled. The "Definition" tab of the "Connection properties" dialog shows only the text "ODBC" instead of the statement, as it was also mentioned by the server log. If I change this text to valid SQL inside this dialog, in short "select * from xbenutzer", the data is inserted correctly into the spreadsheet.
For Excel that would be an acceptable workaround for me, because I don't like MS Query anyway. But this does not help me with MS Access.
Is this internal communication done with DCOM? Could that be broken? Could switching to another WinE version solve that problem?
mike_o
Newbie
Newbie
Posts: 4
Joined: Wed Feb 23, 2022 11:11 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by mike_o »

The responsiveness here is a little underwhelming, 9 days and no response ...

According to my recherche internal communication should still be done with OLE. Now in order to install the MySQL Connector I replaced oleaut32 using winetricks. I did not replace ole32.

I tried to do that now, but after doing that Excel does not start any more - Office can no longer check the license validity. Checking the file ole32.dll I was surprised to find something like a downgrade. While the previous file had a timestamp from the installation (2.2.2022) and a size of 5062303 Bytes, after reinstalling it with winetricks it had a timestamp from 14.4.2008 and a size of only 1287168 Bytes. The dialog during the installation showed that it was extracted using a WinXP Service Pack (as mentioned before, I am using a WinXP bottle).

Btw., here is a list of packages installed with winetricks before my ole32 experiment:

sourcehansans fakechinese fakejapanese fakekorean unifont cjkfonts andale arial comicsans courier georgia impact times trebuchet verdana webdings corefonts eufonts lucida opensymbol tahoma w_workaround_wine_bug-34803 remove_mono dotnet20sp2 msxml6 vb6run mdac28 mdac27 wsh57 jet40 oleaut32 vcrun2013

I installed the fonts first, then .Net 2.0, then MS XML 6, then Microsoft Office, then VB 6, MDAC 2.8 (MDAC 2.7 and Windows Scripting Host were added automatically), then MS Jet 4.0, then oleaut32, then the MySQL ODBC Connector 3.5 (which didn't give usable error messages), then VC Runtime 2013 and MySQL ODBC Connector 5.3 (which pointed me to a DNS problem of the host, and worked as mentioned in the previous posts after I corrected it).

What could I do next?
Ebiko
Level 1
Level 1
Posts: 9
Joined: Fri Dec 03, 2021 8:30 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by Ebiko »

You could use a different Programm to access the databases.

Why using wine with Ms access, if you can just use Linux native applications to connect to databases.

In your case it seems to be a MySQL database.
So just install a MySQL able program ( there are even some Visual Studio Code extensions which are able to do this)
Keep it simple.
You don't need everything you want to use in wine.

If you are forced to use wine, I'd recommend staying away from Microsoft products in wine, because they tend to have tons of local dependencies.

Use easier and smaller programs like Heidi if necessary.

Otherwise just install MySQL and or PHPmyadmin native in Linux Or Any other MySQL able Programm, and don't use wine to run something annoying like msAccess...

Try native solutions first.

Especially for development that's Waaaaay easier.

Visual Studio Code, qtcreator, intellij, .... All of those can run native in Linux without the use of wine.

And one more thing!
Do not Demand ever again.

This is a forum. Everyone here answers questions or topics in their free time.
So if you get an answer be thankful you got one.

You are not in a place where you could demand answers!

You do not have a paid contract which would allow you to ask for support.

This is a free community driven place.
Not your local support center.

no one is forced to answer you.
Get that in your mind.
daveweaton
Level 2
Level 2
Posts: 23
Joined: Mon Aug 30, 2021 2:14 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by daveweaton »

I don't know enough to know if it applies to your situation or not, but if you read through viewtopic.php?f=8&t=34463&p=129959&hili ... ar#p129959 you may find something helpful about an odbccp32 you don't have listed.
wayland
Newbie
Newbie
Posts: 2
Joined: Mon May 16, 2022 11:11 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by wayland »

Hello Mike_o,

I managed to get a pretty good MS Access 2010 installation working with MySQL drivers;
https://appdb.winehq.org/objectManager. ... ent-106492

You may want to compare what I did with what you're doing. I think MS Access 2010 is almost practical on Linux after 12 years. Shame that the more modern ones are not.

Regards,

Wayland
wayland
Newbie
Newbie
Posts: 2
Joined: Mon May 16, 2022 11:11 am

Re: MS Office 2010 and remote MySQL ODBC Data Source

Post by wayland »

Hello Ebiko,

The point about using MS Access is that a lot of business intelligence has been built into the forms and VBA code over the years it's been in use. The users are also familiar with it. It's a good idea to keep the data safe in a MySQL database because MS Access has a habit of screwing up it's own data files when it crashes.

In terms of re-doing it in something native to Linux I would ask after 33 years of MS Access what does Linux have to offer that matches it? As far as I can tell, nothing. When it launched it was the integrated database development environment data did everything and did it better than anything else. Yes you can code it all in C++ if you're that way inclined but that's not better, just harder and more expensive. The only thing that I've needed to change about Access (apart from getting it onto Linux) is to use a MySQL back end, which was always an option thanks to ODBC.

Having an installer that would put MS Access 2010 onto Linux with all the features without the fiddle would be significant.

Wayland
Locked