Database access

Posted by Herman on May 10, 2001 10:26 PM

anybody has sample code to connect to an AS400 and connect to a database on this machine.
so i can use sql to retrieve data

i am stuck with this one.

greetings herman

Posted by Kevin James on May 13, 2001 12:23 PM

greetings herman,

You might research to see if there are any ODBC drivers for AS400. Five years ago, when I was taking AS400 data down to a PC, we had to export the data.

The issues are: EBCDIC vs ASCII and how the AS400 uses subfiles. Talk to your midrange support people for more info.

Kevin

Posted by Herman on May 13, 2001 10:44 PM

What i am doing at the moment is taking the data through a client access session, this is odbc.
but i want to do it within vba and i don't know how to do this.

thanx for your answer but if anyone else knows how to do it within vba please help me.

Posted by doug on May 25, 2001 6:59 AM

You will need an ODBC driver for this to work..
With ActiveSheet.QueryTables.add(Connection:=Array(Array( _
"ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=SYSTEMNAME;CMT=0;DBQ=LIBRARYNAME;NAM=0;DFT=0;DSP=0;TFT=0;TSP=0;DEC=0;XDYNAMIC=0;" _
), Array( _
"RECBLOCK=0;BLOCKSIZE=8;SCROLLABLE=0;TRANSLATE=1;LAZYCLOSE=0;LIBVIEW=0;REMARKS=0;CONNTYPE=0;SORTTYPE=0;LANGUAGEID=ENU;SORTWEIGHT" _
), Array("=0;PREFETCH=0;MGDSN=0;")), Destination:=Range("A1"))
.Sql = Array( _
"SELECT FILENAME.FIELD, FILENAME.FIELD" & Chr(13) & "" & Chr(10) & "FROM SYSTEMNAME.LIBRARYNAME.FILENAME FILENAME" & Chr(13) & "" & Chr(10) & "WHERE (FILENAME.FIELDNAME='filtervariable')" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
That should work...