Post reply

Performance issues with MS Access 97.

  • February 26, 2004 at 6:35 am

    #65947

    Currently, I am upgrading my Access application's back-end to SQL Server 2000.  And I am linking the SQL tables back to the Access fornt-end.  I was wondering what the performance issues are, with this kind of setup.

    Also, I am using VBA code to open several recordsets (Access Front end).  I am using the DBSeeChanges option, when I open recordsets.  In one particular function, I am referencing one recordset several times.  Making updates to the this recordset, using 'Do Loops' with other recordsets.  The first piece of the function is to remove all of the records from this recordset.  Next add new records to the recordset. 

    There are 6 other steps to this function that run through 'Do Loops'.  The first 'Do Loop' works fine and makes all the updates, but everything after that comes up with the following message:

    "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."

    I'm just starting to use SQL and would appreciate any help.

    Thanks....

  • February 26, 2004 at 4:38 pm

    #496048

    try to move as much as possible to the server side and encapsulate the logic in stored procedures.


    * Noel

  • February 27, 2004 at 3:13 am

    #496128

    There are 6 other steps to this function that run through 'Do Loops'.  The first 'Do Loop' works fine and makes all the updates, but everything after that comes up with the following message:

    "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."

    It's just as the error message says. When using the OpenRecordset method you MUST specify the dbSeeChanges parameter and you'll be fine.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 27, 2004 at 3:27 am

    #496129

    Oh, and I forgot. Always, always make sure you are on Access 97 SR2. Prior to that there was a memory allocation bug, which sooner or later let you run out of memory.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 29, 2004 at 2:21 am

    #496362

    the best solution is to make a Stored procedurte on SQL Server ... then call it in Access by making a command and a Connection

    making a Stored procedurte on SQL Server with Transacions is very easy ..

    also you can make a pass-through query in access which makes you work directly on SQL Server

    I hopr this help you.


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply