problems linking Oracle to Access using TransferDatabase

[cheviac]

I am writing a small VB6 program to link an Oracle to an Access database to copy data from Access(my old database) to Oracle(the database I am converting to). I need to do this to test a full blown application that I am converting. I am trying to avoid using import and exports and going through the trouble of manually linking the databases together because I have different copies of the Access database with different types of data. I just want to click one button to get this to work.

Here is a sample of the code that I am trying to get to work. It contains a simple select statement just to see if the link is working.

appAccess.DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=" & _
"{Microsoft ODBC for Oracle};SERVER=elec;;TABLE=OUTAGEUSER.EMERGENCY_DATES", acTable, _
"OUTAGEUSER.EMERGENCY_DATES", "ACCESS_EMERGENCY_DATES", False, True

strSQL = "select * from access_emergency_dates"

rstEmergencyDates.Open strSQL, cnnAccess, adOpenKeyset, adLockOptimistic, adCmdText

From what I understand Access should be able to see the outageuser.emergency_dates table as the access_emergency_dates table. cnnAccess is the connection to the Access database. When I try to open the recordset an error message is displayed that the Jet database engine can not find access_emergency_dates. What I am doing wrong in these statements? Any help will be greatly appreciated.

 

[ddiamond]

I've never never used the TransferDatabase command, so I don't know what you are doing wrong, but here is the code I use to link tables.

Sub LinkTable( _
    SourceTableName As String, _
    DestTableName As String, _
    OdbcConnect As String,
    dbAccess as Database)
    
    Dim t As TableDef
    Dim ts As TableDefs
    
    Set ts = dbAccess.TableDefs
    
    Set t = dbAccess.CreateTableDef(DestTableName)
    t.SourceTableName = SourceTableName
    t.Connect = OdbcConnect
    
    ts.Append t
    ts.Refresh
    
    Set t = Nothing
    Set ts = Nothing
End Sub

For example, to link my oracle Claim_Master table under schema migrate_test, I would do the following:

call LinkTable "migrate_test.claim_master","linked_claim_master","ODBC;DSN=ORACLE;", MyDb

 

[cheviac]

sorry ddiamond,
I got distracted from this problem by something else. I finally figured out what I originally wanted to do: Use an INSERT INTO statement to copy data from an Access database to an Oracle database. Therefore I don't need to worry about linking the databases together. Thanks for the help anyway.