I have used Access frontend with SqlServer backend combination for a
number of years. In the easy 'oldfashioned' way using DAO and odbc
linked tables. Works fine within limits.
Now I'm facing the situation where there will be a 3 base tables that
will be used constantly:
- one of the tables will grow rapidly and can become hugh (think of
'hundredthousands').
- one of the tables is a sort of 'rights table' in wich a username
determines what records from the big tabel may be selected by the
current user.
To prevent performance issues I created a query in the backend that
combines the 3 tables and uses 'system_user' to filter the records at
the server so they will never be send back to the Access frontend.
Sounds like a workable solution to me but I have a few questions
because I have a problem with the username part.
1) Combining the 3 most used tables at the server is the best way to
it? Read about the way Access can send a local query and Sql then
makes a temporary serverquery wich would produce more or less the same
effect.
2) By using integrated security I can use the username in the
Sqlserver query. The alternative is not using int.security and then
sending the username with each query to the server. Will this give the
same performance result? Would think so but I am not sure. (If so, I
tend to do it because I still have problems using int.security and the
problem of preventing users to get to the data via another way than
via the app).
Btw: I know a lot of people think Access front/Dao is not the golden
way to go, but I still love the way one can do things with it when the
needs in an app grow/change a lot and as far as I'm concerned apps
made with it work fine.
