Split database

"Rick Brandt" wrote:

AutoNumber usage makes the use of "temp" or "work" tables very problematic.

You would first have to append the flight data to the real table omitting the
AutoNumber field so that the back end applies its own value. Then you would
have to retrieve that value so you can use it when you append the crew data.
Depending on how you do that and how many users might be simultaneously
appending records this can be very tricky.

I respectfully disagree. When using DAO the following works very
well to determine the last record just inserted.

RS.Move 0, RS.LastModified
lngTableListID = RS!stlid

If using SQL then it I vaguely recall something about some kind of @@
SQL command would work even in an Jet database but I don't recall any
details now.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Tony Toews [MVP] wrote:

If using SQL then it I vaguely recall something about some kind of @@
SQL command would work even in an Jet database but I don't recall any
details now.

Yes, with ADO, I think it could look something like the following

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last
Identity (Autonumber) inserted on this connection.
--
Roy-Vidar

RoyVidar wrote:

If using SQL then it I vaguely recall something about some kind of @@
SQL command would work even in an Jet database but I don't recall any
details now.

Yes, with ADO, I think it could look something like the following

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last
Identity (Autonumber) inserted on this connection.

Yup, that looks familiar.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

RoyVidar wrote:

Set rs = cn.Execute("SELECT @@Identity", , adCmdText)

It sure surprised me when someone first posted that a few years ago
and stated that worked well against a Jet database.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

David W. Fenton wrote:
"Tony Toews [MVP]" wrote in
:

RoyVidar wrote:

Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
It sure surprised me when someone first posted that a few years
ago and stated that worked well against a Jet database.

That's a feature of ADO, not of Jet.

If that was true, then the following shouldn't work

Sub DAO_Identity()

Dim rs As DAO.Recordset

With DBEngine(0)(0)

On Error Resume Next
.Execute "CREATE TABLE DAO_IdentityTest " & _
"(id Counter Primary Key, sometext Text(25))"
On Error GoTo 0
.Execute "INSERT INTO DAO_IdentityTest " & _
"(sometext) VALUES ('Just testing')"
Set rs = .OpenRecordset("SELECT @@Identity")
Debug.Print rs.Fields(0).Value

End With

End Sub

But it does (how reliable it is with concurrent users, I don't know).

Quoting from the book where I first encountered it (A2k Developers
Handbook, Sybex, 1999) p 197:

"The Jet 4 extensions also add support for querying the last-assigned
Auto-Number value using the same syntax as SQL Server

SELECT @@IDENTITY"

Jet 4 extensions, not ADO.

--
Roy-Vidar

RoyVidar wrote:

"The Jet 4 extensions also add support for querying the last-assigned
Auto-Number value using the same syntax as SQL Server

SELECT @@IDENTITY"

Jet 4 extensions, not ADO.

I'll be darned. Maybe I should go back and reread those books front
to back and see what else there is to learn.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/