Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-30 : 09:28:31
|
| Hitaishi writes "We have recently migrated from MSAccess as our back end system to SQL Express, but the dialogs showing records of the table which were taking lesser time in Access are taking more time in opening and doing any database operations.......We are using .Net Framework 2.0. Does it have anything to do with lowering down the performance ?Any help would be appreciated." |
|
|
mikewa
Microsoft SQL Server Product Team
84 Posts |
Posted - 2006-06-24 : 12:38:32
|
| SQL is a very different database than Jet, which is the database base used by default in Access. As such, it is almost certain that you will need to modify you're Access application in some way in order to take advantage of SQL Server. Migrating from Jet to SQL correctly is an involved process about which there are numerous resources, and a discussion of all the different things to consider is byond what can be discussed in a forum posting. If you're new to this, I would suggest you consider viewing my webcast discussing Access to SQL Migration (http://www.microsoft.com/events/EventDetails.aspx?CMTYSvcSource=MSCOMMedia&Params=%7eCMTYDataSvcParams%5e%7earg+Name%3d%22ID%22+Value%3d%221032294933%22%2f%5e%7earg+Name%3d%22ProviderID%22+Value%3d%22A6B43178-497C-4225-BA42-DF595171F04C%22%2f%5e%7earg+Name%3d%22lang%22+Value%3d%22en%22%2f%5e%7earg+Name%3d%22cr%22+Value%3d%22US%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParams%5e) which covers the basics at a high level, and gives you pointers to other resources.As far as your specific issue, I would start with the basics:- Verify you have the appropriate indexes on the table.- Only return the data you actually need, rather than the whole table.- If you're using a query behind your form that includes VBA functions, re-write that query as a SQL view and then link to the view. The VBA function may be forcing Access to download the whole table rather than just the data you want.- Replace queries behind combo boxes with pass through queries, which are faster (I'm assuming your using linked tables)All these will help reduce the number of times you have to hit the server and the amount of data that needs to be transfered.Regards,Mike WachalSQL ExpressThis posting is provided "AS IS" with no warranties, and confers no rights.Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm |
 |
|
|
|
|
|
|
|
Migrating from MSAccess to Sql Express