Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access and SQL Server

Discussion in 'Information Technology' started by Scott Lichtenberg, Jul 28, 2006.

Tags:
  1. Scott Lichtenberg

    Scott Lichtenberg
    Expand Collapse
    Guest

    I just read Arvin Meyer's and Pat Hartman's replies on the ADP Strengths
    thread which discussed using MDBs instead of ADP for SQL projects. Taking
    that as a starting point, I have some questions regarding the best
    methodology to use when creating a client server application.


    I currently have an SQL back end, with Access97 front ends. We've been
    using this (quite happily) for about 10 years. We've just upgraded SQL from
    6.5 to 2005, so we are looking to bring Access into the new century as well.



    One of the issues we ran into when we developed our apps was that we had
    terrible locking problems when we linked SQL tables into Access. If we used
    a linked table as a recordsource for forms, combo boxes, etc., Access would
    read about 100 records, and then hold the table open (with locks) until the
    user moved to the end of the recordset. To get around this, we developed a
    methodology where we created a copy of the SQL table in Access, then copied
    records into it. We used this table for the form's recordsource. When a
    record was edited/added, we pushed back the change to the SQL server. Kind
    of a Flintstones' version of ADO disconnected recordsets.



    Arvin and Pat have settled the issue of ADP vs MDB, but it leaves me with a
    couple of questions, the important of which is:



    Should I develop forms which use the linked tables and queries as row
    sources? If I do this, am I going to run into the same locking issues as I
    had in A97? I've noticed that if you open a pass through query against a
    table with 10,000 records, Access 2003 does not pull in all the day. There
    will be about 100 users for my system, so locking is a major concern.





    Thanks in advance for any help.

    Scott Lichtenberg
     
  2. Loading...


  3. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    I'm not sure what locking options you were using, but have never had a
    similar problem using Access as a client to various server databases. I'd
    certainly not recommend using ADP, as the knowledgeable people on the Access
    development team now (again) suggest that MDB-Jet-ODBC-server is better.

    If you do wish to use ADP, I believe you'll have to wait for the release of
    Access 2007. I haven't used Access-ODBC-MSSQLServer2005, so can't comment
    on that, but ODBC solves most "version" issues.

    Larry Linson
    Microsoft Access MVP


    "Scott Lichtenberg" <xyz@notanemail.com> wrote in message
    news:OguXU06kGHA.1320@TK2MSFTNGP04.phx.gbl...
    >I just read Arvin Meyer's and Pat Hartman's replies on the ADP Strengths
    >thread which discussed using MDBs instead of ADP for SQL projects. Taking
    >that as a starting point, I have some questions regarding the best
    >methodology to use when creating a client server application.
    >
    >
    > I currently have an SQL back end, with Access97 front ends. We've been
    > using this (quite happily) for about 10 years. We've just upgraded SQL
    > from 6.5 to 2005, so we are looking to bring Access into the new century
    > as well.
    >
    >
    >
    > One of the issues we ran into when we developed our apps was that we had
    > terrible locking problems when we linked SQL tables into Access. If we
    > used a linked table as a recordsource for forms, combo boxes, etc., Access
    > would read about 100 records, and then hold the table open (with locks)
    > until the user moved to the end of the recordset. To get around this, we
    > developed a methodology where we created a copy of the SQL table in
    > Access, then copied records into it. We used this table for the form's
    > recordsource. When a record was edited/added, we pushed back the change
    > to the SQL server. Kind of a Flintstones' version of ADO disconnected
    > recordsets.
    >
    >
    >
    > Arvin and Pat have settled the issue of ADP vs MDB, but it leaves me with
    > a couple of questions, the important of which is:
    >
    >
    >
    > Should I develop forms which use the linked tables and queries as row
    > sources? If I do this, am I going to run into the same locking issues as
    > I had in A97? I've noticed that if you open a pass through query against
    > a table with 10,000 records, Access 2003 does not pull in all the day.
    > There will be about 100 users for my system, so locking is a major
    > concern.
    >
    >
    >
    >
    >
    > Thanks in advance for any help.
    >
    > Scott Lichtenberg
    >
    >
    >
    >
     

Share This Page