Observation - Sharepoint ID field vice Access Autonumber

[Dale Fye]

Interesting observation.

I'm using Sharepoint lists (MOSS 2007) as tables for an Access application.
This appears to be working relatively well, except for some work-arounds.

One such work-around is that when you have a form that is bound to an Access
table, the Autonumber field for a new record is filled in as soon as you make
an entry in any of the other fields.

This does not appear to be the case when an Access form is bound to a
Sharepoint list. In this case, the ID field is not filled in until after the
record is saved. This forces the developer to commit the record early, then
proceed with editing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Larry Linson]

Dale, This is not unique to SharePoint; it generally true when working with
Access as a client to some server datastore (which may be a database)... the
server (and that is where the server-equivalent-of-autonumber is created) is
not updated until your record is saved from the client.

Larry Linson
Microsoft Office Access MVP

 

[Dale Fye]

Thanks, Larry. I'll keep that in mind.

The only other Server database I've used is "SQL Server" and I don't
remember this being an issues, but I also don't recall trying to reference
the Autonumber field before saving the record either. In this particular
case, I was trying to pass the ID to a function, but that wasn't working
because the ID was still null. I managed to work around the problem, but
not without getting a couple of headaches first.

Dale

 

[Albert D. Kallal]

Thanks, Larry. I'll keep that in mind.

The only other Server database I've used is "SQL Server" and I don't
remember this being an issues, but I also don't recall trying to reference
the Autonumber field before saving the record either. \

It is the same issue for sql server. You don't get an autonumber until the
record is saved.

for a form, you go:

if isnull(me.ID) = true then
if me.dirty = true then
me.dirty = false
end if
end if

' get id here now is ok....


For recordset code you must go:

old (non sql or non sharepoint)
Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew

' code can go here to add data,
' or set values to the reocord...or, you
' could grab/use the autonumber at this point
' in time.


If you using sql server (or sharepoint) you have to save the record first

eg:

Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew
' cod here to modify/add data to the reocrd....

rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext


So, you force a save to disk and then use the LastModified bookmark
propeirty to move to that last reocrd and then grab the id.

 

[Larry Linson]

The DBA for a lot of Informix database applications on which I worked wrote
a Stored Procedure to retrieve a monotonically increasing "unique id" field
to use on new records in each table in the database. If I recall, it was
named, with great originality, "NextID", and called with a passthrough
query. It worked so well, for so long, that when he created other server
databases, he just dropped it in at the beginning and we used it quite
successfully there, as well (Sybase SQL Anywhere and MS SQL Server were two
of those).

That solved also the problem of "disappearing new records" that can happen
when you are creating a new record, save it, and it disappears, because the
server database doesn't _return_ the new unique id it creates back to you.
With this scheme, you just aren't using an Autonumber-equivalent or SEQUENCE
field to cause that behavior... you are always writing a record with a key.

Larry Linson
Microsoft Office Access MVP