The Andrew N. Wiggins Consultancy

Contact me at webmaster@anw.biz

Experimental Site

 

This site designed by Byg Software Ltd

 

The ANW.BIZ Home Page

AS400 and ODBC

 
The data source name is that of the library where the tables reside.
 
Code Extract (Using VBA / MsQuery)
    ThisWorkbook.Activate
    
    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    '' Open the database
    Set dbs = OpenDatabase("DT2TEST", _
                dbDriverCompleteRequired, True, _
                "ODBC;DSN=DT2TEST;UID=MYIDHERE;PWD=MYPASSWORD;")
            
    vtSql = ""
    vtSql = vtSql & " SELECT MID(CLMN,1,3) , Count(*)"
    vtSql = vtSql & " FROM AUDITPF"
    vtSql = vtSql & " GROUP BY MID(CLMN,1,3);"

    Set rs = dbs.OpenRecordset(vtSql)

    With ThisWorkbook.Sheets("Sheet1")
        With .Cells(1, 1)
            .CurrentRegion.Clear
            '' Copies the contents of a DAO Recordset object onto a worksheet
            numberOfRows = .CopyFromRecordset(rs)
        End With
    End With
 

Notes
The AS400 flavour of SQL would expect SUBSTR instead of MID.This is probably because SUBSTR was already in use elsewhere within the Access/Jet environment when the SQL bit was written