Re: Retrieving Auto increment field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.


"Derek" <Derek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1C6D65D1-1B57-4967-9A3B-2F18D12E5004@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • RE: Reduce # of date columns in crosstab query
    ... "Sales by Product temp" for that product, so it won't show up no matter how I ... it from the final calculation in the crosstab query. ... the criteria out of the crosstab and into something like the original query. ... You could use the column headings to limit the number of columns; ...
    (microsoft.public.access.queries)
  • Re: Was: what does "serialization" mean?
    ... > The data volumes in the case of Walmart are huge ... the sales and inventory database has ... WHATEVER would probably be a separate query to summarize total sales. ... Indeed the psychology of hardware efficiency encourages flaccid design ...
    (comp.programming)
  • Re: Incorrect subtotals in a report
    ... You can create multiple values by combining a crosstab and cartesian query. ... > sales, ... > Subsection 01 subtotals for all of the above quantities. ... whilst the daily totals are incorrect. ...
    (microsoft.public.access.reports)
  • Re: a query to produce sales activity totals
    ... The query generates a row for each salesperson, ... Allen Browne - Microsoft MVP. ... of the sales process, including the number of sold and delivered vehicles. ... put these totals into a spread sheet that shows each salesperson's ...
    (microsoft.public.access.queries)
  • Re: Need help automating a manual process
    ... I would add the query "SQL for Sales Activity by Sales Person" to the VBA ...
    (microsoft.public.access.modulesdaovba)