access97: append query and referential intergrity problems

[JMCN]

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.Shortname AS [ET LN Shortname],
tblBM_OD_REPORT___final.[OD Amt] AS [ET Amount]
FROM tblBM_OD_REPORT___final
WHERE (((tblBM_OD_REPORT___final.[Days Open])=3))
ORDER BY tblBM_OD_REPORT___final.Shortname;

[Rich P]

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!

[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.google.c om...

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.Shortname AS [ET LN Shortname],
tblBM_OD_REPORT___final.[OD Amt] AS [ET Amount]
FROM tblBM_OD_REPORT___final
WHERE (((tblBM_OD_REPORT___final.[Days Open])=3))
ORDER BY tblBM_OD_REPORT___final.Shortname;

[JMCN]

Rich P <rp*****@aol.com> 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 :)

[Mike Sherrill]

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

[JMCN]

Mike Sherrill <MS*************@compuserve.com> 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
"GetNextTrackingNumber":

Dim Dbe As Database
Dim rst As Recordset

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

If rst.RecordCount > 0 Then
rst.MoveLast
GetNextTrackingNumber = rst![ET Number] + 1
Else
GetNextTrackingNumber = 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.

[Mike Sherrill]

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

[JMCN]

Mike Sherrill <MS*************@compuserve.com> 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 GetNextTrackingNumber() As Long

Dim Dbe As Database
Dim rst As Recordset

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

If rst.RecordCount > 0 Then
rst.MoveLast
GetNextTrackingNumber = rst![ET Number] + 1
Else
GetNextTrackingNumber = 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 GetNextTrackingNumber, then why can i not add it in my
code below?

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

'step one: delete the tdf("tblImportET")
dbsEtrack.TableDefs.Delete ("tblImportET")

'step two: Create a new TableDef object.
Set tdfNew = dbsEtrack.CreateTableDef("tblImportET")

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

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

dbsEtrack.TableDefs.Refresh
dbsEtrack.Execute "INSERT INTO [tblImportET] SELECT * FROM
[qryBMODReportWeekday] ", dbFailOnError

so if you have any idea, it would be definitely appreciated!!!
merci - thanks! jung

[Mike Sherrill]

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 GetNextTrackingNumber, 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("tblImportET")
dbsEtrack.TableDefs.Delete ("tblImportET")
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.CreateTableDef("tblImportET")

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

''GetNextTrackingNumber 'call function in the Etrack routine module
'.Fields.Append.CreateField("ET 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.TableDefs.Append tdfNew

[snip]

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

--
Mike Sherrill
Information Management Systems

[JMCN]

Mike Sherrill <MS*************@compuserve.com> 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 GetNextTrackingNumber, 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("tblImportET")
dbsEtrack.TableDefs.Delete ("tblImportET")

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.CreateTableDef("tblImportET")

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

''GetNextTrackingNumber 'call function in the Etrack routine module
'.Fields.Append.CreateField("ET 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.TableDefs.Append tdfNew

[snip]

TableDefs.Append means you're finished creating the table. Now you
can insert values into it. But, if you must use
GetNextTrackingNumber(), 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