468,145 Members | 1,386 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,145 developers. It's quick & easy.

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

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.

Jun 28 '08 #1
3 1187
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.

On Jun 28, 7:24*am, adjo <adgn...@gmail.comwrote:
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.
Jun 28 '08 #2
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

"a a r o n . k e m p f @ g m a i l . c o m" <aa*********@gmail.comwrote in
message
news:69**********************************@p25g2000 pri.googlegroups.com...
yes, do all the work you can on SQL Server.
Join on SQL Server. Queries on SQL Server.
<BIG SNIP OF RANT>
Jun 30 '08 #3
On 30 jun, 06:47, "Larry Linson" <boun...@localhost.notwrote:
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

"a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.comwrote in
messagenews:69**********************************@p 25g2000pri.googlegroups.com...
*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....
Jun 30 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

75 posts views Thread by Howard Nease | last post: by
3 posts views Thread by dlesandrini | last post: by
30 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.