Acess & SQL Server Express

[Guest]

Albert D.Kallal had provided some excellent response to my question on
migrating from my access 2000 to the SQL SERVER EXPRESS but i needed to know
if this migration would work with a FE/BE design and what positive or
negative changes would occur.Does anyone have any thoughts or experience on
this?

 

[Albert D.Kallal]

Here is a few tips:

Some code to use dsn less connections (new re-link code).

http://members.rogers.com/douglas.j.steele/DSNLessLinks.html

make sure that you add a timestamp field to the sql server tables, and this
stamp is exposed to ms-access
(ms-access with linked odbc tables (your dao or ado) uses the timestamp to
help figure what the heck needs to be updated)

Make sure all tables have a primary key...even if they don't (yet) need one.


For example, in dao to a ms-access table (jet), you can get the current
autonumber
id as follows:
:


dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourt­able")


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, and THEN get the
autonumber.

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


So, *most* of your dao code will work, but as above, some things do need to
be changed.


You should spend some time learning sql server, and even consider hanging
around the sql newsgroups.

Here is a few more articles:

Null bits - watch for them!

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table

http://support.microsoft.com/?id=280730

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321