views:

118

answers:

1

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.

A: 

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

Kevin Ross
@Kevin Dim as type is not supported in VBScript
HansUp
Sorry saw the ms-access tag and thought it he was using VBA
Kevin Ross
Thanks for the suggestions. I will probably end up having to just use a single Connection for the entire script, but it's cumbersome for what I'm doing. Setting the Connection to Nothing after closing doesn't fix the problem. Also, using separate variables to store the different connections didn't fix it, either.
Tmdean
Jet/ACE is happier with fewer connections. You might consider adapting your usual practice to the specifics of the tools you are using.
David-W-Fenton