ODBC Connection To DB2 via MSAccess??

[DerSucher]

First time exposure to DB2, and none of the AS400 programmers we have here on site know enough about ODBC to answer my question.... Which is:

Is it possible, and if so- how, to connect to a file in my library on the AS400 via VisualBasic code inside of an MSAccess Database?

1: I have Client Express ODBC Driver for AS400.
2: I have an account on the AS400.
3: I DO Have physical Datafiles in my Library.

;-)

Than you in advance

 

[d3c3as3d]

option A

open new query, DO NOT choose any table, then close table wizard
click query -> sql specific on the file menu
Then click on "pass through"


OPtion B

Dim qdfPT As QueryDef
Dim colSel As String
Dim varitem As Variant

For Each varitem In lstTable.ItemsSelected
' Print value of bound column.
colSel = lstTable.ItemData(varitem)
Next varitem


Set dbs = CurrentDb

Set qdfPT = dbs.QueryDefs("qtableexample&quot
qdfPT.Connect = "ODBC;DATABASE=[odbcsource];uid=zenr;pwd=;dsn=query1"
qdfPT.SQL = "SELECT " & colSel & " FROM DSIM00P GROUP BY " & colSel & " ORDER BY " & colSel & " DESC"

Me.lstExample.RowSource = "QtableExample"
Me.Requery


HTH

 

[EdVassie]

Another alternative...

First of all, make sure you have a ODBC definition configured on your windows box for your AS400 DB2 database.

In Access, Click on the 'File' menu. Then select 'Get External Data' and 'Link Tables'. For the table type, scroll down the list until you see ODBC data sources. Look for the definition of your AS400 DB2 database and select it.

You can then select all the AS400 tables you want to use in your application. They will appear as icons in the Tables tab. You can then write queries as normal. This posting is a personal opinion only, and may not reflect reality.