Unanswered: Accessing MySQL through ADODB recordset
Not sure if this is the correct board for this but my feeling is it's more of an Access problem than a MySQL one!
I have an Access front end to a MySQL database using linked tables through the MySQL ODBC driver.
I have a query that I can run in MySQL query browser and it works fine. I then created the same query in Access 2003 and again it works fine. The problem is I want to run some VBA code looping through the records so I have a form opening up an ADODB recordset like this:
Dim strSQL as String
Dim rsNewProducts As New adodb.Recordset
strSQL = "SELECT STOCK.STOCK_CODE, STOCK.DESCRIPTION, STOCK.SALES_PRICE, STOCK.QTY_IN_STOCK, STOCK.UNIT_WEIGHT, products.product_id "
strSQL = strSQL & "FROM STOCK LEFT JOIN products ON STOCK.STOCK_CODE = products.sage_stock_code "
strSQL = strSQL & "WHERE (((products.product_id) Is Null));"
rsNewProducts.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Now this is exactly the same SQL as I tried in MySQL query browser and as an Access 2003 query but when I run it in VBA the recordset returns about 1000 more records than it should!
Where 'MyQuery' is the name of the query in Access 2003. Opening MyQuery directly is fine but again if I try open an ADODB recordset using it it returns many more records than it should.
Is there something wrong with my code? - the SQL can't be wrong as it runs fine except when used from my VBA code. Does anyone hve any example code of opening a recordset in VBA when using MySQL tables?