Phil Powell wrote:
The situation is this: a group of co-workers of mine will be using an
Access db in a shared source (for now, a directory.. ???) to be able
to generate reports on the fly. What they want to do is to be able to
migrate that data to a MySQL db instance that currently exists on a
different server.
My method...
1. Install MySQL Connector/ODBC 3.51
(
http://dev.mysql.com/downloads/connector/odbc/3.51.html) and create an
ODBC resource for the MySQL server on each of the client machines.
2. In the Access database, link the necessary tables (don't use the same
names for the tables in Access and on MySQL to make it easier on yourself.
3. Create a form button action that performs all the necessary queries
to import the data.
Below is an example of one that I use. What this does is empties the
table on the MySQL server, then imports all records from the local
sources table into the linked remote visitdl_sources table. When the
client wants to update the remote server, they just click a button. ;)
Private Sub cmdUpdateSources_MouseUp(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Dim rslt As ADODB.Recordset, rslt2 As ADODB.Recordset
Dim sqlstr As String, sqlstr2 As String
Dim sourecID As Long, sourceName As String, SID As String
Set rslt = New ADODB.Recordset
Set rslt.ActiveConnection = CurrentProject.Connection
' select all sources in this (Access) database
sqlstr = "SELECT id, name, webDisplay FROM sources WHERE webDisplay
= Yes ORDER BY id"
rslt.Open (sqlstr)
Set rslt2 = New ADODB.Recordset
Set rslt2.ActiveConnection = CurrentProject.Connection
sqlstr2 = "DELETE FROM visitdl_sources"
rslt2.Open (sqlstr2)
Do Until rslt.EOF
sourceID = rslt.Fields("id").Value
SID = sourceID
sourceName = rslt.Fields("name").Value
sqlstr2 = "INSERT INTO visitdl_sources values (" + SID + ","""
+ sourceName + """);"
rslt2.Open (sqlstr2)
rslt.MoveNext
Loop
rslt.Close
Set rslt = Nothing
Set rslt2 = Nothing
End Sub
--
Justin Koivisto -
sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.