AS400 DB2 Data Type Conversion Error

[sttjones]

I am reporting on data from DB2/AS400. I created simple queries that run on a regular MS Access query and a Pass through query.

I could get the VBA to open the file without a query string and loop through the recordset 1 record at a time. I can't get a query string to work.

Every combination I've tried using a SQL query string produces Run Time Error 3421 - Data type conversion error. As you can see, I'm using a trial of just a single field. I have tried both a string field and numeric field.

Yesterday I searched through every posting I could find on MS ACCESS / DB2 and could not find an example with code provided.

Here is my VBA code with the different combinations I've tried:

Sub PassThroughLoop()
 
    Dim sSQL As String
    Dim rst As DAO.Recordset
 
'   AS400 SQL
'    sSQL = "SELECT BJDOCD FROM ID3SCVDTA.COVDTLP WHERE ID3SCVDTA.COVDTLP.BJDOCD='00CC157640'"
    sSQL = "SELECT ID3SCVDTA.COVDTLP.BJACSG FROM ID3SCVDTA.COVDTLP WHERE ID3SCVDTA.COVDTLP.BJACSG=1298677"
 
'   MS Access SQL
'    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJDOCD FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJDOCD ='00CC157640'"
'    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJACSG FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJACSG=1298677;"
'MsgBox "sSQL string is: " & sSQL
 
    Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecordset(sSQL)
 
    With rst
        .MoveFirst
        Do Until .EOF
            MsgBox "Policy " & rst!BJDOCD
            .MoveNext
        Loop
    End With
 
    rst.Close
    Set rst = Nothing
End Sub
 

This looks like a helpful group and I would be thankful for help and advice.

[PhilOfWalton]

Your MsgBox is asking to display the value of rst!BJDOCD, but that field is nor defined in your SQL in the one you are currently using (but is defined in the first SQL attempt).

What is the name of your table. In some places it appears to be ID3SCVDTA.COVDTLP with a dot, and in other places ID3SCVDTA_COVDTLP with an underscore.

I am reasonably sure that ID3SCVDTA.COVDTLP with a dot is an illegal table name.

My guess is (Note 2 underscores)

sSQL = "SELECT BJDOCD FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJDOCD='00CC157640'"
 

Phil

[sttjones]

Thanks for the hint and looking at this. I tried replacing the . with _ and I still get the same Run-time error '3421': Data Type Conversion error. I'm using table COVDTLP in the AS400 database. If I paste the sSQL string into an Access Pass Through query it runs with . but not with _ I changed the message box, though the code doesn't get to here yet. It errors on: Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecor dset(sSQL).

The table opens if I change the statement to: Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecor dset but that doesn't help me as I am using the SQL to get just 3 records (in this case). I simplified the code some and tried the sSQL string with both . and _

Sub PassThroughLoop()
'   3/2/2017 - SJ - This sort of works. The dataset is opened and it moves through each record without the sSQL string.
    Dim sSQL As String
    Dim rst As DAO.Recordset
 
'   AS400 SQL
'    sSQL = "SELECT BJDOCD FROM ID3SCVDTA.COVDTLP WHERE ID3SCVDTA.COVDTLP.BJDOCD='00CC157640'"
    sSQL = "SELECT BJACSG FROM ID3SCVDTA.COVDTLP WHERE BJACSG=1298677"
 
'   MS Access SQL
'    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJDOCD FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJDOCD ='00CC157640'"
'    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJACSG FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJACSG=1298677;"
MsgBox "sSQL string is: " & sSQL
 
    Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecordset(sSQL)
 
    With rst
        .MoveFirst
        Do Until .EOF
            MsgBox "Key " & rst!BJACSG
            .MoveNext
        Loop
    End With
 
    rst.Close
    Set rst = Nothing
End Sub
 

[PhilOfWalton]

OK I think I see your problem

Add yo your code

    Dim MyDb as Database
 
    Set MyDb = CurrentDb
 
 

Change
Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecor dset(sSQL)
to

Set Rst = MyDb.OpenRecordSet(sSQL)

Now there is obviously confusion on the table name. It is whatever it shows up as in your list of tables in the navigation pane.

The format of your SQL should be (to get all the fields)
sSQL = "SELECT MyTableName.* FROM MyTableName WHERE ......."


Phil

[sttjones]

You are awesome Phil. Thank you. Your suggestions work as you expected. I'm getting the records I'm expecting to see. Now I can go on to build a more complicated query. Here is my code that works in the event someone searches for the same thing:

Sub PassThroughLoop()
'   3/6/2017 - SJ - This code opens a DB2 table when you have the correct ODBC connection. The database table is opened and the code moves through each record returned with the sSQL string.
    Dim sSQL As String
    Dim rst As DAO.Recordset
    Dim MyDb As Database
 
    Set MyDb = CurrentDb
 
    sSQL = "SELECT ID3SCVDTA_COVDTLP.* FROM ID3SCVDTA_COVDTLP WHERE BJACSG=1298677"
 
'MsgBox "sSQL string is: " & sSQL
 
    Set rst = MyDb.OpenRecordset(sSQL)
 
    With rst
        .MoveFirst
        Do Until .EOF
            MsgBox "Key " & rst!BJDOCD & " " & rst!BJACSG
            .MoveNext
        Loop
    End With
 
    rst.Close
    Set rst = Nothing
End Sub