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

Local Access database to shared PHP/MySql hosting export

P: n/a
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 PHP/MySql site. From one command button (or
similar) in the Access application.

It would be probably be a complete overwrite. That is to say all the
information on the shared site would be overwritten with that from the local
machine.

I'm assuming that I'd have to make an HTTP request to some PHP page which
would then run the SQL to delete all the records, then append all the new
ones.

Is this the right approach? I don't want to spend weeks finding out that
this is fundamentally flawed in some way. The client has an ADSL connection.

Yours, Mike MacSween
Jul 17 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
In article <42***********************@news.aaisp.net.uk>,
"Mike MacSween" <mi***************************@btinternet.com> wrote:
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 PHP/MySql site. From one command button (or
similar) in the Access application.

It would be probably be a complete overwrite. That is to say all the
information on the shared site would be overwritten with that from the local
machine.

I'm assuming that I'd have to make an HTTP request to some PHP page which
would then run the SQL to delete all the records, then append all the new
ones.

Is this the right approach? I don't want to spend weeks finding out that
this is fundamentally flawed in some way. The client has an ADSL connection.

Yours, Mike MacSween


You'd have to write a php script that generates a access import file.
I'm not that familiar with Access, so I can't comment further on "how",
but I'm sure there are Access books out there that will tell you how to
do "file import". All you have to do is create the output from your
MySQL database in perl or php accordingly and output it as an Access
export document.

One why to try this is to take the client's database and export it.
Create your output in a similar format.

--
DeeDee, don't press that button! DeeDee! NO! Dee...

Jul 17 '05 #2

P: n/a
Thanks. I think you may have misunderstood. I want to get data FROM the
Access database and INTO the MySQL database.

"Michael Vilain" <vi****@spamcop.net> wrote in message
news:vi**************************@comcast.dca.giga news.com...
In article <42***********************@news.aaisp.net.uk>,
"Mike MacSween" <mi***************************@btinternet.com> wrote:
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 PHP/MySql site. From one command button
(or
similar) in the Access application.

It would be probably be a complete overwrite. That is to say all the
information on the shared site would be overwritten with that from the
local
machine.

I'm assuming that I'd have to make an HTTP request to some PHP page which
would then run the SQL to delete all the records, then append all the new
ones.

Is this the right approach? I don't want to spend weeks finding out that
this is fundamentally flawed in some way. The client has an ADSL
connection.

Yours, Mike MacSween


You'd have to write a php script that generates a access import file.
I'm not that familiar with Access, so I can't comment further on "how",
but I'm sure there are Access books out there that will tell you how to
do "file import". All you have to do is create the output from your
MySQL database in perl or php accordingly and output it as an Access
export document.

One why to try this is to take the client's database and export it.
Create your output in a similar format.

--
DeeDee, don't press that button! DeeDee! NO! Dee...

Jul 17 '05 #3

P: n/a
I noticed that Message-ID: <42***********************@news.aaisp.net.uk>
from Mike MacSween contained the following:
Thanks. I think you may have misunderstood. I want to get data FROM the
Access database and INTO the MySQL database.


The way I have done this in the past is to export the access database
then update the MySql database like so:

# Connect to the database
mysql_connect($sql_db,$sql_id,$sql_pwd);

# Delete the current content of the table
$result = mysql_db_query($sql_db,"DELETE FROM $table") or die ("Invalid
DELETE query");

# Optimize the current table (recover empty space)
$result = mysql_db_query($sql_db,"OPTIMIZE TABLE $table") or die
("Invalid OPTIMIZE query");

# Load local comma separated, fields enclosed by quotes text database -
File has to be in the same directory of this file
$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE
'exporteddata.txt' INTO TABLE $table FIELDS TERMINATED BY ';' ENCLOSED
BY ''") or die ("Invalid DATA LOAD query");

# Get how many records are present in the table now
$result = mysql_db_query($sql_db,"SELECT * from $table") or die
("Invalid SELECT query");
$rows_count = mysql_num_rows($result);

echo "Records: $rows_count"; mysql_free_result($result);
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #4

P: n/a
Thanks Geoff. How would you get the txt file up to the server? FTP?

I would like to avoid having FTP passwords embedded in the application if
poss.

Mike

"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:jt********************************@4ax.com...
I noticed that Message-ID: <42***********************@news.aaisp.net.uk>
from Mike MacSween contained the following:
Thanks. I think you may have misunderstood. I want to get data FROM the
Access database and INTO the MySQL database.


The way I have done this in the past is to export the access database
then update the MySql database like so:

# Connect to the database
mysql_connect($sql_db,$sql_id,$sql_pwd);

# Delete the current content of the table
$result = mysql_db_query($sql_db,"DELETE FROM $table") or die ("Invalid
DELETE query");

# Optimize the current table (recover empty space)
$result = mysql_db_query($sql_db,"OPTIMIZE TABLE $table") or die
("Invalid OPTIMIZE query");

# Load local comma separated, fields enclosed by quotes text database -
File has to be in the same directory of this file
$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE
'exporteddata.txt' INTO TABLE $table FIELDS TERMINATED BY ';' ENCLOSED
BY ''") or die ("Invalid DATA LOAD query");

# Get how many records are present in the table now
$result = mysql_db_query($sql_db,"SELECT * from $table") or die
("Invalid SELECT query");
$rows_count = mysql_num_rows($result);

echo "Records: $rows_count"; mysql_free_result($result);
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Jul 17 '05 #5

P: n/a
I noticed that Message-ID: <42***********************@news.aaisp.net.uk>
from Mike MacSween contained the following:
Thanks Geoff. How would you get the txt file up to the server? FTP?
You'd have to with that method.
I would like to avoid having FTP passwords embedded in the application if
poss.


I'd be interested in an alternative solution myself.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #6

P: n/a


Geoff Berrow wrote:
I noticed that Message-ID: <42***********************@news.aaisp.net.uk>
from Mike MacSween contained the following:
Thanks Geoff. How would you get the txt file up to the server? FTP?


You'd have to with that method.

I would like to avoid having FTP passwords embedded in the application if
poss.


I'd be interested in an alternative solution myself.


why not just upload with a form?
http://us2.php.net/manual/en/features.file-upload.php

--
juglesh

Jul 17 '05 #7

P: n/a
Mike MacSween (mi***************************@btinternet.com) wrote:
: 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 PHP/MySql site. From one command button (or
: similar) in the Access application.

: It would be probably be a complete overwrite. That is to say all the
: information on the shared site would be overwritten with that from the local
: machine.

: I'm assuming that I'd have to make an HTTP request to some PHP page which
: would then run the SQL to delete all the records, then append all the new
: ones.

: Is this the right approach? I don't want to spend weeks finding out that
: this is fundamentally flawed in some way. The client has an ADSL connection.
A completely different approach comes to mind. - ODBC

Long ago I was shown how Excel (on windows) could query data from a mysql
database _on Linux_ using ODBC. I wonder if that is still supported? I
beleived it used a mysql odbc driver that runs on windows and knows how to
talk to the mysql server.

Perhaps you could do this for updates as well. Install the mysql odbc
driver on windows, set up an odbc connection with the necessary details to
access the linux mysql server, and then use that connection to allow MS
Access to update the mysql database. I think all the setups are done on
windows except for the network setups to allow the mysql server to accept
the remote incoming connections. The server itself simply sees it like any
other mysql connection (i.e. there's no ODBC stuff going on at the server
end).

The Access program would then have complete flexibility to do anything it
wanted with the data, including simply replacing the data in each table.
Within access you just define the remote tables using the relevent options
on the menubar.

delete from odbc_connection_table_x;
insert into odbc_connection_table_x select * from local_tbl_x;

$0.10

--

This space not for rent.
Jul 17 '05 #8

P: n/a
Dan
I have done this type of thing in the past and the easiest way is through an
ODBC connection. On the machine that has the Access DB, you would setup an
ODBC to the MySQL Server. Then simply right click on the table or query in
Access and choose Export. When asked file type, you can select ODBC. You
can write all of the export commands into an Access macro and assign it to a
button to make it easy.
"Malcolm Dew-Jones" <yf***@vtn1.victoria.tc.ca> wrote in message
news:42******@news.victoria.tc.ca...
Mike MacSween (mi***************************@btinternet.com) wrote:
: 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 PHP/MySql site. From one command button
(or
: similar) in the Access application.

: It would be probably be a complete overwrite. That is to say all the
: information on the shared site would be overwritten with that from the
local
: machine.

: I'm assuming that I'd have to make an HTTP request to some PHP page
which
: would then run the SQL to delete all the records, then append all the
new
: ones.

: Is this the right approach? I don't want to spend weeks finding out that
: this is fundamentally flawed in some way. The client has an ADSL
connection.
A completely different approach comes to mind. - ODBC

Long ago I was shown how Excel (on windows) could query data from a mysql
database _on Linux_ using ODBC. I wonder if that is still supported? I
beleived it used a mysql odbc driver that runs on windows and knows how to
talk to the mysql server.

Perhaps you could do this for updates as well. Install the mysql odbc
driver on windows, set up an odbc connection with the necessary details to
access the linux mysql server, and then use that connection to allow MS
Access to update the mysql database. I think all the setups are done on
windows except for the network setups to allow the mysql server to accept
the remote incoming connections. The server itself simply sees it like any
other mysql connection (i.e. there's no ODBC stuff going on at the server
end).

The Access program would then have complete flexibility to do anything it
wanted with the data, including simply replacing the data in each table.
Within access you just define the remote tables using the relevent options
on the menubar.

delete from odbc_connection_table_x;
insert into odbc_connection_table_x select * from local_tbl_x;

$0.10

--

This space not for rent.

Jul 17 '05 #9

P: n/a
On shared hosting?

Aren't the sysadmins going to prevent direct access to the database server?

"Malcolm Dew-Jones" <yf***@vtn1.victoria.tc.ca> wrote in message
news:42******@news.victoria.tc.ca...
Mike MacSween (mi***************************@btinternet.com) wrote:
: 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 PHP/MySql site. From one command button
(or
: similar) in the Access application.

: It would be probably be a complete overwrite. That is to say all the
: information on the shared site would be overwritten with that from the
local
: machine.

: I'm assuming that I'd have to make an HTTP request to some PHP page
which
: would then run the SQL to delete all the records, then append all the
new
: ones.

: Is this the right approach? I don't want to spend weeks finding out that
: this is fundamentally flawed in some way. The client has an ADSL
connection.
A completely different approach comes to mind. - ODBC

Long ago I was shown how Excel (on windows) could query data from a mysql
database _on Linux_ using ODBC. I wonder if that is still supported? I
beleived it used a mysql odbc driver that runs on windows and knows how to
talk to the mysql server.

Perhaps you could do this for updates as well. Install the mysql odbc
driver on windows, set up an odbc connection with the necessary details to
access the linux mysql server, and then use that connection to allow MS
Access to update the mysql database. I think all the setups are done on
windows except for the network setups to allow the mysql server to accept
the remote incoming connections. The server itself simply sees it like any
other mysql connection (i.e. there's no ODBC stuff going on at the server
end).

The Access program would then have complete flexibility to do anything it
wanted with the data, including simply replacing the data in each table.
Within access you just define the remote tables using the relevent options
on the menubar.

delete from odbc_connection_table_x;
insert into odbc_connection_table_x select * from local_tbl_x;

$0.10

--

This space not for rent.

Jul 17 '05 #10

P: n/a
Dan
I've never had a problem doing this on a shared hosting however, if this is
a problem, you can export from access into a CSV format and then do a form
upload. From there, you can use the fgetcsv to read through the file and
append to the table.
"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .
On shared hosting?

Aren't the sysadmins going to prevent direct access to the database
server?

"Malcolm Dew-Jones" <yf***@vtn1.victoria.tc.ca> wrote in message
news:42******@news.victoria.tc.ca...
Mike MacSween (mi***************************@btinternet.com) wrote:
: 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 PHP/MySql site. From one command
button (or
: similar) in the Access application.

: It would be probably be a complete overwrite. That is to say all the
: information on the shared site would be overwritten with that from the
local
: machine.

: I'm assuming that I'd have to make an HTTP request to some PHP page
which
: would then run the SQL to delete all the records, then append all the
new
: ones.

: Is this the right approach? I don't want to spend weeks finding out
that
: this is fundamentally flawed in some way. The client has an ADSL
connection.
A completely different approach comes to mind. - ODBC

Long ago I was shown how Excel (on windows) could query data from a mysql
database _on Linux_ using ODBC. I wonder if that is still supported? I
beleived it used a mysql odbc driver that runs on windows and knows how
to
talk to the mysql server.

Perhaps you could do this for updates as well. Install the mysql odbc
driver on windows, set up an odbc connection with the necessary details
to
access the linux mysql server, and then use that connection to allow MS
Access to update the mysql database. I think all the setups are done on
windows except for the network setups to allow the mysql server to accept
the remote incoming connections. The server itself simply sees it like
any
other mysql connection (i.e. there's no ODBC stuff going on at the server
end).

The Access program would then have complete flexibility to do anything it
wanted with the data, including simply replacing the data in each table.
Within access you just define the remote tables using the relevent
options
on the menubar.

delete from odbc_connection_table_x;
insert into odbc_connection_table_x select * from local_tbl_x;

$0.10

--

This space not for rent.


Jul 17 '05 #11

P: n/a
On Sat, 18 Jun 2005 14:16:57 +0100, Geoff Berrow wrote:
I noticed that Message-ID: <42***********************@news.aaisp.net.uk>
from Mike MacSween contained the following:
Thanks Geoff. How would you get the txt file up to the server? FTP?


You'd have to with that method.

I would like to avoid having FTP passwords embedded in the application if
poss.


I'd be interested in an alternative solution myself.


Couldn't the user upload/overwrite the the MS Access file and then use
a PHP script that connects to it using ADODB? No ODBC connection
required. You could even design a file/upload template that the user
could use to send the file up via a browser/HTTP interface (rather
than FTP).

Do a search on ADODB connects.

Adam.
Jul 17 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.