dsn-less odbc connection to DB2 tables

[hespenheide]

Hi

I have an autoexec macro in Access 2007 designed to create linked tables to a DB2 database. If I use either a system DSN or user DSN file (via the linked table manager), the linking works fine. However, if I attempt the linking in VBA, the app pops up a "Connect to DB2 Database" window where it is asking for a "Database alias" before it will allow me to connect.

Here is a snippet of the code. The stars represent real values:

Dim db As Database
Set db = CurrentDb
Dim tdf As TableDef

'Remove all ODBC tables from the table definitions
For a = CurrentDb.TableDefs.Count - 1 To 0 Step -1
If Left(CurrentDb.TableDefs(a).Connect, 4) = "ODBC" Then
db.TableDefs.Delete db.TableDefs(a).Name
End If
Next a

Application.RefreshDatabaseWindow

Set tdf = db.CreateTableDef("ACCESS_TABLE")
tdf.Connect = "ODBC;Driver={IBM DB2 ODBC DRIVER};AUTHENTICATION=SERVER;PORT=60001;HOSTNAME=FLOAT.****.COM;PROTOCOL=TCPIP;DATABASE=******;"
tdf.SourceTableName = "DB2_TABLE"
db.TableDefs.Append tdf

The "Connect to DB2 Database" window pops up when I try to append the new table.

Any help will be greatly appreciated.

Thank you

 

[FoFa]

Do you not have to supply the security when you connect?
SQL server and Oracle connections popup a security window if you do not supply that at connection time. Typically when you setup a USER/SYSTEM/FILE DSN you supply connection security, I don't see that in your connection string.
Not sure about DB2.

 

[l.kyriazakos]

Hello,

I am trying to do the same thing, using exactly the same code from above, but I only get a runtime error 3000 reserved Error (-7778).

Did anybody manage to get this working?

Thanks in advance.