By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,349 Members | 1,353 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,349 IT Pros & Developers. It's quick & easy.

Using PHP to migrate data from shared Access db to MySQL

P: n/a
I've read some online resources that utilize various MySQL
command-line actions to migrate data from Access to MySQL.

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.

What would be the best way to do this within a PHP wrapper (if even
PHP at all, which I prefer); if so, what are the ways I can ensure
security as this is US Federal Government data and thus must be locked
down tight?

Thanx

Phil

PS: They're insisting on Access on their end as it is their apparently
easiest (and quickest w/o my having to build an entire CMA for them in
3 months) means of maintaining the data locally before migration.
Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Phil Powell wrote:
I've read some online resources that utilize various MySQL
command-line actions to migrate data from Access to MySQL.

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.

What would be the best way to do this within a PHP wrapper (if even
PHP at all, which I prefer); if so, what are the ways I can ensure
security as this is US Federal Government data and thus must be locked
down tight?

Thanx

Phil

PS: They're insisting on Access on their end as it is their apparently
easiest (and quickest w/o my having to build an entire CMA for them in
3 months) means of maintaining the data locally before migration.


Hmmm...I wonder how you plan on encrypting and securing the odbc link
from the Access application to the database server...I've actually been
kind of curious about this myself. Since mysql typically runs on port
3306, is it possible to configure the server to also run ssh on 3306 and
then tunnel the odbc connection through an ssh shell? Because if you
don't, isn't the data traveling back and forth across an unsecured
connection?

If anyone's actually set this up I'd really be interested in hearing a
step-by-step description...

Meanwhile, migrating the data from Access to MySQL might be a lot easier
if you just copy and paste each table's contents as text into a separate
excel file and then save each one as a .csv. You'd want to check each
file in something like EditPlus to make sure everything actually is
comma delimited and newlines are terminated with '\n' (you don't really
absolutely have to, but it helps), then upload the .csvs to the mysql
server and populate the tables using the LOAD DATA INFILE syntax. In
fact you could write all your LOAD DATA INFILE statements in one .sql
file and then just execute the .sql using the 'source' command. Of
course that doesn't work unless you are using a mysql user account which
has the FILE privilege turned on.

I imagine there are probably more elegant ways to accomplish this. But
just in case, you might want to take a look here:

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

Jul 17 '05 #2

P: n/a
bonehead <se**********@here.org> wrote in message news:<40**************@here.org>...
Phil Powell wrote:
I've read some online resources that utilize various MySQL
command-line actions to migrate data from Access to MySQL.

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.

What would be the best way to do this within a PHP wrapper (if even
PHP at all, which I prefer); if so, what are the ways I can ensure
security as this is US Federal Government data and thus must be locked
down tight?

Thanx

Phil

PS: They're insisting on Access on their end as it is their apparently
easiest (and quickest w/o my having to build an entire CMA for them in
3 months) means of maintaining the data locally before migration.
Hmmm...I wonder how you plan on encrypting and securing the odbc link
from the Access application to the database server...I've actually been
kind of curious about this myself. Since mysql typically runs on port
3306, is it possible to configure the server to also run ssh on 3306 and
then tunnel the odbc connection through an ssh shell? Because if you
don't, isn't the data traveling back and forth across an unsecured
connection?

If anyone's actually set this up I'd really be interested in hearing a
step-by-step description...

Meanwhile, migrating the data from Access to MySQL might be a lot easier
if you just copy and paste each table's contents as text into a separate
excel file and then save each one as a .csv. You'd want to check each
file in something like EditPlus to make sure everything actually is
comma delimited and newlines are terminated with '\n' (you don't really
absolutely have to, but it helps), then upload the .csvs to the mysql
server and populate the tables using the LOAD DATA INFILE syntax. In
fact you could write all your LOAD DATA INFILE statements in one .sql
file and then just execute the .sql using the 'source' command. Of
course that doesn't work unless you are using a mysql user account which
has the FILE privilege turned on.


Actually I thought of just doing that at first but as well am looking
for a more elegant solution, preferably an open-source solution (we're
not IBM y'know!)

Phil
I imagine there are probably more elegant ways to accomplish this. But
just in case, you might want to take a look here:

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

Jul 17 '05 #3

P: n/a
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.
Jul 17 '05 #4

P: n/a
bonehead wrote:
Since mysql typically runs on port
3306, is it possible to configure the server to also run ssh on 3306 and
then tunnel the odbc connection through an ssh shell? Because if you
don't, isn't the data traveling back and forth across an unsecured
connection?

If anyone's actually set this up I'd really be interested in hearing a
step-by-step description...


Okay I did a google search on this topic and found what appears to be a
pretty good article. I might try it this weekend just to see how it
works. If anybody else has tried this I'd be interested in hearing comments:

http://www.vbmysql.com/articles/sshtunnel.html

Jul 17 '05 #5

P: n/a
Justin Koivisto <sp**@koivi.com> wrote in message news:<M7******************@news7.onvoy.net>...
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...


Interesting method and thanx. However, my VB knowledge is between
scant and nonexistent, therefore, I could not impose such a solution
as you suggest w/o extreme difficulty. Would there be a PHP-based
solution similar to this that you would recommend?

Thanx
Phil

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


BTW why do you not recommend alt.php?
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.