Converting from Access to MySQL

[PRD]

Hello, I wrote a simple Module to update records in a table (called 'Labeldata') and it worked perfectly. However, we recently converted this Access table to a MySQL table (with identical data structure) and now my Module does not work. We also installed ODBC on my PC. Below is a copy of the actual Module code, can you tell me what I have to change (perhaps the 'Set dbs' statement) to get the Module to work? Thank you for your time.

Option Compare Database
Option Explicit
 
 
Function UpdateDatabase (PassOldGenus As String, PassOldSpecies As String, PassNewGenus As String, PassNewSpecies As String, PassTodaysDate As String)
 
 
' Updates records in Labeldata table'
 
 
Dim dbs As Database
Dim Labelsold As Recordset
Dim Lblfld As Field
Dim label As Integer
Dim Labelstdf As TableDef
Dim fieldtitle As Variant, fieldnum As Integer
 
 
Set dbs = CurrentDb
Set Labelstdf = dbs.TableDefs!Labeldata
Set Labelsold = dbs.OpenRecordset("Labeldata")
 
Labelsold.MoveFirst
 
Do Until Labelsold.EOF
 
   If Labelsold![Documented_Genus] = PassOldGenus Then
      If Labelsold![Documented_Species] = PassOldSpecies Then
 
         Labelsold.Edit
 
         Labelsold![SourceDB] = "SynonymPGM"
         Labelsold![Date_Of_Entry] = PassTodaysDate
         Labelsold![Documented_Genus] = PassNewGenus
         Labelsold![Documented_Species] = PassNewSpecies
 
         Labelsold.Update
 
      End If
   End If
 
Labelsold.MoveNext
Loop
 
End Function

 

[jdraw]

I haven't done this for a few years, but the approach , if you are using Access as the Front End, from your PC would be to

Create a DSN for the MySQL database (SystemAdmin- ODBC)
Link the MySQL table to Access(Access Linked Table Manager)
Open the MySQL in Access (Access GUI)

 

[boblarson]

Next time, please use code tags for any code over a few lines.

 

[gemma-the-husky]

its this

Set Labelsold = dbs.OpenRecordset("Labeldata")


with a SQL set, you need another parameter. I think it is dbseechanges

Note that if you error trap stuff like this, access would tell you that this is the problem

 

[PRD]

Gemma-the-husky -

I Googled dbseechanges and found a Weblink which says I should do one of the following:

Set Labelsold = dbs.OpenRecordset("Labeldata", dbOpenDynaset)

or

Set Labelsold = dbs.OpenRecordset("Labeldata", dbOpenDynaset,
dbSeeChanges)


I will try them on Monday and let you know what happens. Thanks for your help.

 

[Steve R.]

Below some sample code for connecting MS ACCESS to MS SQL Server through an ODBC connection and linked tables. I have a "primitive" connection to MYSQL (using the same pattern) that works. My MYSQL project seems to be one of those "perpetual" projects, that I have to periodically re-kick-start.

Set BuildListRS = CurrentDb.OpenRecordset("SELECT * FROM NMFS_RegionalList WHERE NMFSActive ORDER BY NMFSRegionName;", dbOpenDynaset, dbSeeChanges)

Example for appending data. It took me a lot of searching to find out that the "+" was the secret ingredient.

Set RSTnew = CurrentDb.OpenRecordset("SELECT * FROM Consistencymain", dbOpenDynaset, dbAppendOnly + dbSeeChanges)

 

[gemma-the-husky]

look at Access help on RECORDSET - it talks about all the settings in there.

as I say, if you error trap, the error message says explicitly something like "you must use dbseechanges with a SQL recordset"

I was trying to convert an app to run under SQL and Jet, and I have hundreds of these to change. I have to set a boolean to say (effectively)

if sqlsystem then
   db.openrecordset, dbseechanges
else
   db.openrecordset
end if

 

[PRD]

I am embarrassed to say that there really was no problem with the OpenRecordSet statement, both (“Labeldata”) and (“Labeldata”, dbDynaset, dbSeeChanges) work fine. The problem was Labeldata has over 83,000 records in it and when I ran the Module when Labeldata was an Access Table it took only 8-10 seconds to read through it. But when I changed it to the MySQL Table it took over 2 minutes to read the 83,000 records and so I ‘assumed’ there was a problem. It was only when I set up an error handling routine and stepped it through the debugger that I realized the Module was working correctly. And so I apologize for taking up your time, I will certainly do more research if/when I have another problem. Sorry about that.