Linking Microsoft Access to Oracle ODBC datasource in VBA.  Error exceeded maximum connect time.

I have constructed the connection string below in order to connect to an ODBC datasource.  The problem is, my program exceeds the maximum connect time.  In order to reconnect I have to quit MS Access and restart the program.  I am running a series of individual queries as follows:

ODBC query 1
other query 1
ODBC query 2
other query 2
....
....
ODBC query n
other query n

Where n = ~20

These queries have to be run in order.
What i want is to completely disconnect from the ODBC datasource after every ODBC query and reconnect them again.  I can delete the linked table but i am unable to completely close the link with the server.  How can i completely close the link with the server without closing my application?

here is my connection string:

<<<<begin>>>>
Dim DBS As Database
Dim RST As DAO.Recordset
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("P_Login")
Dim FLD_pass As DAO.Field
Dim FLD_Log As DAO.Field
Set FLD_pass = RST.Fields("Password")
Set FLD_Log = RST.Fields("Login")

If RST.RecordCount = 0 Then
Exit Sub
End If

RST.MoveFirst
Dim tdflinked As TableDef
Dim strUserid As String
Dim strUserpwd As String
strUserid = FLD_Log
strUserpwd = FLD_pass

    For i = 0 To DBS.TableDefs.Count - 2
    If "TABLENAME" = DBS.TableDefs(i).Name Then
        DBS.TableDefs.Delete (DBS.TableDefs(i).Name)
    End If
    Next i
Set tdflinked = DBS.CreateTableDef("TABLENAME")
tdflinked.Connect = "ODBC;DSN=InfoCtr;UID=" & strUserid & ";PWD=" & strUserpwd & ";SERVER=X007.ORACLEDB.C.COM;"
tdflinked.SourceTableName = "TABLENAME"
DBS.TableDefs.Append tdflinked
RST.Close

<<<<end>>>>

Thanks,
Shawn
Start Free Trial