generate SQL insert statements from MS access

[hardog]

Hi,
i need to append data that is currently in a single MS Access table into multiple MYSQL tables that have primary keys and auto_ids etc. Therefore I need to run the queries in order so that I can use the new auto_ids correctly, see below:

INSERT INTO users (username, unconfirmed_email, password, agrees_with_terms, active, created, modified)
VALUES ("46", "", unix_timestamp(now()), 0, 0, now(), now());
SET @curr_user_id = LAST_INSERT_ID();
INSERT INTO accounts (type_id, status_id, profile_name, email, match_permissions, odesk, created, modified) VALUES (3, 0, "46", "", 1, 1, now(), now());
SET @curr_account_id = LAST_INSERT_ID();
INSERT INTO accounts_users (account_id, user_id, role_id, created, modified) VALUES (@curr_account_id, @curr_user_id, 1, now(), now());

is there a way i can change a single Access append query to generate the required insert statements to do this?

thanks
hardog

 

[mdlueck]

is there a way i can change a single Access append query to generate the required insert statements to do this?

VBA ADO.RecordSet object reads the Access table, in a loop until the RecordSet has been completed issue your INSERT statements, capturing the AutoID col values along the way.

So, a "VBA code required" solution would be what I would suggest.

 

[G37Sam]

Yes, you can use VBA code in a module like this

Sub SQLadd()
Dim strSQL as string
strSQL = "INSERT INTO ..." 'insert your SQL statement here as you have done above
CurrentDB.execute strSQL
End Sub

Note that numbers don't require quotes, strings require single quotes and dates require hash (#) signs

 

[Pat Hartman]

Here's an example posted by RuralGuy
http://www.access-programmers.co.uk/forums/showthread.php?t=175790

Search for @@Identity or @@Scope_Identity
Here's an explanation:
http://blog.sqlauthority.com/2007/0...nt-retrieve-last-inserted-identity-of-record/

 

[hardog]

thanks guys,
i dont think i explained my problem very well - i was bascially just trying to create a mysql script to load data from an Access table into mysql tables.
I simply put the parts of the insert statements into fields in the table and concatenated them with the fields in question, something like this:


INSERT INTO users (username, unconfirmed_email, password, agrees_with_terms, active, created, modified) VALUES ("sdsdsdsdsd", "", unix_timestamp(now()), 0, 0, now(), now()); SET @curr_user_id = LAST_INSERT_ID(); INSERT INTO accounts (type_id, status_id, profile_name, email, match_permissions, odesk, created, modified, public, public_modified) VALUES (3, 1,"Dominique", "", 1, 1, now(), now(), 0, now()); SET @curr_account_id = LAST_INSERT_ID(); INSERT INTO accounts_users (account_id, user_id, role_id, created, modified) VALUES (@curr_account_id, @curr_user_id, 1, now(), now()); INSERT INTO boutiques (id, account_id, website, buyer_name, buyer_title, description, nbr_of_designers, created, modified) VALUES (@curr_account_id, @curr_account_id, "", "454634636", "", "", 0, now(), now()); INSERT INTO boutique_locations (boutique_id, primary_location, type, store_name, phone, address_1, address_2, city, state, zip, country, hours, created, modified) VALUES (@curr_account_id, 1, 1, "233454545", "", "47 Miracle Mile", "", "Coral Gables", "FL", "", "United States", "", now(), now());

so field1 in my access table had the value:

INSERT INTO users (username, unconfirmed_email, password, agrees_with_terms, active, created, modified) VALUES (" etc etc

not the most efficent way of doing it im sure, but it got the job done.