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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • adjo
    #1

    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
    'hundredthousan ds').
    - 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.

    Tags: None
    • lyle fairfield
      #2
      Re: Advice needed on Access front and lots of recs in Sql back

      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.

        Comment

        • Larry Linson
          #3
          Re: Advice needed on Access front and lots of recs in Sql back

          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.kempf@gm ail.comwrote in
          message
          news:691bc667-80bf-43ae-a4af-8eec6b27367e@p2 5g2000pri.googl egroups.com...
          yes, do all the work you can on SQL Server.
          Join on SQL Server. Queries on SQL Server.
          <BIG SNIP OF RANT>


            Comment

            • adjo
              #4
              Re: Advice needed on Access front and lots of recs in Sql back

              On 30 jun, 06:47, "Larry Linson" <boun...@localh ost.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...@gm ail.comwrote in
              messagenews:691 bc667-80bf-43ae-a4af-8eec6b27367e@p2 5g2000pri.googl egroups.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....

                Comment

                Previous template Next
                Working...