Using PHP to migrate data from shared Access db to MySQL

[Phil Powell]

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.

[bonehead]

Re: Using PHP to migrate data from shared Access db to MySQL

Phil Powell wrote:[color=blue]
> 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.[/color]

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

[Phil Powell]

Re: Using PHP to migrate data from shared Access db to MySQL

bonehead <sendmenospam@h ere.org> wrote in message news:<40E48FEE. 4080900@here.or g>...[color=blue]
> Phil Powell wrote:[color=green]
> > 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.[/color]
>
> 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.
>[/color]

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
[color=blue]
> 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[/color]

[Justin Koivisto]

Re: Using PHP to migrate data from shared Access db to MySQL

Phil Powell wrote:[color=blue]
> 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.[/color]

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 cmdUpdateSource s_MouseUp(Butto n 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.ActiveConn ection = 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.ActiveCon nection = CurrentProject. Connection
sqlstr2 = "DELETE FROM visitdl_sources "
rslt2.Open (sqlstr2)

Do Until rslt.EOF
sourceID = rslt.Fields("id ").Value
SID = sourceID
sourceName = rslt.Fields("na me").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 - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

['bonehead]

Re: Using PHP to migrate data from shared Access db to MySQL

bonehead wrote:
[color=blue]
> 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...[/color]

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

[Phil Powell]

Re: Using PHP to migrate data from shared Access db to MySQL

Justin Koivisto <spam@koivi.com > wrote in message news:<M7fFc.217 9$m3.119138@new s7.onvoy.net>.. .[color=blue]
> Phil Powell wrote:[color=green]
> > 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.[/color]
>
> My method...[/color]

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
[color=blue]
>
> 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 cmdUpdateSource s_MouseUp(Butto n 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.ActiveConn ection = 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.ActiveCon nection = CurrentProject. Connection
> sqlstr2 = "DELETE FROM visitdl_sources "
> rslt2.Open (sqlstr2)
>
> Do Until rslt.EOF
> sourceID = rslt.Fields("id ").Value
> SID = sourceID
> sourceName = rslt.Fields("na me").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[/color]

BTW why do you not recommend alt.php?