Can't connect twice to linked table using ACE/JET driver

Question

I'm trying to connect to an MS Access database linked table in VBScript. It works fine connecting the first time on one connection but if I close that connection and open a new one in the same script it gives me an error.

test.vbs(13, 1) Microsoft Office Access Database Engine: 
ODBC--connection to '{Oracle in OraClient10g_home1}DB_NAME' failed.

This is some code that triggers the error. TABLE_1 is an ODBC linked table in the test.mdb file.

Dim cnn, rs

Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=test.mdb"

Set rs = cnn.Execute("SELECT * FROM [TABLE_1]")
rs.Close
cnn.Close

Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=test.mdb"

Set rs = cnn.Execute("SELECT * FROM [TABLE_1]") '' crashes here
rs.Close
cnn.Close

This error does not occur if I try to access an ordinary Access table. Right now I'm thinking it's a bug in the Oracle ODBC driver.

Answer 1

I have just tried this using some tables I have linked to an SQL server and it work just fine (sorry I don’t have an oracle DB to test against) however I have a few things for you to try.

Firstly on your DIM line you are not specifying a type for cnn and rs. This is generally bad practice so change it to something like this

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset

Secondly do you need to close the connection in between statements? It is best to keep it open and just reuse it. Also as a force of habbit I also tend to close any object I open and also set them to nothing i.e.

Set cnn=nothing

If you must close it and reopen it try setting it to nothing see if that helps