473,851 Members | 2,297 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

access97: append query and referential intergrity problems

hi-

i have inherited an access 97 database that keeps track of the loans.
i have been running into referential intergrity problems when i try to
append new loans to [Exception Tracking] table.

first of all [Exception Tracking] is a linked table from another ms
database. when i try to run the append query, i receive an error
message that it cannot append all the records due to key violations to
the [Exception Tracking]. so i looked at all of the relationships and
there aren't any.

i have no idea why my append query will not append new records. if
anyone could suggest went when wrong, it would be greatly appreciated.
thanks in advance - jung

here is the sql code (which will be easier to understand):

INSERT INTO [Exception Tracking] ( [ET LN Shortname], [ET Amount] )
SELECT tblBM_OD_REPORT ___final.Shortn ame AS [ET LN Shortname],
tblBM_OD_REPORT ___final.[OD Amt] AS [ET Amount]
FROM tblBM_OD_REPORT ___final
WHERE (((tblBM_OD_REP ORT___final.[Days Open])=3))
ORDER BY tblBM_OD_REPORT ___final.Shortn ame;
Nov 12 '05 #1
9 4520
The field names can be different, but the field properties need to be
identical for the fields involved. If a textfield in one table is 30
and 45 in the other, they both need to be either 30 or 45. If one field
is indexed in one table, but not in the other, they either both need to
be indexed or not indexed. If a number is a long in one but a double in
the other, you get the idea.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2
TC
The relationships are defined & enforced in the linked-to database, not the
linking-from one. I suspect that you are looking in the latter, not the
former.

HTH,
TC
"JMCN" <pi******@yahoo .fr> wrote in message
news:27******** *************** ***@posting.goo gle.com...
hi-

i have inherited an access 97 database that keeps track of the loans.
i have been running into referential intergrity problems when i try to
append new loans to [Exception Tracking] table.

first of all [Exception Tracking] is a linked table from another ms
database. when i try to run the append query, i receive an error
message that it cannot append all the records due to key violations to
the [Exception Tracking]. so i looked at all of the relationships and
there aren't any.

i have no idea why my append query will not append new records. if
anyone could suggest went when wrong, it would be greatly appreciated.
thanks in advance - jung

here is the sql code (which will be easier to understand):

INSERT INTO [Exception Tracking] ( [ET LN Shortname], [ET Amount] )
SELECT tblBM_OD_REPORT ___final.Shortn ame AS [ET LN Shortname],
tblBM_OD_REPORT ___final.[OD Amt] AS [ET Amount]
FROM tblBM_OD_REPORT ___final
WHERE (((tblBM_OD_REP ORT___final.[Days Open])=3))
ORDER BY tblBM_OD_REPORT ___final.Shortn ame;

Nov 12 '05 #3
Rich P <rp*****@aol.co m> wrote in message news:<3f******* *************** *@news.frii.net >...
The field names can be different, but the field properties need to be
identical for the fields involved. If a textfield in one table is 30
and 45 in the other, they both need to be either 30 or 45. If one field
is indexed in one table, but not in the other, they either both need to
be indexed or not indexed. If a number is a long in one but a double in
the other, you get the idea.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


thanks! i changed the field properties and made sure they are exactly
as the linked table [Exception Tracking] but I still run into the key
violations. could one of the problems be due to the field called
Number, which is a primary key and is indexed (cannot contain a Null
value?). i suspect that is the case. ugh. i might have just answered
my part of my question.

thanks again both for your advice :)
Nov 12 '05 #4
On 1 Dec 2003 14:40:13 -0800, pi******@yahoo. fr (JMCN) wrote:
when i try to run the append query, i receive an error
message that it cannot append all the records due to key violations to
the [Exception Tracking]. so i looked at all of the relationships and
there aren't any.


Is there a primary key or a unique index defined on any column(s) in
the [Exception Tracking] table? That's what you should look for.

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #5
Mike Sherrill <MS************ *@compuserve.co m> wrote in message news:<m5******* *************** **********@4ax. com>...
On 1 Dec 2003 14:40:13 -0800, pi******@yahoo. fr (JMCN) wrote:
when i try to run the append query, i receive an error
message that it cannot append all the records due to key violations to
the [Exception Tracking]. so i looked at all of the relationships and
there aren't any.


Is there a primary key or a unique index defined on any column(s) in
the [Exception Tracking] table? That's what you should look for.


mike -
yes there is a primary key and a unique index defined (yes(no
duplicates) on the ET number field in Exception Tracking table. I
have created many tests to figure out if i could append the data
without the primary key and index on ET number field, which did work.

the reason why there is the primary key and index on ET number because
it suppose to be the next sequence number. for instance when a user
creates a new record in a form, a function is called
"GetNextTrackin gNumber":

Dim Dbe As Database
Dim rst As Recordset

Set Dbe = DBEngine(0)(0)
Set rst = Dbe.OpenRecords et("SELECT * FROM [Exception Tracking] ORDER
BY [ET Number] ASC")

If rst.RecordCount > 0 Then
rst.MoveLast
GetNextTracking Number = rst![ET Number] + 1
Else
GetNextTracking Number = 100000
End If

rst.Close
Dbe.Close
so in the end, i'm thinking of creating a new tabledef with this
function to see if i can get the next sequence number... or something
like that... i don't know yet :) like i said before, i inherited this
database. perhaps it would be best to recreate it :) i don't know.
thanks again!! cheers! jung.
Nov 12 '05 #6
On 4 Dec 2003 08:45:06 -0800, pi******@yahoo. fr (JMCN) wrote:
yes there is a primary key and a unique index defined (yes(no
duplicates) on the ET number field in Exception Tracking table. I
have created many tests to figure out if i could append the data
without the primary key and index on ET number field, which did work.
Are you saying that you cannot append the data unless you drop the
primary key constraint? If so, that would seem to be your
problem--you're not providing suitable values for the primary key.
the reason why there is the primary key and index on ET number because
it suppose to be the next sequence number.


Are you providing the next sequence number?

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #7
Mike Sherrill <MS************ *@compuserve.co m> wrote in message news:<gh******* *************** **********@4ax. com>...
On 4 Dec 2003 08:45:06 -0800, pi******@yahoo. fr (JMCN) wrote:
yes there is a primary key and a unique index defined (yes(no
duplicates) on the ET number field in Exception Tracking table. I
have created many tests to figure out if i could append the data
without the primary key and index on ET number field, which did work.


Are you saying that you cannot append the data unless you drop the
primary key constraint? If so, that would seem to be your
problem--you're not providing suitable values for the primary key.
the reason why there is the primary key and index on ET number because
it suppose to be the next sequence number.


Are you providing the next sequence number?


Yes I cannot append the data unless if I drop the primary key
constraints. The values are the next sequence number that is written
in a routine:

Public Function GetNextTracking Number() As Long

Dim Dbe As Database
Dim rst As Recordset

Set Dbe = DBEngine(0)(0)
Set rst = Dbe.OpenRecords et("SELECT * FROM [Exception Tracking] ORDER
BY [ET Number] ASC")

If rst.RecordCount > 0 Then
rst.MoveLast
GetNextTracking Number = rst![ET Number] + 1
Else
GetNextTracking Number = 100000
End If

rst.Close
Dbe.Close

End Function

what i don't understand is if one can add a sequence number by means
of the Function GetNextTracking Number, then why can i not add it in my
code below?

Set dbsEtrack = CurrentDb
'Set rstET = ("Exception Tracking")

'step one: delete the tdf("tblImportE T")
dbsEtrack.Table Defs.Delete ("tblImportE T")

'step two: Create a new TableDef object.
Set tdfNew = dbsEtrack.Creat eTableDef("tblI mportET")

With tdfNew
.Fields.Append .CreateField("E T LN Shortname", dbText, 16)
.Fields.Append .CreateField("E T Amount", dbCurrency)
.Fields.Append .CreateField("E T EC Code", dbText, 120)

''GetNextTracki ngNumber 'call function in the Etrack routine module
'.Fields.Append .CreateField("E T Number", dbLong) = rstET![ET
Number] + 1
'cannot append when it is a primary key and is indexed
dbsEtrack.Table Defs.Append tdfNew
End With

dbsEtrack.Table Defs.Refresh
dbsEtrack.Execu te "INSERT INTO [tblImportET] SELECT * FROM
[qryBMODReportWe ekday] ", dbFailOnError

so if you have any idea, it would be definitely appreciated!!!
merci - thanks! jung
Nov 12 '05 #8
On 9 Dec 2003 15:20:49 -0800, pi******@yahoo. fr (JMCN) wrote:
Yes I cannot append the data unless if I drop the primary key
constraints. The values are the next sequence number that is written
in a routine: [snip]what i don't understand is if one can add a sequence number by means
of the Function GetNextTracking Number, then why can i not add it in my
code below?
Two reasons. First, you're trying to execute the function in the
wrong place at the wrong time. Second, because the function would be
evaluated only once for the entire set of rows you're trying to
insert. (If you got it in the right place.)
Set dbsEtrack = CurrentDb
'Set rstET = ("Exception Tracking")

'step one: delete the tdf("tblImportE T")
dbsEtrack.Tabl eDefs.Delete ("tblImportE T")
I'd be a little surprised if this were really necessary. In any case,
using a temporary *database* (MDB) for the temporary table is Best
Practice.
'step two: Create a new TableDef object.
Set tdfNew = dbsEtrack.Creat eTableDef("tblI mportET")

With tdfNew
.Fields.Append .CreateField("E T LN Shortname", dbText, 16)
.Fields.Append .CreateField("E T Amount", dbCurrency)
.Fields.Append .CreateField("E T EC Code", dbText, 120)

''GetNextTracki ngNumber 'call function in the Etrack routine module
'.Fields.Append .CreateField("E T Number", dbLong) = rstET![ET
Number] + 1
You're in the process of creating a table here. You haven't finished.
You can't insert values until you're finished.
'cannot append when it is a primary key and is indexed
dbsEtrack.Table Defs.Append tdfNew

[snip]

TableDefs.Appen d means you're finished creating the table. Now you
can insert values into it. But, if you must use
GetNextTracking Number(), I think you'll have to append one row at a
time.

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #9
Mike Sherrill <MS************ *@compuserve.co m> wrote in message news:<h3******* *************** **********@4ax. com>...
On 9 Dec 2003 15:20:49 -0800, pi******@yahoo. fr (JMCN) wrote:
Yes I cannot append the data unless if I drop the primary key
constraints. The values are the next sequence number that is written
in a routine:

[snip]
what i don't understand is if one can add a sequence number by means
of the Function GetNextTracking Number, then why can i not add it in my
code below?


Two reasons. First, you're trying to execute the function in the
wrong place at the wrong time. Second, because the function would be
evaluated only once for the entire set of rows you're trying to
insert. (If you got it in the right place.)
Set dbsEtrack = CurrentDb
'Set rstET = ("Exception Tracking")

'step one: delete the tdf("tblImportE T")
dbsEtrack.Tabl eDefs.Delete ("tblImportE T")


I'd be a little surprised if this were really necessary. In any case,
using a temporary *database* (MDB) for the temporary table is Best
Practice.
'step two: Create a new TableDef object.
Set tdfNew = dbsEtrack.Creat eTableDef("tblI mportET")

With tdfNew
.Fields.Append .CreateField("E T LN Shortname", dbText, 16)
.Fields.Append .CreateField("E T Amount", dbCurrency)
.Fields.Append .CreateField("E T EC Code", dbText, 120)

''GetNextTracki ngNumber 'call function in the Etrack routine module
'.Fields.Append .CreateField("E T Number", dbLong) = rstET![ET
Number] + 1


You're in the process of creating a table here. You haven't finished.
You can't insert values until you're finished.
'cannot append when it is a primary key and is indexed
dbsEtrack.Table Defs.Append tdfNew

[snip]

TableDefs.Appen d means you're finished creating the table. Now you
can insert values into it. But, if you must use
GetNextTracking Number(), I think you'll have to append one row at a
time.

okay, that makes sense adding one row at a time for the
trackingnumber. i think i'm just going to start over and recreated the
whole darn database over again.

many thanks!!! i appreciate your help:)
cheers - jung
Nov 12 '05 #10

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

Similar topics

2
4007
by: Richard Coutts | last post by:
I have a simple table with a Yes/no field called "Select." I have an append query that adds items to the table. As items get added, I want the "Select" field of the new items set to True. My query looks like this: INSERT INTO tblLocations ( LocationName, LocationType, Job, ) SELECT tblJobTypeLocations.LocationName, tblJobTypeLocations.LocationType, Forms!frmMAIN!cbxJob AS Job, True AS Expr1
1
2488
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to two csv files. (One for updated data, and the other for unupdated data.) The CSV files are attached to my Jobcosting database. After the CSV
4
7087
by: robboll | last post by:
When I try to use an append query from an oracle link it takes forever. I am exploring the idea of doing an append action using a pass-through query. If I have an Oracle ODBC connection to server OraTest. User: User1 Password: password and I am trying to append all records in table: tblTEST that are code: "abc"
4
5772
by: pmacdiddie | last post by:
I have an append query that needs to run every time a line item is added to a subform. The append writes to a table that is the source for a pull down box. Problem is that it takes 5 seconds to run. This makes adding lines to an order too slow for the users. The result of the query provides real time availabilty, so I really do need this to work. The tblJobs has only 10,000 records, tblJobsLineItems has 150,000 records.
1
3223
by: S.Dickson | last post by:
I have a form that has a combo box thats displays all the suppliers I then have subform that shows all the products that we buy from them and how many have been bought and sold therefore work out stock remaining I then have a button that runs a append query that opens up a Purchase order form with a subform that has the products that have to be bought
11
7911
by: kabradley | last post by:
Hello Everyone, So, thanks to nico's help I was finally able to 'finish' our companies access database. For the past week or so though,I have been designing forms that contain a subform and an option group so that whenever a certain button on the option group is pressed the correct subform source object property is changed to display the correct form. For instance, if they click "add new investment" the subform's source object is now changed to...
4
3376
by: Scott12345 | last post by:
Hi, here is my situation, I have a DB that tracks machine downtime (30 machines) per day. Several users will update this through the day. I created an append query that creates 30 dummy values and then opens up a form that displays all 30 values. The user can then go through these and change the ones they are responsible for. After they have updated this form, i have a delete query that searches for the dummy values and removes them. My...
1
2090
by: MLH | last post by:
Anyone remember if A97 append query failure would ever report data breaking validation rule when such was not the case. I have an old SQL statement - several years old now. I've encountered a case in which the append fails and the reported error is validation rule. The table being appended to (tblClusters) has only 1 field with a validation rule: , with Byte Field Size property setting. The validation rule is >0. The field's default...
4
6811
by: Adam Tippelt | last post by:
Situation: When a user 'confirms' that they want to save the information they've inputted, I need to append the database records from a temporary table to a different table. Problem: The data spans across 3 related tables - scripts, tests and data. One script can have many tests, and one test can have many data entries. When the records are appended, they're also assigned new primary keys, which means the relationships needed to be...
0
9895
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9744
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11011
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10725
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
10352
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7900
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5735
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4547
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
2
4142
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.