rs.Update method failing when adding a new row

[Richard Muskett]

Hi,

I have just installed mySQL 5.0.22. I am using the 3.51.12 driver. I had a functional database in Access (no errors). I have exported my access tables to MySQL via ODBC and have set them up as linked tables in Access.

All my queries (all VB in Access forms/modules) work fine when retrieving data/updating data. Whenever I try to add a new record I get an error about a SQL Syntax error. I discovered that after my import into mySQL the auto increment primary key field was no longer auto increment - I changed this in MySQL Admin and then was able to add new rows directly thru mySQL Query browser (INSERT .... etc) which I couldn't do previously. However this has not fixed my problem from access.

Here is a sample of the code I am using. My only change from the working Access code is to change the connection to the mySQL ODBC connection. The error always comes on the rstBest.Update command saying that the SQL syntax isn't valid. Any help much appreciated - I am totally stuck on this.

Set dbs = CurrentDb
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "DATABASE=2006;" _
& "UID=******;PWD=*******;OPTION=3"

cnn.Open

strsql = "SELECT tblBestTeam.Forward1, tblBestTeam.Forward2, tblBestTeam.Forward3, "
strsql = strsql & "tblBestTeam.ForwardTotal, tblBestTeam.ForwardTeam, "
strsql = strsql & "tblBestTeam.VintagersForwardTotal FROM tblBestTeam;"

rstBest.Open strsql, cnn, adOpenStatic, , adCmdText

rstBest.AddNew
rstBest![Forward1] = "Whatever"
total = rstPlayers![Forward_average]
rstBest.Update
rstBest.Close

cnn.Close