insert to remote mysql tbl from local table using ADODB 2

[hovercraft]

I am having problems appending records from a local table (currentdb) to a mySQL table on a 3rd party web host. I have to connect using ADO and the myODBC driver 5.1 . Simply linking the table doesn't work as the web host has a connection time out set at 60 seconds and they won't change it.
I can connect and delete all the records from the remote mySQL table like this

myodbcconn = "Driver={MySQL ODBC 5.1 Driver};Server=xx.xx.xxx.xx;Port=3306;Database=mydqldbname;User=myuser; Password=mypassword;Option=3;"

Dim adoConn As ADODB.Connection

Dim adoRS As ADODB.Recordset

Set adoConn = New ADODB.Connection

Set adoRS = New ADODB.Recordset

Dim strSql As String

strSql = "DELETE mysqltable.* FROM mysqltable;"

adoConn.Open myodbcconn

adoRS.Open strSql, adoConn

Set adoRS = Nothing

Set adoConn = Nothing
    
Set adoConn = Nothing

if I replace strSql with ....

strSql = "insert INTO mysqltable select * FROM myaccesstable;"

then I get a run time error # -2147217865 table mysqldatabase.myaccesstable doesn't exisit.
beacuase it's a local access table not part of the adoConn
Any idea's how I can use a local table in the strSql variable?

Thanks for this great community!

 

[dhookom]

You could create a recordset of the local table and move through it one record at a time to run insert SQL statements witth the values from the local table. This might be a bit kludgy but should work.

Duane
Hook'D on Access
MS Access MVP

 

[hovercraft]

Thanks dhookom!
Would you suggest creating the mysql connection in a loop for each reccord in the access table.

I was trying to avoid this due to the kludgy factor. I will be updating various tables, some small but some rather large.

 

[dhookom]

You should only need one connection to mysql. Loop through the records in your local table and create the strSQL for each local record.

Duane
Hook'D on Access
MS Access MVP

 

[hovercraft]

Here's what I ended up using to append a local access table into a remote mysql table.

Dim localtablefield1 As String
Dim localtablefield2 As String
Dim localtablefield3 As String

Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rs As dao.Recordset

  Set db = CurrentDb
  Set rs = db.OpenRecordset("myaccesstable")
    rs.MoveFirst
    Do Until rs.EOF
    localtablefield2 = rs!field1
    localtablefield2 = rs!field2
    localtablefield2 = rs!field3
    

myodbcconn = "Driver={MySQL ODBC 5.1 Driver};Server=xx.xx.xxx.xx;Port=3306;Database=mysqldatabase;User=myuser; Password=mypassword;Option=3;"

Dim adoConn As ADODB.Connection

Dim adoRS As ADODB.Recordset

Set adoConn = New ADODB.Connection

Set adoRS = New ADODB.Recordset

Dim strSql As String

strSql = "INSERT INTO mysqltable(mysqlfied1, mysqlfied2, mysqlfied3) VALUES ('" & localtablefield1 & "', '" & localtablefield2 & "', '" & localtablefield3 & "');"

adoConn.Open myodbcconn

adoRS.Open strSql, adoConn

Set adoRS = Nothing

Set adoConn = Nothing
    
Set adoConn = Nothing


    rs.MoveNext
    Loop

MsgBox ("INSERT COMPLETE")

It takes access a little while to complete this ~500 records is about 4 minutesish.
Can you make any recommendations for de-kludgification?

Thanks,
Hovercraft

 

[PHV]

It takes access a little while to complete
What about this ?

myodbcconn = "Driver={MySQL ODBC 5.1 Driver};Server=xx.xx.xxx.xx;Port=3306;Database=mysqldatabase;User=myuser; Password=mypassword;Option=3;"
Set adoConn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
adoConn.Open myodbcconn
Set db = CurrentDb
Set rs = db.OpenRecordset("myaccesstable")
Do Until rs.EOF
  strSql = "INSERT INTO mysqltable(mysqlfied1,mysqlfied2,mysqlfied3) VALUES ('" & rs!field1 & "','" & rs!field2 & "','" & rs!field3 & "');"
  adoRS.Open strSql, adoConn
  rs.MoveNext
Loop
Set adoRS = Nothing
Set adoConn = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886