473,236 Members | 1,318 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,236 software developers and data experts.

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 1437
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

75
by: Howard Nease | last post by:
Hello, everyone. I would appreciate any advice that someone could give me on my future career path. Here is my situation: I am a bright Junior in a very well-respected private high school, taking...
6
by: Terry Bell | last post by:
We've had a very large A97 app running fine for the last seven years. I've just converted to SQL Server backend, which is being tested, but meanwhile the JET based version, running under terminal...
3
by: dlesandrini | last post by:
I need advice about my decision to go with Replication in general. This post was placed on the Microsoft Replication newsgroup, but I really value the feedback that comes from this group as well. ...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
1
by: David Van D | last post by:
Hi there, A few weeks until I begin my journey towards a degree in Computer Science at Canterbury University in New Zealand, Anyway the course tutors are going to be teaching us JAVA wth bluej...
5
by: B1ackwater | last post by:
We've fooled around with Access a bit, but only using the single-user store-bought version. It seems to be a good database - versatile and infinitely programmable - and can apparently be used as a...
23
by: JohnH | last post by:
I'm just recently come to work for an auto brokerage firm. My position involves performing mysterious rites, rituals and magick in order to get information out of their access database. This is...
64
by: John | last post by:
Hello there, Im cursing my place of employment...and its taken me a month to realise it... The scenario: Ive just stepped into a role to migrate an access database to VB.Net. The access...
10
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Thank you for that. It was very...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.