473,562 Members | 2,831 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3243
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**********@h ere.org> wrote in message news:<40******* *******@here.or g>...
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 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 - 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******* ***********@new s7.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 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


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
4179
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 direct access to the db server. I'd like to give her the facility to export the information in her local Access application to the shared...
5
14215
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 pg_dump, but this file doesn't have > the insert in Transac-sql. > > Any help is welcome
3
1169
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 accommodating with configuration, we still don't have absolute control. I'm looking at MySQL, MSDE and Visual FoxPro as options for the data store and...
16
9759
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: Run-time error '-2147024894 (80070002)': File or assembly name COMTest3, or one of its dependencies, was not found. The code for the COM:
4
1747
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 their pricing, and it wants to keep that service going on. I contacted NServ system operator and he provided me with four links, three to their xml...
4
4957
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
3006
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 code don't see my paswords. there is a solution? Thank you in advance. Mario.
9
1647
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 the database each day. Every 3 months I have to archive some of the older detail rows to allow continued running of the application against the most...
2
3209
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 give the how's. Thanks. hebollozos
0
7655
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7577
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7869
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7935
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6221
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3608
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2073
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
903
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.