Solved: Type conversion error with SQL query

[Frenchy646]

Hello wonderful people,

So, I've been fiddling around with a query I was doing in VBA, as I had made a few mistakes ( for some unknown reason to me I put commas instead of "AND" for multiple criteria after the WHERE clause ), but I have now encountered an error I have no idea how to fix.
The error I get is Run-time error 3421, data type conversion error.
My first guess is, I've probably got the wrong end of the stick with regards to DAO and ADO.
If you could help me find out where I've gone wrong, and explain to me why, it would be much appreciated.


Dim sqlQuery As String
Dim myDatabase As New ADODB.Recordset

sqlQuery = "SELECT * FROM [Customer_Identifiers] WHERE ([SVC] = '" & myRecordset.Fields(0) & _
"' AND [Field1]='" & myRecordset.Fields(1) & _
"' AND [Field2]='" & myRecordset.Fields(2) & _
"' AND [Description]='" & myRecordset.Fields(3) & _
"' AND [Field3]='" & myRecordset.Fields(4) & _
"' AND [Field4]='" & myRecordset.Fields(5) & _
"' AND [Field5]='" & myRecordset.Fields(6) & _
"' AND [Field6]='" & myRecordset.Fields(7) & _
"' AND [Field7]='" & myRecordset.Fields(8) & _
"' AND [Field8]='" & myRecordset.Fields(9) & "')"

Set myDataBase = CurrentDb.OpenRecordset(sqlQuery2, CurrentProject.Connection, adOpenStatic)

[Frenchy646]

If you're wondering where myRecordset comes from, just before, I have

Const theFileName As String = "Summary.xls"
Dim theFolder As String
DIm connectString As String
Dim myConnection As Object
Dim myRecordset As Object
Set myConnection = CreateObject("ADODB.Connection")
Set myRecordset = CreateObject("ADODB.RecordSet")

theFolder = Chr(92) & "Username" & Chr(92) & "Personal"

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolder & _
Chr(92) & theFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

myConnection.Open connectString
sqlQuery = "SELECT * FROM [Billing$] WHERE [Field1] Is Not Null;"
myRecordset.Open sqlQuery, myConnection

[Frenchy646]

If anybody was brave enough to scroll down to here, quick question.

Why do many examples on the net, using OpenRecordSet, for example
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Select [ID], [Ln] From [Order Detail] Where ((([Order Detail].[ID]) = [Forms]![Order Data Entry Header]![ID]))")

Only need one parameter in the openRecordSet method ?
When I try and use only one parameter in the above method of my code, I get a runtime error "expecting 3 arguments" ...

[Frenchy646]

I found the answer myself, so no need to help.