I've worked on this for many hours and found basically nothing, i'm starting to think that this maybe a huge flaw in the

program language itself since there maybe no fix for it.

Using:
- compiler->Visual Basic 6.0
- o/s->Windows XP SP2
- reference->Microsoft ActiveX Data Objects

Checked thoroughly:
- I'm using service pack 2 windows xp which means its fully updated.
- every instance of recordset when opened is closed.
- The query works perfectly.
- In database.mdb the position field is a number and has a value.
- both sql and database strings passed into function are always valid.

Problem?:
- It just won't open inside mysql_query function


Info:
Every recordset i've used in this program seems to work
perfect except when i use the '=' sign or 'LIKE'
when compairing values in the sql string. I've checked
the sql string to see if it works and it does perfectly,
but it gives this error when used in the function.
Out of curiousity i removed the 'WHERE position=1' part
and it opened successfully.. to state this more blunt
'WHERE position=1' seems to be the problem.


SQL = "SELECT * FROM tblArmors WHERE position=1"
DataBase = "./database.mdb"

Function MySQL_Query(SQL As String, DataBase As String) As ADODB.recordset

    Dim conConnection As New ADODB.Connection
    Dim cmdCommand As New ADODB.Command
    Dim rstRecordSet As New ADODB.recordset

    conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\" & DataBase & ";Mode=Read|Write"
    conConnection.CursorLocation = adUseClient
    conConnection.Open

    With cmdCommand
        .ActiveConnection = conConnection
        .CommandText = SQL
        .CommandType = adCmdText
    End With

    With rstRecordSet
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmdCommand
    End With

    Set MySQL_Query = rstRecordSet

End Function


Final Note:
If any of you have any ideas on what i can try next, or if you somehow have a solution, pls post.

Forgot to say this, When the program crashed with the error in debug mode it pointed to the following line in mysql_query().

".Open cmdCommand"

just replace funtion decalriotnto this
Function MySQL_Query(SQL As String, DataBase As String) As New ADODB.recordset
 it will work

I am having the same problem, and when I try to add the as new into the function call I get an error: Expected: type name

If i remove the new it runs and I just hit the problem of no where statement in the sql.

Any luck?

I am having the same exact problem too and cannot find a solution. Mine is with an Access database though.

If anyone has resolved this, please post.
Thanks!

First, when you use the LIKE predicate in ADO, you must use ANSI wildcards - % instead of *. And you don't need the command object. A simple

  rstRecordset.Open SQL, conConnection, adOpenStatic, adLockOptimistic

.. should do it.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

i think the problem is the fields , maybe the same as the reserved word.
try changing the fields or placeing a bracket like: [section] when querying...

I had the same problem, turned out to be a clash with a vb6 keyword, I had a database field called 'position' which I renamed and query worked fine.

Hello