Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Location
    Copenhagen, Denmark
    Posts
    2

    Unhappy Unanswered: IDENTITY column in SQL 2000 and linked tables in MS Access

    Please help

    We have an application written in MS Access. The tables are linked to a SQL 2000 database.
    The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
    When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
    The application is used by many in the company.
    We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?

    Regards Anna-Lena

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It all depends

    How is the application written...

    Do all of your controls have a data source property that is a table in SQL Server?

    Or do you use unbound controls...


    You might be better off posting here though

    http://www.dbforums.com/forumdisplay.php?f=84

    I doubt it's a problem with IDENTITY though
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is that Access does not work will with large numbers of users. Anything above six to ten simultaneous users can cause problems.
    I assume you have a form linked to a table or a view in your SQL Server database. Well, Access likes to copy down the ENTIRE recordset so that you can step through the results. When you have several people who each have loaded their own local copies of the same recordset and then try inserting a new record, I'd guess it plays havoc with the locking.
    You might try having each user download a filtered subset of the data, reducing the number of copies of the same record that are spread over multiple terminals.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The only way this works is to have all unbound controls, using rs.whatever and write code to fill in the controls, and to perform dml back to the database
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Which, in my opinion, takes away most of the advantages of using MS Access. So the application may as well be written on a more robust platform.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Which, in my opinion, takes away most of the advantages of using MS Access.
    Apart from it being a one stop shop GUI\ report generator that can be distributed with xcopy you mean? A disconnected Access app is by no means the best solution in many situations but I would use one over a linked Access version any day**. And I would use Access over (for example) .NET in many circumstances too. In fact I do.

    EDIT - ** Actually I got over excited and fibbed there. A disconnected app might be overdesigned for many applications (e.g. those that are accessed by a very small number of people).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I use Access for rapid application development of apps with low user counts, and for that ADP files work fine. I never use linked tables, though.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •