How to create DSN-Less Connection to AS400

[luilloxp]

Hi, I need to create a link for a table that reside in the AS400 using
a dsn-less connection. I'm using a code that create a link using a
dsn-less connection to a sql server and this works fine, but I can see
that for the AS400 is a very diferent. The code that I used to create
a link to a sql server database is:

In a standard module:

'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a
DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in
the current database
'// stRemoteTableName: Name of the table that you are linking to
on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are
linking to
'// stUsername: Name of the SQL Server user who can connect to SQL
Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String,
stRemoteTableName As String, stServer As String, stDatabase As String,
Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";Trusted_Connection=No"
Else
'//WARNING: This will save the username and the password with
the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" &
stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName,
dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " &
Err.Description

End Function


And in the onLoad event form:

'If AttachDSNLessTable("authors", "authors", "(local)", "pubs", "",
"") Then
If AttachDSNLessTable("dbo_att_employees", "dbo.att_employees",
"Kronos", "ITS", "sa", "sa") Then
MsgBox "All is okay"
'// All is okay.
Else
'// Not okay.
MsgBox "Not okay"
End If

 

[Douglas J. Steele]

Based on whatCarl Prothman has at
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForAS400 try

stConnect = "ODBC;Driver={Client Access ODBC Driver (32-bit)};" & _
"System=" & stServer & ";" & _
"Uid=" & stUsername & ";" & _
"Pwd=" & stPassword