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