Need to connect to local data files

[Titan5]

Hi, All:
MS Access ADP as a front end will access data in SQL Server (presumably a different computer in the network). I have several big data files (fixed, read-only, lookup tables, not to be edited) such as comprehensive tax table, medical dictionary codes, etc. For speed I feel I should store these files in the front end client computer to avoid network traffic. How should I do it? Put the data in mdb file? How do I access them thru ADP? Thanks in advance.

 

[cmmrfrds]

I can't think of a scenario where it would be better to store the tables local, considering the hassle of maintaining the extra data source. Can you elaborate on why it is necessary to store the data local. I believe you would need to use the same techniques to pare the data to manageable lists whether local or remote.

 

[BobJacksonNCI]

Keep in mind that the Access Project design is to store an .ADP file for each user. You don't have to, but if you don't, every time two or more users access the .ADP, they will have to answer a read-only message. It refers to the front end, not the tables on the SQL server. So, if you were able to store tables in the front end DB, by design you'd have a copy of those tables for every user.

Also, the SQL server will be hundreds of times faster than the Access engine. And, as you learn about Views and Stored Procedures, you'll find that only the data that is needed is sent over the network.

There are other threads regarding the use of Access Project with local tables and I don't recall anyone who wanted to do that having success at it.

If you're sure you want local tables and server tables you can do that by linking tables within your Access database instead of using Access Project. That implies Access Jet engines both places and most folks here will tell you that performance will suffer when a fairly small number of users access the database simultaneously. Ten seems to be about the number. And once the primary tables exceed some arbitrary size, again, performance will suffer. Straight SQL and Access Project support hundreds of times the number of users and amount of data as plain Access.
Consider future growth carefully before choosing Access with linked tables.

Good luck!
Bob

 

[Titan5]

Thank you all for your input. The read-only data files have about 50 MB or so each. They are served almost like a dictionary. I would think if each desktop has a dictionary it would be easier for everybody. But from what you people told me it is still faster if the data files are in the remote SQL server. It looks like there is no need for the local approach I thought of. Thanks all.