ODBC/Linked Tables/Updating Tables

[madhouse]

Ok here's my problem......

I have a table on one of our AS/400 systems called LIBINFO.LIFLIPM which I want to import into an Access 2000 database. I've created a System DSN called LIBINFO and can use the Get External Data -> Import option to manually import the table. However, I need to import the table each day and rather than having to manually use the Get External Data method, is there a way I can create a procedure that can be run when the database is opened? My idea is to have the imported tables deleted at the end of the day when I close the database so that the updated tables can be imported in the morning when the database is opened. I'm ok with how to delete the tables....just need some help on creating the procedure to import the tables. Has anyone got an example of how to do this?

Also, someone else mentioned the idea of having the table on the AS/400 as a Linked Table. So I experimented and added a new Linked Table betwen MS Access and the table on the AS/400 but again I'm not sure how the Linked Table gets updated??

 

[VBslammer]

A linked table contains live data so once linked you don't need to import anything.

If you still want to do daily imports you can use the TransferDatabase method of the DoCmd object.

VBSlammer

Unemployed in Houston, Texas

 

[madhouse]

Have you go an example of the code I need for the TransferDatabase method of the DoCmd object?

 

[VBslammer]

'Need a valid connection string for this to work

Sub ImportODBCTable(ByVal strConnection As String, _
                    ByVal strSource As String, _
                    ByVal strDest As String, _
                    Optional ByVal intType As AcDataTransferType = acImport)
                    
  'Transfer type can be acExport,acImport or acLink
  DoCmd.TransferDatabase intType, "ODBC Database", strConnection, acTable, strSource, strDest, False

  
End Sub

VBSlammer

Unemployed in Houston, Texas