SQL back end with DAO in the front end

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have an access database where most of the code is using
DAO, Now I want to change the BE to SQL so my question is
if I will have to change each DAO entry to ADO or not. ?

Thanks in advance

Scott
 
If you continue to use linked tables, the dao stuff should work most of the
time.

There are a few things to watch for. For example, when you add a new record
in DAO, then the key id (primary key) is availing right away, where as in
sql server..you have to write the record first.
 
Thank you for your reply, so that means that I can not
refer to the key id field before I have updated the
recordset using the RecordsetName.Update method. ?

Scott
 
Yes.

So the old code might be like:

dim rstRec as DAO.RecordSet
dim lngNewID as long

set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID <----grab id of reocrd
rstRec.Update

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 <--- grad id or record AFTER update
 
Also, you need to add the DAO.dbSeeChanges option to every
OpenRecordset or Execute that reads data from a Server table.

And you will need to rewrite any complex transactions (if you
are using BeginTrans/CommitTrans)

There are other possible differences, but mostly you will get
99.9% compatibility.

(david)
 
Back
Top