Primary Key Question

[Alastair MacFarlane]

Dear all,

Is there any way in access that I can switch of the PrimaryKey constraint
off and Append data through an action query and start the PrimaryKey
constraint again.

If I have a table that has:

ID (PK) - Autonumber
Field1

with values of :

ID: 1; Field1: A
ID: 2; Field1: B
ID: 4; Field1: C

How can insert an ID of 3 and any filed value into the Field1 column? If you
add another record you will get the next autonumber (5 in this case) but I
want it to be 3. Can this be achieved? You can remove the PrimaryKey
constraint, add the data but you then can't save the table.

Thanks again for any thoughts.

Alastair

 

[Jeff Boyce]

You did say "any thoughts"...

The Access Autonumber field type is designed to be used as a unique row
identifier, and is generally unfit for human consumption. If you are
displaying the Autonumber as an ID to a user, that user needs to understand
that there will be gaps, and that the Autonumber ID is not (always)
sequential.

Moreover, if you were able to alter Autonumbers as a Primary Key in a parent
table, you'd be, at best, orphaning any child table records that pointed
back to the parent table with that ID. At worst, you'll be irrevocably
corrupting the connection between the two.

If you'll describe what you are trying to accomplish (what business need are
you trying to satisfy), the 'group readers may be able to offer alternative
approaches.

 

[Douglas J. Steele]

While I agree whole-heartedly with Jeff's comments, you can use INSERT INTO
queries, providing a value for the Autonumber field, and it'll work.

 

[Alastair MacFarlane]

Douglas and Jeff,

I have 2 databases, one live and one backup. The contents of backup is made
up of exported and deleted records from the live system. The client states
that they would like to be able to un-archive some these records after a
couple of years. I am trying to create this utility in a database
application with a VB front end. I have tried the INSERT INTO and this does
work (I was not sure until I tested it!). I would obviously like the records
to be inserted into the system painlessly.

From your wealth of experience do you see any problems with this approach?
There are about 10 tables that may be un-archived and as long as I start
with the one side, do you foresee any problems? It is the volume of data and
speed that makes me think that this approach would be the best way.

Thanks again for the group's comments and support.

Alastair

 

[Alastair MacFarlane]

Dear All,

Is there a way of running an INSERT INTO query as below that does not treat
the whole INSERT as a transaction, whereby if one insert fails the whole
batch fails. The last thing I want to do is loop through the table to insert
the rows one-by-one?

INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) IN 'C:\Mydb.mdb'
SELECT tmpWBRestore.WBID, tmpWBRestore.PKid, tmpWBRestore.WeekBegin
FROM tmpWBRestore;

Thanks

Alastair

 

[Joseph Meehan]

Douglas and Jeff,

I have 2 databases, one live and one backup. The contents of backup
is made up of exported and deleted records from the live system. The
client states that they would like to be able to un-archive some
these records after a couple of years. I am trying to create this
utility in a database application with a VB front end. I have tried
the INSERT INTO and this does work (I was not sure until I tested
it!). I would obviously like the records to be inserted into the
system painlessly.
From your wealth of experience do you see any problems with this
approach? There are about 10 tables that may be un-archived and as
long as I start with the one side, do you foresee any problems? It is
the volume of data and speed that makes me think that this approach
would be the best way.
Thanks again for the group's comments and support.

Alastair

It sounds like you are doing it the hard way. Normally when one needs
to "archive" data in Access on the chance that it may be needed later or
someone may want to view it a simple binary filed us used to indicate
archive and all regular accesses of data simply filter out the "archived"
data. It makes doing what you want to do far easier.

 

[Alastair MacFarlane]

Thanks again Joseph,

I quite agree that this is the hard way, but in this system I think it is
the most effective way. I will take your advice on board for smaller
systems. Thanks again.

Alastair MacFarlane

 

[Douglas J. Steele]

The only way would be to set up a loop, and insert the rows one by one:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT WBID, PKid, WeekBegin FROM
tmpWBRestore")
With rsCurr
Do While .EOF = False
strSQL = "INSERT INTO tblWeekBeginning ( WBID, PKid, WeekBegin ) "
& _
"IN 'C:\Mydb.mdb' " & _
"VALUES(" & !WBID & ", " & !PKid & ", " & _
Format(!WeekBegin, "\#mm\/dd\/yyyy\#") & ")"
dbCurr.Execute strSQL, dbFailOnError
Loop
.Close
End With

Set rsCurr = Nothing
Set dbCurr = Nothing

This assumes that WBID and PKid are both numeric fields, and that WeekBegin
is a date field.

BTW, I agree with Joseph: this really doesn't seem to be very efficient.

 

[Alastair MacFarlane]

Douglas,

I have learnt my lesson and won't do it this way again. I will alter my
strategy for future projects, and I appreciate your comments and sample
code.

Alastair

 

[Guest]

BTW, you have a slight mis-understanding of "Primary Key
Constraint" and "Autonumber".

The Constraint, which in Access/Jet is part of an Index, can
be switched on and off: you can drop the index, then re-create
the index later.

The data type (Autonumber), can be changed to a number,
but can't be changed back to Autonumber.

You can have an Autonumber field which is not indexed,
does not have constraints, and is not a primary key. You
can have duplicate values in the field if you use an append
query to append records.

You can also have a number field, not Autonumber, with a
Primary Key Index, (which includes the constraint that values
must be non-null and unique). The primary key index/constraint
won't let you append duplicate values.

(david)

 

[Guest]

The current version of Jet (Jet 4 Sp8) corrupts the
autonumber seed when you append to linked tables.

http://support.microsoft.com/?scid=kb;en-us;884185&spid=2509&sid=106

If you use INSERT INTO to insert values into an Autonumber
field in a linked table you will probably meet this bug.

(david)