Access to AS400

[dugbel]

I am trying to connect to an As400 from Access to retrieve a recordset.
I am using:
Set ws=dbEngine.CreateWorkspace("ODBCWorkspace","","",dbUseODBC)

stDNS="Name of DNS" 'Set up using ODBC Admin aplet

Set db=ws.OpenDatabase("tempDB", , , "ODBC;DSN=" & stDNS & ";DATABASE=&quot

stSQL="SELECT * FROM BPCSFH.WS40;"

Set rs=db.openrecordset(stSQL, dbOpenSnapshot)

That is where it bomps out with &quot;Error:3146; 37000: [IBM][Client Access Express ODBC Driver(32-bit)][DB2/400 SQL]SQL0104 - Token ; was not valid, Valid tokens: <END-OF-STATEMENT>, &quot;

After I set the db it asks for Log-In and accepts valid Log-In but doesn't like the recordset?

Any clues??

I also found that I could not create a file DSN that worked only a System or User. That will cause problems later on when we distribute the Access application out to the branch office in Melbourne?

 

[dugbel]

I have solved part of the problem. AS400 didn't want a semicolon at the end of the SQL.

Anyone know what I can do about the File DSN?