Access connection in Connect Edition

Question

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

Ingo · Accepted Answer

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.

Robert Hook · Answer

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

Access connection in Connect Edition

Top Replies