DSN less ODBC connection to AS/400 1

[NVSbe]

Hi there. I'm trying to make a DSN less connection to a table in DB/2. I'm using Access 97, and the IBM client access ODBC driver for this. I want to do it this way because otherwise I'd have to add a DSN on all machines.

This is my code to link the table:

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.

sConnString = "ODBC;" & _
               "Driver={Client Access ODBC Driver (32-bit)}; " & _
               "System={AS400}; uid=xxx; pwd=xxx"
              
              
' Create and open an ADOX connection to Access databased

Set oCat = New ADOX.Catalog
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=D:\shared\sharefiles\KAL.mdb"
' Create a new Table object
Set oTable = New ADOX.Table

With oTable
    .Name = "test2"
     Set .ParentCatalog = oCat
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Remote Table Name") = "LEMDTA.PRSKAL"
    .Properties("Jet OLEDB:Cache Link Name/Password") = False
    .Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

Upon the line where the table would be appended, I get error -2147467259 (80004005). Undefined error. I've looked all over the web, and seen many explanations for this error, ranging from invalid chracters in the base table name, to the fact that it is read only... None of the suggestions I found helped; Anyone got any clue perhaps?

--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[NVSbe]

I fixed the problem using a file DSN on a share all clients can see, like I read in another thread. I had to cut back on some options generated by the ODBC-utility, but in the end, that worked like a charm.

--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[koresnordic]

Nice one - been struggling wiht this problem for years. Having to create DSN in each machine. Will try this and see if it works for me. Thanks for letting us know how you did it.



Graham