Wednesday, October 28, 2009

Pitfalls of AS400 and MS Access

Where I work we have an AS400 system that provides our DB.  However, our IT admins decided to use a rather uncommon naming scheme. Our Libraries "folders if your looking for a Windows equal" have a period in their name.

Well you may, or may not, know that the period is how the AS400 system tells the difference between a library and a member when using the *SQL naming scheme. So usually you have something like this:


FNTUSR.ACCNUMB


Then you have the FNTUSR library and the ACCNUMB member of that library. But if you have something like this:


CHK.ENTR.BILLNO


You basically have garbage, at least as far as ODBC goes. The problem actually lies within the ODBC driver that IBM includes. It doesn't follow the same resolution method as the actual AS400 system, maybe because it is local and ODBC is remote but details aren't really that important. The ODBC driver goes out and yanks the CHK member during the resolution and asks for the BILLNO member, skipping completely the ENTR part of the name.

Needless to say this may prevent you from using ODBC to connect to the AS400 system. If there was only a way to pass the name as one chuck so that the ODBC driver would stop parsing the second period and placing the resulting token in the garbage.

Oh wait, there is...

The wonderful world of Microsoft allows you to pass a chuck of information into the ODBC parser, bypassing the tokenizer, by surrounding it with quotes! Hooray! But wait! In addition to the wonderful-ness that is Microsoft. None of their GUI offerings allow you to give quotes (except Excel, go figure). So if you are wanting to connect to an AS400 system that is administer by half witted admins that place extra periods all over the place in an AS400 system (look *SYS != *SQL, you can not use *SYS when you are brainwashed into thinking that it is the same as *SQL). You will need to go the VBA route (hooray... please shoot me now).

For those of you not really familiar with the VBA way of doing things, it involves a lot of pain. Please observe (I've named the DSN as400_connect):

Public wspAS400 as Workspace
Public cntAS400 as Connection
Public dbAS400 as Database

Public Function IWroteThisFunctionBecauseOurAdminsAreDumb()

Dim qryDef as QueryDef
Dim rs as RecordSet
Dim SQLString as String

On Error GoTo ConnectionError
Set wspAS400 = DBEngine.CreateWorkspace("idiot", "", "", dbUseODBC)
Set dbAS400 = wspAS400.OpenDatabase("idiot_db", , , "ODBC;DSN=as400_connect;DATABASE=SM456J12")
Set cntAS400 = wspAS400.Connections(0)

On Error GoTo QueryError
SQLString = "SELECT * FROM ""CHK.ENTR""/BILLNO"
Set qryDef = cntAS400.CreateQueryDef("", SQLString)
Set rs = qryDef.OpenRecordset

DoCmd.SetWarnings False

'Insert you records into you MS Access DB here

DoCmd.SetWarnings True

ConnectionError:
MsgBox "Something got F***ed up!"
GoTo CleanUp

QueryError:
MsgBox "Something got F***ed up!"
GoTo CleanUp

CleanUp:

On Error Resume Next
rs.Close
qryDef.Close

Set rs = Nothing
Set qryDef = Nothing

cntAS400.Close
wspAS400.Close

Set cntAS400 = Nothing
Set dbAS400 = Nothing
Set wspAS400 = Nothing

End Function


That should get you started on getting crap out of the AS400 system and into your MS Access database. Notice that I am using the *SYS naming scheme. Try both the *SYS and *SQL naming scheme (library/member, library.member) to see which one your AS400 system is using (or at least will resolve). You can change that bit of information in the DSN entry on your system under ODBC Administration in Windows.

PS: Please make sure you have the damn ODBC driver from IBM before you even try setting up the DSN.

No comments: