Pulling info from remote mysql db using VBA in access 2003

[gpl9310]

Hello,

I'm fairly new to VBA and Access in general, but I have some questions regarding using VBA to pull info from a remote MySQL db into an Access db. (Almost all of my experience is in Web and Database Development) Basically I'm wondering if there are any resources that describe, in detail, how to pull info from a mySQL db on a web server from an Access db that resides elsewhere, or if anyone would be willing to share their experiences with me in doing this or something similar.

-Gage

 

[mvester]

' try something like this

Dim db_source, db_dest As ADODB.Connection
Dim rs_source, rs_dest As ADODB.Recordset
Dim strCnxn, strSQL, qrybuff As String
Set db_source = New ADODB.Connection
Set rs_source = New ADODB.Recordset
Set db_dest = New ADODB.Connection
Set rs_dest = New ADODB.Recordset
strCnxn = "connection string to your database"
strSQL = "query to get the desired colums from you database"
qrybuff = "whatever Access query to populate your Access table"
db_source.Open strCnxn
rs_source.Open strSQL, db, adOpenStatic, adLockReadOnly, adCmdText
Set db_dest = CurrentDb
Set rs_dest = db_dest.OpenRecordSet(qrybuff, dbOpenDynaset)
rs_source.MoveFirst
Do While Not rs_source.EOF
rs_dest.AddNew
rs_dest!column1 = rs_source!column1
rs_dest!column2 = rs_source!column2
.
.
rs_dest.Update
rs_source.MoveNext
Loop

rs_dest.Close
rs_source.Close
db_source.Close
db_source.Close
Set db_dest = Nothing
Set rs_dest = Nothing
Set db_source = Nothing
Set rs_source = Nothing

 

[Tdgu]

You could also go for a software solution to pull the data from MySQL. There are plenty of ETL tools out there to satisfy your needs. I would suggest going for open source tools, that are cheaper - free - than other very comparable tools. Instead of datastge, look at programs such as Talend. The tool is free to download.