Sql Insert query in Access vba

[Boss1b]

Hi, all! I have an Access 2007/2010 database that is being converted to use SQL tables. I have one insert query that is giving problems. By using debug.print, I can grab the query, drop it into SQL Server Mgt Studio and run it w/no problems. But when trying to run it thru Access, it errors out with "Data type mismatch in criteria expression.". Anyone have any ideas what to look for? Why would the query run in sql mgr but not in vba?
Thanks!

 

[boblarson]

Post the SQL for it and we'll see.

 

[Boss1b]

Thanks for the help, Pat! I appreciate it! The majority of the queries seem to be functioning correctly, just a couple I've found so far that are being obstinate. I'll make some changes and see what develops.
Thanks again!
Earl

 

[Boss1b]

Bob, Here is the SQL.

rs.Source = SqlStr
rs.Open

Do While rs.EOF = False
iCnt = iCnt + 1
sUserId = FillUserId()

SqlStr = "Insert into tempExportsForMerge values (" & iCnt & "," & rs(0) & ",'" & rs(1) & "','" & rs(2) & "','" & rs(3) & "',"
SqlStr = SqlStr + "'" & rs(4) & "','" & rs(5) & "','" & rs(6) & "','" & rs(7) & "',"
SqlStr = SqlStr + "'" & rs(12) & "','" & rs(13) & "','" & rs(14) & "','" & rs(15) & "','" & rs(16) & "','" & rs(17) & "','" & rs(18) & "',"
SqlStr = SqlStr + "'" & rs(19) & "','" & rs(20) & "','" & rs(21) & "',"
SqlStr = SqlStr + "'" & rs(22) & "','" & rs(23) & "','" & rs(24) & "','" & rs(25) & "','" & rs(26) & "','" & rs(27) & "','" & rs(28) & "',"
SqlStr = SqlStr + "'" & rs(29) & "','" & rs(30) & "','" & rs(32) & "','" & rs(31) & "','" & rs(33) & "','" & rs(34) & "','" & rs(35) & "',"
SqlStr = SqlStr + "'" & rs(36) & "','" & rs(37) & "','" & rs(38) & "','" & rs(39) & "','" & rs(9) & "','" & rs(10) & "','" & rs(11) & "',"
SqlStr = SqlStr + "'" & rs(40) & "','" & rs(41) & "','" & rs(42) & "'," & rs(43) & "," & rs(44) & ",'" & rs(45) & "','" & rs(46) & "',"
SqlStr = SqlStr + "" & rs(47) & "," & rs(48) & ",'',"
SqlStr = SqlStr + "'" & rs(49) & "'," & rs(50) & "," & rs(51) & ",'','','" & rs(52) & "','" & rs(53) & "','" & rs(54) & "','" & rs(55) & "',"
SqlStr = SqlStr + "'" & rs(56) & "','" & rs(57) & "','" & rs(58) & "','','" & rs(59) & "','" & rs(60) & "','" & rs(71) & "',"
SqlStr = SqlStr + "'','','','','','','','','','" & rs(8) & "','','','','','" & rs(61) & "','','','','','" & rs(62) & "'," & rs(63) & ",'" & rs(64) & "',"
SqlStr = SqlStr + "'" & rs(65) & "','" & rs(66) & "'," & rs(67) & "," & rs(68) & ",'" & rs(69) & "',"
SqlStr = SqlStr + "'','','','','','','','','" & rs(70) & "','" & rs(72) & "','" & rs(73) & "','" & rs(74) & "',"
SqlStr = SqlStr + "'" & rs(75) & "','" & rs(76) & "','" & rs(77) & "','" & rs(78) & "','" & rs(79) & "','" & rs(80) & "',"
SqlStr = SqlStr + "'" & rs(81) & "','" & rs(82) & "','" & rs(83) & "')"
Debug.Print SqlStr
cn.Execute SqlStr

' DoCmd.RunSQL SqlStr
rs.MoveNext
Loop


I have tried both cn.Execute SqlStr and DoCmd.RunSQL SqlStr with similar results.

Thanks for looking at it!
Earl

 

[Boss1b]

Pat,
Understand. In the string, all text fields were '" & rs(?) & "', (single quote, double quote, & ... & dq, sq) the numeric fields were " & rs(?) & " (dq & ... & dq). And there are dates referenced as well. Your last 3 lines probably hold the key. I will config accordingly and try it!
Thanks!