473,287 Members | 1,395 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,287 software developers and data experts.

Local Access database to shared PHP/MySql hosting export

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
11 4153
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
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
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
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
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


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

Similar topics

12
by: filesiteguy | last post by:
Some partners of mine and I are writing a host-based application, which would greatly benefit if we could take advantage of a web services-like function we are formulating. Unfortunately, this...
1
by: Ray in HK | last post by:
What are the differences between LOAD DATA INFILE and LOAD DATA LOCAL INFILE ? I found some web hosting company do not allow using LOAD DATA INFILE but allow LOAD DATA LOCAL INFILE. The reason...
4
by: Nathaniel Sherman | last post by:
Alright, folks, here's the deal... I'm working on migrating a classic ASP website to an ASP.NET codebase. At the heart of the site is a MySQL database. To make sure an "in-process" program...
17
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting...
7
by: Lero | last post by:
I know SQL server is the way to go but I feel rip by my ISP when they give me 3Gb of storage without MS SQL Server support, unless I pay $15 dollars a month more, on the other hand I can use Access...
4
by: Richard | last post by:
hi there, i've got a mysql database with a research data. i would like to get some information from that database and save it as ms access database. it will be a client feature. the client...
8
by: mouac01 | last post by:
I'm not sure if this is possible. I would like to have a PHP app on the Internet connect and write to a local database (Intranet). For example, users would go to a web site...
6
by: mirianCalin | last post by:
i am doing the programming at home and our teacher checks it in school that's why i need to export/import my database.. but i dont know how.. i've found this instruction on the net but i cant...
7
by: Fred | last post by:
I have a file (access.php) with the db username and pwd, which I include in every php file that needs db access. I'm not clear on how to set the path. I have an account on a shared *nix server,...
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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.