Converting Access to SQL Server question..

[BuilderSpec]

Hi

I currently run an Access 2002 front end db connected to an Access 2002 back end. I am thinking of changing the back end to be a SQL Server database.

Apart from re-linking the tables in the front end to the database on the SQL Server will everything else work as is?

I have forms that do updates and code that does it that i have written using ADODB Recordsets , will these all still work or will they need changing ?

Regards

BuilderSpec

 

[NoCoolHandle]

I can't speak definitivly for Access 2002, but linked tables tend to SUCK when it comes to performace. (at least in all the versions I have ever worked with).

They tend to cause alot of datacacheing.. (All of it, all the time, and that can cause it to be SLOW for everyone as they will all be cacheing it alll the time.......(with periodic refreshes ..)

I would tend to advise you to re write the entire access app to use ado and recordsets.. Also stored procs for all datamods and inserts (not to mentions a large percentage of your querys...)

My 3c

Rob

 

[mrdenny]

Will it work, probably. Every table will need to have a primiary key when you move over to the SQL Server.

Will it work well, probably not (for the reasons that NoCoolHandle mentioned).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

AS Denny said, you will need a unique Id on every table. Then every thing should work if the structure is the same. (Check the data types when you convert though because the wizard doesn;t always make the best choice.)

Then test. If performance is acceptable, you are home free. If not then you need to start with the slowest qqueries and make then Stored procedues and call them through passthroughqueries or rewrite for ADO access.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[BuilderSpec]

Thank you all for your comments..

How much faster will the front end be if i re-wrote all the linked table access and queries in ADO ? Seems a mammoth task for me ( it's a huge database 200+ queries ) .. the client is already complaining it is slow which is why i want to go to SQL in first place.

Regards

BuilderSpec

 

[mrdenny]

It could end up being quite a bit faster. Access likes to pull down a lot of data when it opens a table or linked table. When using ADO you'll only be pulling down the record or records you need.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Burglar]

You may even want to consider using adp to connect to the sql server...speeds things up dramatically!

 

[BuilderSpec]

Thanks..initial runs of the wizard to create a SQL Linked database worked. I tried making an ADP but it crashes half way through...