We're trying to adapt some vba scripts to Microstation Connect Edition.
One does an Microsoft Access Database import / export.
In SS4 we're establishing a connection to an access database with the following code
Set MyDBcon = New ADODB.Connection MyDBcon.CursorLocation = adUseClient 'db connection On Error GoTo Error_Connection MyDBcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + stMyDbFileLocal, "", "" On Error GoTo 0
This fails in CE.(The file which 'stMyDbFileLocal' points to exists.)
We have some references loaded in ss4 as you can see in the attached image.
Do we have to reference additional / other files to make this work in CE?
Ingo
Hi Ingo,
I recall similar topic was discussed in the past already (had no time to do a detail searching).
MicroStation CONNECT Edition is 64bit application, so you have to ensure 64bit ODBC / OLE DB / etc. database drivers are installed properly. If you have no 64bit Office installed, they are probably not availalbe on your computer and you have to download them. I assume also reference will be different.
With regards,
Jan
Bentley Accredited Developer: iTwin Platform - AssociateLabyrinth Technology | dev.notes() | cad.point
Jan beat me to it, I was going also say my bet would be 32bit/64bit connection problem.
Nice profile pic by the way!
(Thanks ;-) long live the penguin!)
I'm trying to check all the components for 64 bit.
Is the ADO ext responsible for database connectivity?
I've tried to switch to ADO 6.0 but this didn't help on first sight.
Ingo,
Jan is correct that just as there are 32-bit and 64-bit OSs and Applications, there are also 32-bit/64-bit CMD shells, ODBC Managers (both called: odbcad32.exe), Drivers, and Connections. Fortunately, Microsoft does provide a robust set of default 32-bit/64-bit database drivers that can be used in most cases to create connections to respective 32-bit/64-bit application databases/data sources. In the past I have found Microsoft UDL connections to be something that can easily be "coded", "updated", and "shared" easily for users and applications. Given this connection type advantage for consideration, you may wish to make the simple change to UDL then review how to make successful connections via this blog post.
How to run 32-bit UDL file on a 64-bit Operating System
HTH,Bob
Bob,
Thanks for the insights. We'll look into that.
What is not quite clear for us at the moment:
Will there be a chance to get Connect working with Access 32bit or will we have to upgrade Access first to 64bit to get everything working?
Of course the optimal solution for stability and performance would be to purchase optimized direct database driver packs to meet your requirements. However, most application developers do not wish to impose those requirements and use the readily available OS drivers unless the 3rd party application developer finds those drivers to not meet their own requirements. I suggest the above to test and see if you can achieve acceptable performance and reliability, and if not satisfactory then yes it is always best to avoid (thunking) constant 32-bit/64-bit data connections and conversions.
Bob
So for the curious:
Just to get it running we had to change the connection string from line 1 to 2:
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & stMyDbFileLocal & ";" strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & stMyDbFileLocal & ";" On Error GoTo Error_Connection MyDBcon.Open strCon On Error GoTo 0
There still seems to be some converting in background before running the first time but at least it's running.
Answer Verified By: Robert Hook