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

Multiple sql statements in insert query

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 11 Jul 2005 13:24:31 -0700, kr***********@gmail.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.

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.


Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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 ...

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.