Insert Into Queries

[George Starks]

I'm using Access 2007 and I'm trying to take data from a single table and
insert that data as multiple new records into three tables with established
relationships.

I tried to use the existing, append query, but it does not appear to allow
for this (allows one to append records to only one table at a time). The
latter does not work due to the tables’ relationships. (i.e. you can’t enter
data in one table without an associated record in the other three.)

Has anyone seen and resolved this issue before, I’d love a little help. I’ve
been scouring the Internet with no luck.

Thanks
George

 

[KARL DEWEY]

You did not say what the relationship was. I assume that there is a
one-to-many between the ONE table and the other three.
If this is so then the ONE table must have data in the primary key field
before you can enter related data in the many tables.
The best way is to use a form/subforms with Master/Child links set using the
primary key of the ONE and foreign key of the many.

 

[MGFoster]

I'm using Access 2007 and I'm trying to take data from a single table and
insert that data as multiple new records into three tables with established
relationships.

I tried to use the existing, append query, but it does not appear to allow
for this (allows one to append records to only one table at a time). The
latter does not work due to the tables’ relationships. (i.e. you can’t enter
data in one table without an associated record in the other three.)

Has anyone seen and resolved this issue before, I’d love a little help. I’ve
been scouring the Internet with no luck.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe what you've got set up is wrong. Having said that:

INSERT INTO only works on one table at a time. Try turning off the
Relationship before running the INSERT and turning it back on after the
INSERT.

Relationships are FOREIGN KEY CONSTRAINTS and can be set and dropped
using SQL. Ex:

Set the relationship:

ALTER TABLE foo ADD CONSTRAINT FK_bar FOREIGN KEY (bar) REFERENCES
table_name (bar) ON UPDATE CASCADE

Drop the relationship:

ALTER TABLE foo DROP CONSTRAINT FK_bar

"bar" is the column name that provides the relationship between the
tables "foo" and "table_name."

See the indicated Access Help topics (from the Help table of contents):


Microsoft Jet SQL Reference
Microsoft Jet SQL Reference
Data Definition Language
ALTER TABLE statement <-
CONSTRAINT clause <-


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkpQhIechKqOuFEgEQLiwwCeLiPgd9o+vc8wF4H0kDxmNUtVp9sAoJL5
PIBDuvVtZdVYDN1Yvk42l8Dw
=+vb7
-----END PGP SIGNATURE-----

 

[George Starks]

Hi Karl,

Yes, they are one-to-many relationship.

Question, I have numerous records that are required to update my database;
does your recommendation facilitate the appending of multiple records to the
database’s tables (containing parent child links) via a Visual Basic script
or Access Query (or some other means), or does it lend more to a manual
effort? That is to say the forms/subforms would be linked to a query
containing all of the required fields, after which the user would have to
enter in the required data manually one record at a time.

Thanks
George

 

[KARL DEWEY]

If you have an on-going requirement to append records then create a separate
query for each table and then a macro with action OpenQuery for the queries.

That way they all will run at the same time with one push of a button.