Use ADO, Is it easy upgrade from Jet -> SQL 2K ?

[S.L.]

If I use ADO with OLE DB Provider for Microsoft Jet 4.0. My code use only
basic function (ADO library only, no ADOX,ADO MD, JRO) . In the future if I
change from Jet to SQL Server 2000, Will I have to modify my code a lot ?
(Suppose my code not use stored-procedure.)

TIA

 

[Sal]

the only thing you will have to change is your connection
object to point to SQL Server.
After you do the upgrade do not use
currentproject.connection
because this will slow down SQL server performance by
putting Jet in between SQL Server and the client.

Sal

 

[S.L.]

Execuse me, Could you explain more Why Jet is between SQL Server and client
?

What I understand now, the model should be like this :
MS Access > ADO > OLE DB Provider for Microsoft Jet 4.0 > Jet database.

And if I change from Jet database to SQL Server database, Model is changed
to :
MS Access > ADO > OLE DB Provider for SQL Server > SQL Server database

Is this correct or not ?

Suntisuk

 

[Mary Chipman]

Your ADO-specific code doesn't have to change, but anything that
directly references the database engine will. For example, date
delimiters are different in Jet and SQL Server, as well as other SQL
syntax. So it really depends on how Jet-specific your application code
is now. FWIW, you'd probably be better off converting as much of your
ADO data access code to use stored procedures once you migrate to SQL
Server. There will be less of it in ADO, and you'll reap the benefits
of having it execute on the server instead of on the client.

-- Mary
MCW Technologies
http://www.mcwtech.com

 

[S.L.]

Thanks so much for your both reply.
Suntisuk

Your ADO-specific code doesn't have to change, but anything that
directly references the database engine will. For example, date
delimiters are different in Jet and SQL Server, as well as other SQL
syntax. So it really depends on how Jet-specific your application code
is now. FWIW, you'd probably be better off converting as much of your
ADO data access code to use stored procedures once you migrate to SQL
Server. There will be less of it in ADO, and you'll reap the benefits
of having it execute on the server instead of on the client.

-- Mary
MCW Technologies
http://www.mcwtech.com