Posted by Dan on 06/19/05 01:02

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" <yf110@vtn1.victoria.tc.ca> wrote in message
news:42b474e0@news.victoria.tc.ca...
> Mike MacSween (mike.macsween.getlostspammers@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.

[Back to original message]