Updating large recordsets takes long time in ms access database

Any suggestions on things to look into to speed this up, and make it happen more reliably on the first try?This is a multi-user file and I was not wanting to make any changes to the registry since that would require making that update for everyone in my department.ADO is the default object library for Access, but DAO is still available.If you use a DAO Recordset, you must explicitly reference it.This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.Summary: This article outlines specific performance tips that you can use to improve the speed of your Microsoft Office Access 2007 applications.

There is no specific activity on the server, there is a trigger associated with one of the columns which I am trying to update. The data will be moved to from TB1 --Tb4 DB2 And all the data from Tb1 & TB2 will be deleted after insert. STAT in ('C','X') -- Delete all the closed or disabled lead sheets and leads after archiving DELETE [DB1].[Schema1].[Table2] WHERE LID IN (SELECT LID FROM inserted WHERE STAT in ('C','X')) DELETE [DB1].[Schema1].[Table1] WHERE LID IN (SELECT LID FROM inserted WHERE STATUS in ('C','X')) END An overview of the trigger - There are two databases DB1-Transactional and DB2-Archive. Table definition - SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [Schema1].[Table1]( [ROWID] [bigint] IDENTITY(1,1) NOT NULL, [LID] [varchar](100) NOT NULL, [NAME] [varchar](100) NULL, [TOTALCOUNT] [int] NULL, [PID] [int] NULL, [CID] [int] NULL, [OID] [int] NULL, [ADATE] [datetime] NULL, 03-мая-2019 20:04 [datetime] NULL, [CDATE] [date] NULL, [STAT] [varchar](10) NULL, [NOTE] [nvarchar](max) NULL, [REACH] [varchar](4) NULL, [SA] [varchar](4) NULL, [RED] [char](1) NULL, [DK] [varchar](4) NULL, 05Fri753 [datetime] NULL, [MODDATE] [datetime] NULL, [TID] [varchar](100) NULL, CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED ( [ROWID] ASC, [LID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [Schema1].[Table1] ADD CONSTRAINT [DF_Table1_STAT] DEFAULT ('A') FOR [STAT]GOALTER TABLE [Schema1].[Table1] ADD CONSTRAINT [DF_Table1_DATEINDB] DEFAULT (getdate()) FOR 05Fri753GOTrigger Statement - SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [Schema1].[Table1_STAT]ON [Schema1].[Table1]FOR UPDATE , INSERT , DELETEAS BEGIN IF NOT UPDATE(MODDATE) UPDATE [Schema1].[Table1]SET MODDATE=GETDATE() WHERE LID IN (SELECT LID FROM inserted WHERE STATUS in ('C','X')) ---Insert the C or X lead sheet to arch INSERT INTO [DB2].[dbo].[Arch Table2] ([ROWID] ,[LID],[NAME],[TOTALCOUNT],[PID],[CID],[OID],[ADATE],03-мая-2019 20:04 [CDATE],[STAT] ,[NOTE] ,[REACH] ,[SA] ,[RED], [DK] , 05Fri753,[MODDATE],[TID]) SELECT [ROWID] ,[LID],[NAME],[TOTALCOUNT],[PID],[CID],[OID],[ADATE],03-мая-2019 20:04 [CDATE],[STAT] ,[NOTE] ,[REACH] ,[SA] ,[RED], [DK] , 05Fri753,[MODDATE],[TID] FROM inserted WHERE LID IN (SELECT LID FROM inserted WHERE STAT in ('C','X')) -- Insert the closed or disabled lead sheet associated leads to arch INSERT INTO [DB2].[dbo].[ARCHTable3] (80 Columns) SELECT 80 columns FROM [DB1].[Schema1].[Table2] A INNER JOIN inserted I ON A. Question, can data being INSERTed be immediately archived?

I suspect the size of the tables involved is causing the issue.

Please let me know your thoughts, Thank you for all your responses.

Most of the data is populated through SQL queries run through VBA.

When I first connect to the back end data, it takes a long time and the back end file (.accdc file) locks and unlocks 3 or 4 times.

As an Access developer — or even as a power user — you won't get far if you can't work with the Recordset object.

You must have an account to comment. Please register or login here!