Hi all,

Somehow I want to transfer data from an mysql database on the web to an
local offline access database. Note: tables and types do not match.
So, I toughed, generating sql commands to insert the data would do the
trick. But, unfortunately it works only properly for 1 record.

This is what I do (suggestions are more than welcome)
Create a new query, skip all the wizard-stuff and choose sql-view. Then
I past my dozen sql commands in their. Like:
INSERT INTO RunnerInfo ( RunnerID, FirstName, LastName, BirthDate,
Gender, ClubName, RaceID ) VALUES (10, 'me', "too", ' 01/01/1900', "M",
"city", 2);
INSERT INTO RunnerInfo ( RunnerID, FirstName, LastName, BirthDate,
Gender, ClubName, RaceID ) VALUES (11, 'me', "not", ' 31/12/1980', "M",
"city", 2);
....

When I try to save this query, I get: "Characters found after end of
SQL statement." This just means that only 1 command is allowed (leaving
only 1 command in their shows this).

How can I solve this problem and execute multiple sql commands in
batch? I have seen the import sql option but I does not read sql
commands, It wants to connect to a database, which is, in my case, not
possible.

On 11 Jul 2005 13:24:31 -0700, kristof.loots (AT) gmail (DOT) com wrote:

Find a decent ODBC driver for MySql.
Using it, create an ODBC data source, and using it, attach the MySql
tables to your Access app (keywords: Connect, RefreshLink,
TransferDatabase).
Then write insert statements like this:
insert into AccessTable(<fieldnames>)
select <fieldnames> from MySqlTable

This will bulk-insert the data.

-Tom.

I don't quite understand. Does it mean that the ODBC driver needs to be
installed on the server with the mysql server? or with access?
Anyway, I can't install this on the server with mysql.

All thankx for your help.
I have created a ; - separated file with php and then I import this in
access and it works fine.
Easy as it can be ...