coding a module to programmatically link Oracle tables to my Access db

[collinsHawkeye]

I am trying to code programmatically the ability to link tables from Oracle into Access 97 using the Class Module. I believe I'm on the right track, however, I keep getting errors saying stuff about data types and what not. Here is what I have.

Also, once the code compiles, how do I implement it? Do I just RUN IT, (F5) or do I have to call the function elsewhere. I'd like to perform this operation for several tables if possible.

Please advise if you can help.

here's the code I have:


Option Compare Database
Option Explicit
Dim strTableName As String
Public db As Database
Public tdef As TableDef


Public Function AttachTable() As Variant
strTableName = "B399.BUS"

On Error GoTo AttachTable_Err

Dim strConnect As String
Dim strTemp As String

Set db = CurrentDb()
strConnect= "ODBC;DSN=***;UID=*****" &_
PWD=*******;DBQ=****"

'Name Access Table
Set tdef = db.CreateTableDef("Bus", "", strTableName, strConnect)

db.TableDefs.Append tdef


AttachTable_Exit:
Exit Function

AttachTable_Err:
MsgBox "Error: " & Str(Err) & " - " & Error$ & " occured in global module."
Resume AttachTable_Exit

End Function