473,288 Members | 2,725 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Using PHP to migrate data from shared Access db to MySQL

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
5 3221
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Mike MacSween | last post by:
My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a simple PHP/MySql application on shared hosting, so no...
5
by: Fabio Benavides Murillo | last post by:
> Hello > > I have a problem, I want to migrate the data of a postgres' database to a > mysql's database, this because i need to move to a windows. > > I tried making a postgres' backup using...
3
by: John Spiegel | last post by:
Hi all, I'm looking at adding some ASP.NET apps to an existing Win2K-hosted website. The site is hosted by another company on a server shared by others. While the hosting company is fairly...
16
by: Asaf | last post by:
I am trying to create and use a COM object with C#.NET 2005. The assembly is set to "Register for COM interop" but when I am trying to call it from VB on Word 2003 I am getting this error: ...
4
by: Pasi | last post by:
Hi all! I have a problem with xml-data. My small enterprice is asked to renew one car reseller company's (Comp.) websites. This company uses national dataservice (NServ.) about used cars and...
4
by: Roy | last post by:
Hi, Can anyone tell me how to migrate data from MySQL to DB2? Does DB2 offer such migration tools? Thank you. Roy
20
by: _mario.lat | last post by:
hallo, I use PHP and I'd like to not write in hardcoded way password and login to access to mysql. how to not write password in code for access to mysql? How can I do? I'd like that who see my...
9
by: Algonquin J. Calhoun | last post by:
I've developed an application that exceeds the Access 2GB limit. This application was developed as a prototype and the users have found it very useful. Approximately 45,000 records are added to...
2
by: hebollozos | last post by:
Can anyone help me by giving me instructions how to migrate my MySQL Data for Windows to MySQL for Linux. I tried to find Sqlporter software by Realsoft Studio, but I failed. Hope somebody out can...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.