September 24, 2008 at 10:18 am
Hi All,
I have a SME (subject matter expert) that need to modified data in SQL Server 2005 by looking at row by row (there is no other way to do it). They have no knowledge of SQL so I use link tables from MS Access to SQL Server.
The problem is: it is very slow and if I run anything else on the server. The Access app would time out. I have index the link tables and don't know what else to do? Anyone have any suggestion?
Thanks.
September 24, 2008 at 10:40 am
Unfortunately when you are connecting access to SQL server via Linked tables, every time you query a table with a join access will pull the entire thing down to the local host before it joins them. You could use pass through queries instead to help with performance.
Is this a production database that's already in use, or one that you are preparing for use? I.E can the records be exported to excel or somethign and then the SME could modify them and you could upload them back into the db via SSIS?
September 24, 2008 at 1:06 pm
Have you tried setting it up as an Access project instead of an Access application?
A project connects directly to the database, no linked tables, and can use stored procedures and such in the database to pull the data into its forms and reports.
I've found using such to be quite efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 25, 2008 at 8:09 am
Luke,
That would be good if I have small amount of data. Unfortunately, there is more than 1500 tables and the user need to be able to search for the records and I'm not that good with Excel.
GSquared,
I'm going to try Access Project.
Thanks All.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply