Description:
I have written my VBA-code to download all data from a MySQL table, since the TransferDatabase method is both slow and does too much (copying the structure of the table).
The problem is this code works with small amounts of data (about 120 records), but fails with greater amounts (1500 records). In the latter case, only about 10% of the dats is downloaded.
I can't see the error in my code, besides, it does work in the other database. So, I conclude this to be a bug in MyODBC.
Below is my code, commented in English as much as possible (originally written in Dutch).
One remarkable thing however is, when I add a counter to this code (like: amount=amount+1; msgbox(amount)), I get the perfect amount of records, so equal to the amount on the MySQL server.
Public Sub data_download_sql(TableName As String)
'a sub that's faster then the TransferDatabase method, since this
'sub only downloads the data and inserts it into the already present
'local table
'the string TableName contains the table to be downloaded
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
'set the connection variables
mysql_server = "******"
mysql_username = "*****"
mysql_password = "**********"
mysql_database = "**********"
'and the the connectionstring
connectstring = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & mysql_server & ";" _
& "DATABASE=" & mysql_database & ";" _
& "UID=" & mysql_username & _
";PWD=" & mysql_password & _
";OPTION=1312771"
'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = connectstring
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
'fetch the table
rs.Open "SELECT * FROM " & TableName, conn
rs.MoveFirst
'these two variables are used to create a local SQL INSERT command
Dim FieldNames As String
Dim FieldValues As String
Do Until rs.EOF
'this loop is being repeated until the last record in the specified table
'it creates an SQL INSERT command by checking which field names there are
'in the table and then grabbing the field values for each record
'this data is taken together to create a local INSERT-command
'so this kind of string emerges:
'INSERT INTO TableName ([fieldname1],[fieldname2],...) VALUES ('fieldvalue1','fieldvalue2'....);
'start both variables with a (
FieldNames = "("
FieldValues = "("
x=0
For Each fld In rs.Fields
'in this for...next loop all field names are found and the
'corresponding field values are saved
FieldNames = FieldNames & "[" & fld.Name & "]"
'prevent errors when there's a ' in the field value
apostrof_fix = Replace("" & fld.Value, "'", "''")
FieldValues = FieldValues & "'" & apostrof_fix & "'"
x=x+1
If x <> rs.Fields.Count Then
'a comma is needed, except for the last field name and field value
FieldNames = FieldNames & ","
FieldValues = FieldValues & ","
End If
Next
'close both the field name and field value with a )
FieldNames = FieldNames & ")"
FieldValues = FieldValues & ")"
With CurrentDb
'and insert the entire string with field names and data
.Execute "INSERT INTO " & TableName & " " & FieldNames & " VALUES " & FieldValues & ";"
End With
'go to the next record
rs.MoveNext
Loop
End Sub
How to repeat:
Run the code.
Description: I have written my VBA-code to download all data from a MySQL table, since the TransferDatabase method is both slow and does too much (copying the structure of the table). The problem is this code works with small amounts of data (about 120 records), but fails with greater amounts (1500 records). In the latter case, only about 10% of the dats is downloaded. I can't see the error in my code, besides, it does work in the other database. So, I conclude this to be a bug in MyODBC. Below is my code, commented in English as much as possible (originally written in Dutch). One remarkable thing however is, when I add a counter to this code (like: amount=amount+1; msgbox(amount)), I get the perfect amount of records, so equal to the amount on the MySQL server. Public Sub data_download_sql(TableName As String) 'a sub that's faster then the TransferDatabase method, since this 'sub only downloads the data and inserts it into the already present 'local table 'the string TableName contains the table to be downloaded Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim fld As ADODB.Field Dim sql As String 'set the connection variables mysql_server = "******" mysql_username = "*****" mysql_password = "**********" mysql_database = "**********" 'and the the connectionstring connectstring = "DRIVER={MySQL ODBC 3.51 Driver};" _ & "SERVER=" & mysql_server & ";" _ & "DATABASE=" & mysql_database & ";" _ & "UID=" & mysql_username & _ ";PWD=" & mysql_password & _ ";OPTION=1312771" 'connect to MySQL server using MySQL ODBC 3.51 Driver Set conn = New ADODB.Connection conn.ConnectionString = connectstring conn.Open Set rs = New ADODB.Recordset rs.CursorLocation = adUseServer 'fetch the table rs.Open "SELECT * FROM " & TableName, conn rs.MoveFirst 'these two variables are used to create a local SQL INSERT command Dim FieldNames As String Dim FieldValues As String Do Until rs.EOF 'this loop is being repeated until the last record in the specified table 'it creates an SQL INSERT command by checking which field names there are 'in the table and then grabbing the field values for each record 'this data is taken together to create a local INSERT-command 'so this kind of string emerges: 'INSERT INTO TableName ([fieldname1],[fieldname2],...) VALUES ('fieldvalue1','fieldvalue2'....); 'start both variables with a ( FieldNames = "(" FieldValues = "(" x=0 For Each fld In rs.Fields 'in this for...next loop all field names are found and the 'corresponding field values are saved FieldNames = FieldNames & "[" & fld.Name & "]" 'prevent errors when there's a ' in the field value apostrof_fix = Replace("" & fld.Value, "'", "''") FieldValues = FieldValues & "'" & apostrof_fix & "'" x=x+1 If x <> rs.Fields.Count Then 'a comma is needed, except for the last field name and field value FieldNames = FieldNames & "," FieldValues = FieldValues & "," End If Next 'close both the field name and field value with a ) FieldNames = FieldNames & ")" FieldValues = FieldValues & ")" With CurrentDb 'and insert the entire string with field names and data .Execute "INSERT INTO " & TableName & " " & FieldNames & " VALUES " & FieldValues & ";" End With 'go to the next record rs.MoveNext Loop End Sub How to repeat: Run the code.