ACCESS FEED: Februari 2022

Selasa, 22 Februari 2022

Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Jim – Are you not making daily (or at least, weekly) backups of your data. If you have backups you can compare tables to find differences in records.

Regards,

Bill Mosca

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Jim Wagner
Sent: Tuesday, February 22, 2022 12:29 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Michael

I have spent 2 days on this. I just compacted and repair and it I do not see the error anymore. But I do not know if any records are missing.

Thank You

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Cheeseburger <Michael.P.Devlin@Hofstra.edu>
Sent: Tuesday, February 22, 2022 7:56 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

If you try opening the tables that are involved, do any of them open with all the records as #DELETED?
We have encountered an issue with one of the updates that caused this with random users. A note from our ITS team:
Remoted to user pc, uninstall and reinstall office 365 version 21111-build 701-20262 and it worked fine after. Stopped the update from updating.

Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Michael

I have spent 2 days on this. I just compacted and repair and it I do not see the error anymore. But I do not know if any records are missing.

Thank You

Jim Wagner

Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Senin, 21 Februari 2022

Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Bill

I still get the same error message.

Jim Wagner

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Mosca <wrmosca@myself.com>
Sent: Monday, February 21, 2022 11:34 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Jim - What happens if you run the query without the INSERT clause. That is to say, just the SELECT/FROM/WHERE clauses?

That might give you a clue as to what is gong wrong. It could be just a bad record is messing things up.

--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

On Mon, Feb 21, 2022 at 08:29 AM, Jim Wagner wrote:

Hello all,
Well I have been avoiding this for 4 years since my boss retired. She had created around 24 queries to build one table. It is quite amazing but i never wanted to change it because for one it works and another it is messy. But here I am needing some help. Last friday an error came up during the running of this process that I have never seen before. "Record Deleted". I have no idea what that means. I changed the query to get rid of the error but I am pretty sure the correct results of the query are going to be wrong. What i changed was to get rid of part of the where statement AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null)
there are actually 2 queries in the process that fail but they are 2 different error messages. I will tackle one at a time.
Below is the SQL for the record deleted error.

INSERT INTO ALLSEPARATEDEMPLOYEES ( Effdt, [Acn Rsn Cd], [Acn Rsn Ld], [First Nm], [Last Nm], [Person Id], [Jobcode Id], [Jobcode Ld], [Empl Cls Ld], [Dept Id], [Dept Ld], [Empl Stat Cd], TFOutDesignation, [Acn Cd], [Acn Ld], RcdAddedDt, [Service Date], source, [Paygrp Ld] )
SELECT [FinalTermsAndTransfersOut-ALL].Effdt, [FinalTermsAndTransfersOut-ALL].[Acn Rsn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Rsn Ld], [FinalTermsAndTransfersOut-ALL].[First Nm], [FinalTermsAndTransfersOut-ALL].[Last Nm], [FinalTermsAndTransfersOut-ALL].[Person Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Ld], [FinalTermsAndTransfersOut-ALL].[Empl Cls Ld], [FinalTermsAndTransfersOut-ALL].[Dept Id], [FinalTermsAndTransfersOut-ALL].[Dept Ld], [FinalTermsAndTransfersOut-ALL].[Empl Stat Cd], [FinalTermsAndTransfersOut-ALL].Transferout, [FinalTermsAndTransfersOut-ALL].[Acn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Ld], Date() AS RcdAddedDt, [FinalTermsAndTransfersOut-ALL].[Service Dt], "PS" AS source, [FinalTermsAndTransfersOut-ALL].[Paygrp Ld]
FROM [FinalTermsAndTransfersOut-ALL] LEFT JOIN ALLSEPARATEDEMPLOYEES ON [FinalTermsAndTransfersOut-ALL].[Person Id] = ALLSEPARATEDEMPLOYEES.[Person Id]
WHERE ((([FinalTermsAndTransfersOut-ALL].Transferout) Like "tran*") AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null));

I wonder if this is related to a office update because this has been working for 10 years.

Thank You Very much for any help

Jim Wagner

Re: [MSAccessProfessionals] Record deleted error | Run-time Error 3167

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

On Mon, Feb 21, 2022 at 08:29 AM, Jim Wagner wrote:

Hello all,
Well I have been avoiding this for 4 years since my boss retired. She had created around 24 queries to build one table. It is quite amazing but i never wanted to change it because for one it works and another it is messy. But here I am needing some help. Last friday an error came up during the running of this process that I have never seen before. "Record Deleted". I have no idea what that means. I changed the query to get rid of the error but I am pretty sure the correct results of the query are going to be wrong. What i changed was to get rid of part of the where statement AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null)
there are actually 2 queries in the process that fail but they are 2 different error messages. I will tackle one at a time.
Below is the SQL for the record deleted error.

INSERT INTO ALLSEPARATEDEMPLOYEES ( Effdt, [Acn Rsn Cd], [Acn Rsn Ld], [First Nm], [Last Nm], [Person Id], [Jobcode Id], [Jobcode Ld], [Empl Cls Ld], [Dept Id], [Dept Ld], [Empl Stat Cd], TFOutDesignation, [Acn Cd], [Acn Ld], RcdAddedDt, [Service Date], source, [Paygrp Ld] )
SELECT [FinalTermsAndTransfersOut-ALL].Effdt, [FinalTermsAndTransfersOut-ALL].[Acn Rsn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Rsn Ld], [FinalTermsAndTransfersOut-ALL].[First Nm], [FinalTermsAndTransfersOut-ALL].[Last Nm], [FinalTermsAndTransfersOut-ALL].[Person Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Ld], [FinalTermsAndTransfersOut-ALL].[Empl Cls Ld], [FinalTermsAndTransfersOut-ALL].[Dept Id], [FinalTermsAndTransfersOut-ALL].[Dept Ld], [FinalTermsAndTransfersOut-ALL].[Empl Stat Cd], [FinalTermsAndTransfersOut-ALL].Transferout, [FinalTermsAndTransfersOut-ALL].[Acn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Ld], Date() AS RcdAddedDt, [FinalTermsAndTransfersOut-ALL].[Service Dt], "PS" AS source, [FinalTermsAndTransfersOut-ALL].[Paygrp Ld]
FROM [FinalTermsAndTransfersOut-ALL] LEFT JOIN ALLSEPARATEDEMPLOYEES ON [FinalTermsAndTransfersOut-ALL].[Person Id] = ALLSEPARATEDEMPLOYEES.[Person Id]
WHERE ((([FinalTermsAndTransfersOut-ALL].Transferout) Like "tran*") AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null));

I wonder if this is related to a office update because this has been working for 10 years.

Thank You Very much for any help

Jim Wagner

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

[MSAccessProfessionals] Record deleted error | Run-time Error 3167

Hello all,
Well I have been avoiding this for 4 years since my boss retired. She had created around 24 queries to build one table. It is quite amazing but i never wanted to change it because for one it works and another it is messy. But here I am needing some help. Last friday an error came up during the running of this process that I have never seen before. "Record Deleted". I have no idea what that means. I changed the query to get rid of the error but I am pretty sure the correct results of the query are going to be wrong. What i changed was to get rid of part of the where statement AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null)
there are actually 2 queries in the process that fail but they are 2 different error messages. I will tackle one at a time.
Below is the SQL for the record deleted error.

INSERT INTO ALLSEPARATEDEMPLOYEES ( Effdt, [Acn Rsn Cd], [Acn Rsn Ld], [First Nm], [Last Nm], [Person Id], [Jobcode Id], [Jobcode Ld], [Empl Cls Ld], [Dept Id], [Dept Ld], [Empl Stat Cd], TFOutDesignation, [Acn Cd], [Acn Ld], RcdAddedDt, [Service Date], source, [Paygrp Ld] )
SELECT [FinalTermsAndTransfersOut-ALL].Effdt, [FinalTermsAndTransfersOut-ALL].[Acn Rsn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Rsn Ld], [FinalTermsAndTransfersOut-ALL].[First Nm], [FinalTermsAndTransfersOut-ALL].[Last Nm], [FinalTermsAndTransfersOut-ALL].[Person Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Id], [FinalTermsAndTransfersOut-ALL].[Jobcode Ld], [FinalTermsAndTransfersOut-ALL].[Empl Cls Ld], [FinalTermsAndTransfersOut-ALL].[Dept Id], [FinalTermsAndTransfersOut-ALL].[Dept Ld], [FinalTermsAndTransfersOut-ALL].[Empl Stat Cd], [FinalTermsAndTransfersOut-ALL].Transferout, [FinalTermsAndTransfersOut-ALL].[Acn Cd], [FinalTermsAndTransfersOut-ALL].[Acn Ld], Date() AS RcdAddedDt, [FinalTermsAndTransfersOut-ALL].[Service Dt], "PS" AS source, [FinalTermsAndTransfersOut-ALL].[Paygrp Ld]
FROM [FinalTermsAndTransfersOut-ALL] LEFT JOIN ALLSEPARATEDEMPLOYEES ON [FinalTermsAndTransfersOut-ALL].[Person Id] = ALLSEPARATEDEMPLOYEES.[Person Id]
WHERE ((([FinalTermsAndTransfersOut-ALL].Transferout) Like "tran*") AND ((ALLSEPARATEDEMPLOYEES.[Person Id]) Is Null));

I wonder if this is related to a office update because this has been working for 10 years.

Thank You Very much for any help

Jim Wagner

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_