Working with SQL

[dw134]

Well I am not sure which forum is best to post this in, if I have it wrong please move this post.

For years I have been working with a hybrid of Access, an online data base and csv imports. Two years ago I embarked on a journey to build a SQL backend and to get this accessible by clients in real time as we move about their sites doing our work. This is partly complete...however I am now at the point where I can see an end to the tunnel so to speak. To take the next step is to move away from the old online database and csv imports however to do this a lot of things change at once. I am telling this for context and background.

To make this move I am having to rebuild the office end of the chain first. This consists of an Access front end with a smattering of local tables to link to the SQL server online.

I already am aware of limiting fields and restricting records to reduce server traffic. However I was wondering what others approach may be to queries and loading subforms (which seems to take forever) when working in this manner?

 

[kray4660]

We have been using various Access databases with a MS-SQL back end for a number of years. If the indexes on the tables are set up correctly, we normally do not have any issues. Not sure why you need local tables to link to the SQL server, but then again I am not a full time Access DB developer. Fortunately we have a very robust network at our organization, so network slowness normally is not an issue unless we use the wireless (and a lot of users are on the wireless also).

 

[BrooksR]

I already am aware of limiting fields and restricting records to reduce server traffic. However I was wondering what others approach may be to queries and loading subforms (which seems to take forever) when working in this manner?"

You definitely want the fields linking a form and subform to be indexed fields within the tables.

Also, if you are running queries against the SQL Server db, you want all the fields in the JOIN, WHERE and ORDER BY clauses to be indexed fields.

Brooks