Recent SQL Migration

[Tim Johnson]

Hi there,

I recently migrated the backend of a FE/BE Access setup to a SQL Server.
The transition of data has gone fairly smoothly.

However, in testing the FE seems to be having some growing pains
(e.g. recordsets now need the 'dbSeeChanges' option added, etc.) One such
problem, which I have found a temporary solution for is as follows:

In Access, when adding a new record to a recordset, the Autonumber field
automatically generates, allowing you to assign this ID to a variable in code
PRIOR to update. SQL server does not, in fact, it doesn't appear to want me
to assign this ID to the variable after update, either. I am currently using
the DMax function, and it seems to be working fine, but am not entirely
certain that this is the best/most reliable way to obtain the most recently
generated ID.

Any suggestions?

Thanks in advance,
Tim

 

[Rui]

SQL server does not have an 'autonumber' attribute for a field in a table.
But it does have an equivalent called 'identity'. This provides a unique,
incremental value for the column/field

You will need to edit your tables and amendthe field properties to implement
it.

i.e.
[field name] | [data type] | [identity specification]
pk_fieldid | int | is identity - yes (default incremental
seed is 1)


Hope this helps
Rui

 

[Tim Johnson]

Rui, thanks for your response, though it doesn't quite answer the question I
have.

Let me rephrase:

I have made a successful migration of tables, Identity properties on the
tables and all. Automatically generating an incremental ID is not my issue.

Access commits this generated ID as soon as you START to create a record;
that number is commited forever, whether you save the record or not.

SQL appears to wait until the record is commited to create the incremental
number; making the code:

rst.addnew
rst.fields("SomeField") = "New Data"
intNewRecordID = rst.fields("ID")
rst.update

throw an error. I initially used DMax("[ID]","SomeTable") after the update
as a method of grabbing that ID, but it seems to me that this is a risky and
inappropriate method by which to grab that ID number (esp. in a multi-user
environment); I am currently bookmarking the lastmodified instead, and am
pretty sure that this will work, but am still a little concerned about
multiple users affecting the outcome and altering the variable. Is there a
better method?

Thanks in advace,
Tim

SQL server does not have an 'autonumber' attribute for a field in a table.
But it does have an equivalent called 'identity'. This provides a unique,
incremental value for the column/field

You will need to edit your tables and amendthe field properties to implement
it.

i.e.
[field name] | [data type] | [identity specification]
pk_fieldid | int | is identity - yes (default incremental
seed is 1)


Hope this helps
Rui

Hi there,

I recently migrated the backend of a FE/BE Access setup to a SQL Server.
The transition of data has gone fairly smoothly.

However, in testing the FE seems to be having some growing pains
(e.g. recordsets now need the 'dbSeeChanges' option added, etc.) One such
problem, which I have found a temporary solution for is as follows:

In Access, when adding a new record to a recordset, the Autonumber field
automatically generates, allowing you to assign this ID to a variable in code
PRIOR to update. SQL server does not, in fact, it doesn't appear to want me
to assign this ID to the variable after update, either. I am currently using
the DMax function, and it seems to be working fine, but am not entirely
certain that this is the best/most reliable way to obtain the most recently
generated ID.

Any suggestions?

Thanks in advance,
Tim

 

[Tony Toews [MVP]]

I am currently bookmarking the lastmodified instead, and am
pretty sure that this will work, but am still a little concerned about
multiple users affecting the outcome and altering the variable. Is there a
better method?

FWIW I've been using the lastmodified property for quite a number of
years in the Access world and have never had a problem with it. Not
against a SQL Server backend though.

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/