473,545 Members | 534 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Run SQL For "many" table with relationship Established

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 tblScheduleActu al_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
Nov 12 '05 #1
2 3409
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@fed1rea d06...
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 tblScheduleActu al_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

Nov 12 '05 #2
"Larry R Harrison Jr" <la*******@cox. net> wrote in message news:<ZjYsb.912 $6G3.215@fed1re ad06>...
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 tblScheduleActu al_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(dt Start 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",dbOp enTable)
for dtDate - dtStart to dtFinish
rs.Addnew
rs.Fields("MyDa te")=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).
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2429
by: Bill MacLean | last post by:
-- Example Schema posted at end of message: --- For reporting purposes, I need to build a single comma delimited string of values from the "many" table of a database. The easiest way to see what I want is to look at the sample after my signature. By the way, this is actually a busines problem, not homework! I just
3
2245
by: Paul Janssen | last post by:
Hello! Can anyone help me out with the following situation: (a) a single query with 550 id's in the IN-clause resulting into 800+ seconds; (b) 550 queries with a single id in the IN-clause resulting into overall time of <60 seconds; The table consists of 950.000 records, and the resultset consists of 205.000 records.
6
3079
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from the table, but I was not allowed to do that, because "there are records related with those PRODUCTS in other tables (e.g. in table "ORDER_DETAIL").
3
1161
bartonc
by: bartonc | last post by:
Most of the lines in this fuction deal with null data and default data: def FillFromDB(self): gradeSense = None slopeSense = None gradeOffset = None slopeOffset = None varList = (gradeSense, slopeSense, gradeOffset, slopeOffset) cursor = self.dbServer.Execute("SELECT * FROM...
0
2648
by: Ofelia | last post by:
Hi, I'm new to this forum and to Perl language but I would like to ask for your help. I'm working in Linux and the files I need to process are in the format “file.gz”. I created a script which should decompress, open and then delete nearly 400 files. To do so I use "open FILEPT, "zcat $filename|"". In the beginning the script works fine, but...
4
5100
bugboy
by: bugboy | last post by:
I'm inserting a new word into table 'w' and a definition into table 'c' which are linked in table 's' which is the relation table for the many to many relationship between 'w' and 'c'. I've been passed 2 variables from a form; $word and $def and i'm inserting them ok but i'm having trouble getting and passing on their ID's in order to insert...
1
1427
by: Cirene | last post by:
I have a gridview that displays a listing of cars for sale. It's based on a "Cars" table. (The "Cars" table has a 1 to many relationship to "CarPictures" table.) On each gridview row (ie for each car) I want to show just 1 picture from the "CarPictures" table. Any suggestions as to how to do this?
5
2105
by: lsllcm | last post by:
Hi All, I have one question about many "or" operation make system choose incorrect index There is one table TT ( C1 VARCHAR(15) NOT NULL, C2 VARCHAR(15) NOT NULL, C3 VARCHAR(15) NOT NULL, C4 VARCHAR(15) NOT NULL
1
1191
by: per9000 | last post by:
We have a big application written without thought of supporting multiple languages with lots of strings in a default "english". Now the client wants to support any language (with a latin alphabet) and we need to redo a lot. I'm sort of thinking that we are not the first ones to end up in this kind of situation and I'd like to know if there...
0
7456
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7390
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7410
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5320
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4940
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3442
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3437
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1869
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
692
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.