SQL Server as Back End...Hard??

[SteelersFan]

If you have a split database already, is it hard to migrate the back end to SQL Server? I work in the pharmaceutical industry and the database is validated so I want to see how much qualification/testing I have to do for this.

Will the database be noticibly quicker? Everyone accesses the same front end so I'm not sure that this won't still be the bottleneck.

Dave

 

[Pace]

Hey im doing this for my company

Yes it will be quicker "out the box" but the real way to make it quicker is good database design.

Do you know how big the database is ?

You could split it up into multiple .mdb's to start with to make it quicker.

Its not hard to get the data onto SQL Server, infact its a breeze, but getting access to talk to the tables correctly requires a little more patience and a heck of a lot of code reading

hth

Pace

 

[Pat Hartman]

Yes it will be quicker "out the box"

- not necessarily. Most Access applications are designed specificaly for Access (Jet) tables. To get any effective speed out of a SQL server back end, your forms/reports MUST be based on queries with selection criteria. Basing them on tables or queries with no criteria or with filters will cause the entire table to be brought over the LAN each time you need to work with it.

 

[Pace]

- not necessarily. Most Access applications are designed specificaly for Access (Jet) tables. To get any effective speed out of a SQL server back end, your forms/reports MUST be based on queries with selection criteria. Basing them on tables or queries with no criteria or with filters will cause the entire table to be brought over the LAN each time you need to work with it.

Yes I agree with what you say but I have found that our app (approx 140+ tables) runs a hell of a lot quicker running off SQL Server than Access. Thats even with DAO

Cant wait to convert to ADO