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
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?
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
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.