Desperate with a problem (SQL Server linked tables)

[Tony Gilbert]

Hi,

I have 2 problems with the SQL Server tables that are linked to my
front-end:

* Before update code cannot catch the ID of the new record:
With an Access table, you can ask for the autonumber of the record to
be saved, so you can use that to add details to another table, for
instance. I don't get the ID when linking to sql server, and catching
it after the record is saved creates all kinds of problems (some forms
automatically go to a new record when a record is saved, some forms
get real ugly when I can't use before update..). Is there any solution
to this??
* When I use DAO to add a record to a table (AddNew & Update), I don't
get the ID of the new record:
Similar. Before the Update method, the recordset won't show the
autonumber. After the Update method, the record isn't the current
record anymore.. I need the id to verify data in other tables... I
just can't come up with a simple solution...

Thanx a million for your ideas!

 

[Chadlon]

Hi Tony,

The 'LastModified' property of the recordset should get you to the required
record.

CD

 

[Albert D. Kallal]

Even with DAO, you have to first force the update to occur first, and then
grab the ID.

Thus, you old code of:



dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server, you have to force the update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID

Not much of change,and in fact a few regulars have always coded things the
above way, since many did not realize that you can grab the id right after a
addnew. With sql server, you have to execute the update to get the
identify/autonumber field.

Anyway, the above code snip works for either JET, or sql server...so it is a
good coding standard to adopt. (and some have done the above even BEFORE
they ever used a server anyway).

 

[Tony Gilbert]

Thanks for your help guys, but I tried it and it isn't working....

So I don't have to write too much code throughout the forms, there is
a function returning a dynaset recordset object

Set rst = gfGetSQLrst("SELECT * FROM tbl_PlanningShipper;")

gfGetSQLrst basically does:
Set dbs = DBEngine(0).OpenDatabase("MySQLdb", dbDriverNoPrompt, False,
gstrMyODBCString)
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

I tried using the LastUpdated on a table tbl_PlanningShipper:
rst.AddNew
rst("PlanningDate") = dtmDate
rst("PlanningShipper") = lngShipperID
rst.Update
rst.Bookmark = rst.LastUpdated
lngPlanningID= rst("PlanningID")
It always returns the FIRST PlanningID instead of the newly added
record.

Second try:
Set rst = gfGetSQLrst("tbl_Transports")
rst.AddNew
'...
rst.Update
rst.Bookmark = rst.LastUpdated
lngTransportID = rst("TransportID")
Here, the code doesn't work either... it won't bookmark a thing..

Am I missing something?

 

[Albert D. Kallal]

SORRY!

That is what happens when you type code from memory.

You need to use the LastModified!!!!

(last updated is the time/date the table was last updated!)

rstRecords.Bookmark = rstRecords.LastModified