Saving to 2 tables using SQL

[Guest]

I need to add and save a new record to 2 tables related by the field
CompanyID using SQL. CompanyID is an auto-number (and increments) in Table1
but not in Table2. I can add a new record to Table1 but not sure how to save
the same information (CompanyID) to Table2.

Since CompanyID is an auto-number in Table1, should I get the last CompanyID
in Table1, and assume that the next CompanyID will automatically equal to
last CompanyID incrementing 1? Will this cause any conflict should 2 users
open a new record at the same time?
What's the best way to do this? Thanks in advance for your help.

 

[Nikos Yannacopoulos]

Samantha,

See answers in line below.

HTH,
Nikos

I need to add and save a new record to 2 tables related by the field
CompanyID using SQL. CompanyID is an auto-number (and increments) in Table1
but not in Table2. I can add a new record to Table1 but not sure how to save
the same information (CompanyID) to Table2.

Since CompanyID is an auto-number in Table1, should I get the last CompanyID
in Table1, and assume that the next CompanyID will automatically equal to
last CompanyID incrementing 1?

No, you can't assume that! Autonumber fields have this nasty habit of
not releasing a number when a record is deleted or cancelled out from
during creation; the only exception is unused numbers beyond the last
one used, if a Compact and Repair is carried out. So, assuming your
autonumber had gone up to say, 9 at a time, then that record was
deleted, if a Compact and Repair is carried out, then 9 will be used
again; if a new record is created before a Compact and Repair, then it
will be assigned number 10, even though 9 is no longer used, and you'll
end up with a gap which you will never be able to reclaim. This is not a
problem, really, since autonumbers are only meant to provide unique
record identifiers, but it certainly doesn't let you work on the
assumption that the next one will be Max+1. The fact is that record
deletions and cancelled entries happen all the time, so don't do it.

Will this cause any conflict should 2 users open a new record at the same time?
What's the best way to do this?

In order to deal with assigneng the correct foreign key value in the new
record in the second table, the best way to go is use an unbound form
and create the new records in both tables through recordset operations.
Under this scenario, once you start creating the new record in the first
table (rst.AddNew) and before you save it (rst.Update), the autonumber
value is already available for you to read in your code, and then use it
in the second recordset operation when creating the record(s) for the
second table.
This method also minimizes the risk of conflicts in a multiuser
environment, as each new record creation in the first table through the
recordset operation will only take a few milliseconds (or less?), so the
chances of two overlapping are next to nothing.

 

[Guest]

Nikos,
Thanks for your response. So, that means that as long as I do NOT save the
new record (with a rst.update), then I can get retrieve the auto-number
CompanyID from Table1 and use it to save into Table2, and it will not cause
any conflict with simultaneous users?
thanks.
Samantha

 

[Nikos Yannacopoulos]

Smantha,

The nimute you execute an rst.AddNew, the autonumber value is made
available to read into a local variable, to then use in your second
recordset operation. something like:

Dim PKValue As Long
rs1.AddNew
PKValue = rs1.Fields("ThePKFieldNameHere")
rs1.Fiedls(whatever) = Me.SomeControl
....etc 'populate the record
rs1.Update

rs2.AddNew
rs2.Fields("TheFKFieldNameHere") = PKValue
rs2.Fiedls(whatever) = Me.SomeControl
....etc 'populate the record
rs2.Update

As you see, the autonumber value is not read from the table, it's read
from within the recordset operation of creating the record. In terms of
actual execution time, the amount of time required for this code to
execute - even if the second recordset operation is a loop creating a
number of fields - is so small, that the chances of conflict are next to
nothing, if any. I say "if any" because I believe (but can't justify it
right now) that even if there was a timing overlap, Jet would still
assign different autonumber values to the two separate processes.
Actually "next to nothing" would apply if you were assigning the value
in a non-autonumber field (like the last one plus one), in which case
you would have a real issue with conflicts using bound forms.

HTH,
Nikos