By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

AS400 DB2 Data Type Conversion Error

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Sub PassThroughLoop()
  2.  
  3.     Dim sSQL As String
  4.     Dim rst As DAO.Recordset
  5.  
  6. '   AS400 SQL
  7. '    sSQL = "SELECT BJDOCD FROM ID3SCVDTA.COVDTLP WHERE ID3SCVDTA.COVDTLP.BJDOCD='00CC157640'"
  8.     sSQL = "SELECT ID3SCVDTA.COVDTLP.BJACSG FROM ID3SCVDTA.COVDTLP WHERE ID3SCVDTA.COVDTLP.BJACSG=1298677"
  9.  
  10. '   MS Access SQL
  11. '    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJDOCD FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJDOCD ='00CC157640'"
  12. '    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJACSG FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJACSG=1298677;"
  13. 'MsgBox "sSQL string is: " & sSQL
  14.  
  15.     Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecordset(sSQL)
  16.  
  17.     With rst
  18.         .MoveFirst
  19.         Do Until .EOF
  20.             MsgBox "Policy " & rst!BJDOCD
  21.             .MoveNext
  22.         Loop
  23.     End With
  24.  
  25.     rst.Close
  26.     Set rst = Nothing
  27. End Sub
  28.  
This looks like a helpful group and I would be thankful for help and advice.
Mar 3 '17 #1

✓ answered by PhilOfWalton

OK I think I see your problem

Add yo your code
Expand|Select|Wrap|Line Numbers
  1.     Dim MyDb as Database
  2.  
  3.     Set MyDb = CurrentDb
  4.  
  5.  
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

Share this Question
Share on Google+
4 Replies


PhilOfWalton
Expert 100+
P: 1,430
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)
Expand|Select|Wrap|Line Numbers
  1. sSQL = "SELECT BJDOCD FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJDOCD='00CC157640'"
  2.  
Phil
Mar 3 '17 #2

P: 4
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 _
Expand|Select|Wrap|Line Numbers
  1. Sub PassThroughLoop()
  2. '   3/2/2017 - SJ - This sort of works. The dataset is opened and it moves through each record without the sSQL string.
  3.     Dim sSQL As String
  4.     Dim rst As DAO.Recordset
  5.  
  6. '   AS400 SQL
  7. '    sSQL = "SELECT BJDOCD FROM ID3SCVDTA.COVDTLP WHERE ID3SCVDTA.COVDTLP.BJDOCD='00CC157640'"
  8.     sSQL = "SELECT BJACSG FROM ID3SCVDTA.COVDTLP WHERE BJACSG=1298677"
  9.  
  10. '   MS Access SQL
  11. '    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJDOCD FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJDOCD ='00CC157640'"
  12. '    sSQL = "SELECT ID3SCVDTA_COVDTLP.BJACSG FROM ID3SCVDTA_COVDTLP WHERE ID3SCVDTA_COVDTLP.BJACSG=1298677;"
  13. MsgBox "sSQL string is: " & sSQL
  14.  
  15.     Set rst = CurrentDb.TableDefs("ID3SCVDTA_COVDTLP").OpenRecordset(sSQL)
  16.  
  17.     With rst
  18.         .MoveFirst
  19.         Do Until .EOF
  20.             MsgBox "Key " & rst!BJACSG
  21.             .MoveNext
  22.         Loop
  23.     End With
  24.  
  25.     rst.Close
  26.     Set rst = Nothing
  27. End Sub
  28.  
Mar 4 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
OK I think I see your problem

Add yo your code
Expand|Select|Wrap|Line Numbers
  1.     Dim MyDb as Database
  2.  
  3.     Set MyDb = CurrentDb
  4.  
  5.  
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
Mar 4 '17 #4

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Sub PassThroughLoop()
  2. '   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.
  3.     Dim sSQL As String
  4.     Dim rst As DAO.Recordset
  5.     Dim MyDb As Database
  6.  
  7.     Set MyDb = CurrentDb
  8.  
  9.     sSQL = "SELECT ID3SCVDTA_COVDTLP.* FROM ID3SCVDTA_COVDTLP WHERE BJACSG=1298677"
  10.  
  11. 'MsgBox "sSQL string is: " & sSQL
  12.  
  13.     Set rst = MyDb.OpenRecordset(sSQL)
  14.  
  15.     With rst
  16.         .MoveFirst
  17.         Do Until .EOF
  18.             MsgBox "Key " & rst!BJDOCD & " " & rst!BJACSG
  19.             .MoveNext
  20.         Loop
  21.     End With
  22.  
  23.     rst.Close
  24.     Set rst = Nothing
  25. End Sub
  26.  
Mar 6 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.