Help with ODBC Connection

[Wes1961]

I use some code that I found on this site to establish and refresh an ODBC connection to Oracle. And it works great.

The problem I have is that I need to connect to two different sources.

I use the following information to feed the code.

UID = myId
PWD = myPwd
ODBCtable = apps_Ap_table
LocalTable = apps_Ap_table
DSN = Oracle
DBQ = FINPROD_PROD

And:

UID = fbdId
PWD = fbdPwd
ODBCtable = PO_Items
LocalTable = PO_Items
DSN = Oracle
DBQ = Tor_mfg

I can open the database and open either source independently, however, when I try to open the second table I get the following message:

"Microsoft Jet database could not find the object"
"Make sure the object exists and that you spell its name and path correctly."


What I need to do is open the first source and download some information and then "close" it and open the second source. I would love to able to have them both accessible at the same time if possible.

Currently the only way I can do this by closing and reopening the database.

I can post the code but I don't think that is the problem since it will work once either table I try to connect.

Thanks in advance for your help,
Wes


I like work. It fascinates me. I can sit and look at it for hours..."

 

[PHV]

You have to play with 2 different Database objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wes1961]

PHV,

Can you help me a little more? I don't understand what you mean by 2 different database objects.

Thanks.

 

[PHV]

Can you please post your code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wes1961]

Here is the code.

The first time I enter the database I can run the code to attach to either one of the sources/tables above without any problems. However, if I try to run it again for the second source, I get an error (above). Exiting the database and attaching to the second source works.

Function CreateODBCLinkedTables(txtTable As String) As Boolean
   On Error GoTo CreateODBCLinkedTables_Err
   Dim strTblName As String, strConn As String
   Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
   Dim strDSN As String

   ' ---------------------------------------------
   ' Register ODBC database(s).
   ' ---------------------------------------------
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Select * From " & txtTable & " Order By DSN")
   With rs
      While Not .EOF

         strDSN = rs("DSN")
      ' ---------------------------------------------
      ' Link table.
      ' ---------------------------------------------
         strTblName = rs("LocalTableName")
         strConn = "ODBC;"
         strConn = strConn & "DSN=" & rs("DSN") & ";"
         strConn = strConn & "DBQ=" & rs("DBQ") & ";"
         strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
         strConn = strConn & "UID=" & rs("UID") & ";"
         strConn = strConn & "PWD=" & rs("PWD") & ";"
         strConn = strConn & "ASY=" & rs("ASY") & ";"
         strConn = strConn & "TABLE=" & rs("ODBCTableName")
         If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                          dbAttachSavePWD, rs("ODBCTableName"), _
                          strConn)
            db.TableDefs.Append tbl
         Else
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
            tbl.RefreshLink
         End If
  '          Debug.Print strConn

         rs.MoveNext
      Wend
  End With
   CreateODBCLinkedTables = True
   MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
   Exit Function
CreateODBCLinkedTables_Err:
   MsgBox Err.Description, vbCritical, "MyApp"
    Resume Next
   'Resume CreateODBCLinkedTables_End
End Function

Function DoesTblExist(strTblName As String) As Boolean
   On Error Resume Next
   Dim db As DAO.Database, tbl As DAO.TableDef
   Set db = CurrentDb
   Set tbl = db.TableDefs(strTblName)
        If tbl.Attributes And dbAttachedODBC Then
 '          Debug.Print tbl.Name & ", " & tbl.Connect, tbl.SourceTableName
        End If
   If Err.Number = 3265 Then   ' Item not found.
      DoesTblExist = False
      Exit Function
   End If
   DoesTblExist = True
End Function