Importing an AS400 table

[karen scheu via AccessMonster.com]

I have successfully created an ODBC datasource for my AS400 connection. I
can link to my AS400 library and import or link the table that I want. What
I need to do is schedule access to kick off daily and import an AS400 table,
then run queries to update the access master table. I don't know how to
accomplish this. I figured that I need to use a connection string to pass
the AS400 user id & password, etc., but I don't know how to do this. Can
someone point me in the right direction.

Thanks very much.

 

[Guest]

Like this with DAO

Public Function fnRelinkODBCTables()
On Error GoTo fnRelinkODBCTables_Err
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim I As Integer
Dim S As String

S = "ODBC;"
S = S & "DSN=MyDSN;"
S = S & "UID=MyUsername;"
S = S & "PWD=MyPassword;"

Set db = CurrentDb()
For I = 0 To db.TableDefs.Count - 1
Set td = db.TableDefs(I)
If (td.Attributes And dbAttachedODBC) Then
db.TableDefs(I).Connect = S
db.TableDefs(I).RefreshLink
' Only one refresh required
Exit For
End If
Next I

...
...
...
etc

or like this with ADO

http://www-912.ibm.com/s_dir/slkbase.NSF/0/c52dfe451b37518086256931005499ff?OpenDocument

Hope this helps ...