Run SQL For "many" table with relationship Established

[Larry R Harrison Jr]

I have an Access 97 database with 2 tables that have a one-many
relationship. I have a SQL statement in the "one" table which I want to
execute and insert 7 records into the "many" table, and I want them to be
linked to the "main" table.

Problem is, it won't let me run the SQL run as long as the one-many
relationship is established in the relationship window.

The SQL is:

Dim sSQL As String
For stDate = stBegDate To stEndDate
sSQL = "INSERT INTO tblScheduleActual_Test ( date1 ) SELECT #" & stDate & "#
AS Expr1;"
DoCmd.RunSQL sSQL
Next stDate

It loops through the dates of the week and inserts 7 records with successive
dates.

It works fine if I remove the relationship, but I figured the relationship
should be established in the relationship window.

Tips?

LRH

[TC]

Not to answer your question, but, the following code is very unsafe:

"SELECT #" & stDate & "# ..."

If stDate is a string, it will be formatted accrding to your current PC
settings. But dates provided in #...# format must be provided in U.S.
(month/day/year) order, >regardless of your PC's settings<. So you should
really format stDate explicitly (using the Format$() function) into
month/day/year order in the SQL statement.

HTH,
TC
"Larry R Harrison Jr" <la*******@cox.net> wrote in message
news:ZjYsb.912$6G3.215@fed1read06...

I have an Access 97 database with 2 tables that have a one-many
relationship. I have a SQL statement in the "one" table which I want to
execute and insert 7 records into the "many" table, and I want them to be
linked to the "main" table.

Problem is, it won't let me run the SQL run as long as the one-many
relationship is established in the relationship window.

The SQL is:

Dim sSQL As String
For stDate = stBegDate To stEndDate
sSQL = "INSERT INTO tblScheduleActual_Test ( date1 ) SELECT #" & stDate & "# AS Expr1;"
DoCmd.RunSQL sSQL
Next stDate

It loops through the dates of the week and inserts 7 records with successive dates.

It works fine if I remove the relationship, but I figured the relationship
should be established in the relationship window.

Tips?

LRH

[Pieter Linden]

"Larry R Harrison Jr" <la*******@cox.net> wrote in message news:<ZjYsb.912$6G3.215@fed1read06>...

I have an Access 97 database with 2 tables that have a one-many
relationship. I have a SQL statement in the "one" table which I want to
execute and insert 7 records into the "many" table, and I want them to be
linked to the "main" table.

Problem is, it won't let me run the SQL run as long as the one-many
relationship is established in the relationship window.

The SQL is:

Dim sSQL As String
For stDate = stBegDate To stEndDate
sSQL = "INSERT INTO tblScheduleActual_Test ( date1 ) SELECT #" & stDate & "#
AS Expr1;"
DoCmd.RunSQL sSQL
Next stDate

It loops through the dates of the week and inserts 7 records with successive
dates.

It works fine if I remove the relationship, but I figured the relationship
should be established in the relationship window.

Tips?

LRH

You must be violating referential integrity. Otherwise it would work
fine. You need to include the primary key from the 1-side table...
Sub InsertValues(dtStart as date, dtFinish as date)

dim db as dao.database
dim rs as dao.recordset
dim dtDate As Date

set db = dbengine(0)(0)
set rs=db.opentable("MyDates",dbOpenTable)
for dtDate - dtStart to dtFinish
rs.Addnew
rs.Fields("MyDate")=dtDate
rs.Update
next dtDate

rs.close
set rs=nothing
set db=nothing

or use db.Execute... but that and RunSQL are SLOW!!! (try looping like
1000 times... you'll cry it's so slow).