Retrieving Auto increment field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am entering information into two tables (Sales and Forecasts). The Sales
table has an autoincrement field (RecordID). RecordID is then used as a
Foreign key in the second table (where it is a normal long).

I am using excel as a front end, and ADO to connect to the database. My
code currently works as follows.
1) Insert into the Sales table.
2) Query the Sales table to get the RecordID.
3) Insert into the Forecasts table.

The problem I'm having is that step 2 is often failing. It appears that the
information hasn't been "fully" inserted into Access, and therefore the query
returns no records.

I would prefer not to get the number manually, as it is possible that two
users will be inserting at the same time, and I'm worried the numbers will
get confused.

How do I get the autoincrement number successfully?

Thanks
Derek
 
Derek:

The following KB article shows how to use the an @@Identity SELECT query to
retrieve the autoincrement value. If you are using version 4.0 of the JET
OLEDB provider then you can consider this as a possible alternative. Since
the value returned is connection specific, you should get the desired
results in a multi-user environment.

http://support.microsoft.com/default.aspx?scid=kb;en-us;232144

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am entering information into two tables (Sales and Forecasts). The Sales
table has an autoincrement field (RecordID). RecordID is then used as a
Foreign key in the second table (where it is a normal long).

I am using excel as a front end, and ADO to connect to the database. My
code currently works as follows.
1) Insert into the Sales table.
2) Query the Sales table to get the RecordID.
3) Insert into the Forecasts table.

The problem I'm having is that step 2 is often failing. It appears that the
information hasn't been "fully" inserted into Access, and therefore the
query
returns no records.

I would prefer not to get the number manually, as it is possible that two
users will be inserting at the same time, and I'm worried the numbers will
get confused.

How do I get the autoincrement number successfully?

Thanks
Derek
 
The program has now gone live and I'm having some problems.

Essentially communication to the database (over a WAN) is now very slow.
Sometimes it is near normal speed, sometimes it is very slow.

Initially I thought it was a network problem, but things are now pointing to
my application.

One user took nearly 1 1/2 hours to run code that inserted 1000 rows into
the database (most were 4 fields wide). Network connections show this user
had nearly 53Mb of information transfered to and from the server during that
time. User says she wasn't running any other programs at the time.

I have inherited the code from another developer - and to be honest I'm more
used to SQL Server. Is there some codes I should or shouldn't be using? How
do I investigate further?

Thanks in advance.
 
Back
Top