Advice needed on Access front and lots of recs in Sql back

[adjo]

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.

 

[lyle fairfield]

I'm with you, Brother. I know nothing about cars and have never
investigated anything else but I love my Lada. It takes me back and
forth from the trailer park to the dump every day just fine and since
it's so ugly and dirty nobody complains about my throwing the junk
into it. Amen!

And it's so light that Betsy and Moolster don't mind hauling it so
much, either.

 

[a a r o n . k e m p f]

yes, do all the work you can on SQL Server. Join on SQL Server.
Queries on SQL Server.
Access is nothing but a piece of shit database and you're stuck in the
90s.

And DAO?

_ANY_ library that is SOOOOOOOOOOOOOOOOO buggy that you've got to
explicitly clean up variables?
NOT WORTH USING

Move to ADP, kid

-Aaron

 

[Larry Linson]

FYI, Mr. Kempf trolls a number of Access newsgroups, claiming long
experience in Access, but apparently not understanding that Access is very
commonly used as a front-end to SQL Server, and he is certainly not
up-to-date with Microsoft's recommendations for using it in that mode, which
are that the method of choice is _not_ Mr. Kempf's favorite, the ADP.

His recommendations typically are, as this one, not applicable to users not
blessed with MS SQL Server on a LAN, a company-provided DBA, and in-depth
training in MS SQL Server T-SQL, VB.NET or C#, and assorted other aspects of
MS SQL Server. Generally, they qualify only as rants, and can safely be
ignored.

Larry Linson
Microsoft Office Access MVP

message

yes, do all the work you can on SQL Server.
Join on SQL Server. Queries on SQL Server.

<BIG SNIP OF RANT>

 

[adjo]

FYI, Mr. Kempf trolls a number of Access newsgroups, claiming long
experience in Access, but apparently not understanding that Access is very
commonly used as a front-end to SQL Server, and he is certainly not
up-to-date with Microsoft's recommendations for using it in that mode, which
are that the method of choice is _not_ Mr. Kempf's favorite, the ADP.

His recommendations typically are, as this one, not applicable to users not
blessed with MS SQL Server on a LAN, a company-provided DBA, and in-depth
training in MS SQL Server T-SQL, VB.NET or C#, and assorted other aspects of
MS SQL Server.  Generally, they qualify only as rants, and can safely be
ignored.

 Larry Linson
   Microsoft Office Access MVP

message > yes, do all the work you can on SQL Server.
 > Join on SQL Server. Queries on SQL Server.
<BIG SNIP OF RANT>

Thx. Looks like the word 'MsAccess' still trigger some people to
convince the world how bad this product is. Not correct in my opinion
but continue disussion looks like a way of wasting energy....