Microsoft Access is not allowing me to compact and repair. Keeps saying its open etc

Had the same problem on my New Dell (which came with McAfee Antivirus) I was also having issues with adding tables and was getting the error message Cannot define field more than once. (Error 3191) which has nothing to do the actual issue, as there are no fields defined more than once.

This function works perfectly on my Windows 10 32 bit office Dell:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

On Error GoTo AttachDSNLessTable\_Err 

Dim td As TableDef 

Dim stConnect As String 

For Each td In CurrentDb.TableDefs 

    If td.Name = stLocalTableName Then 

        CurrentDb.TableDefs.Delete stLocalTableName 

DoEvents

    End If 

Next 

   stConnect = "ODBC;DRIVER=MySQL ODBC " & MySQLVersion & " ANSI Driver;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword & ";Port=" & stPort & ";Option=8;Option=16" 

End If 

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect) 

DoEvents

**CurrentDb.TableDefs.Append td   '64 bit MSAcces Ver 2202 build 14931.20120 click-to-run), Windows 11 errors out here**

AttachDSNLessTable = True 

Exit Function 

AttachDSNLessTable_Err:

AttachDSNLessTable = False 

MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description & " " & stRemoteTableName 

End Function

I was able to fix the Compact and Repair functionality by Uninstalling McAfee and Installing MalewareBytes instead. I was hoping this was going to resolve both issues, but I still can't add a table either with VBA above, or by trying to use the build in New Datasource wizard in MS Access. Thought I would mention both as they might be related.

Note: Just rebooted after getting rid of McAfee and compact and repair is still working, adding new linked tables is not however.

FYI: I just closed down the program on the computer I use most of the time then went and started my other computer and opened the database from that one (I have it linked via shortcut to my main computer over my home network) and I was able to compact. The only difference I can see is that they have two different versions of windows running. My main computer is running the latest version where as the other is build # 19042. Both are running the same version of Office.

I tried to revert back to the previous version of windows, but it won't let me as it's been more than 10 days.

Not sure if this has anything to do with what's going on.

Guys I think I am correct that it is about speed of the app - In this new Dell my file is not being compacted as I mentioned in my last update. The file size is 7.8 Mb and has a whole lot of objects. Another database file that is very new, and has only 2 tbles, 2 queries, 3 forms, and 3 reports. This has just been made a couple of days ago. And there is only sample data that I have put in to examine the database in use. This file is 648 KB and gets compacted in a jiffy.

So since the larger file gets compacted in one comp but not the other, it cannot be a size of file issue. Plus the file isn't really large. Also the file cannot be corrupted if it is opening on one PC without a problem and not the other. The only thing I can say it has a lot of objects for Access to juggle with in the background. This is where I feel the problem is - Access is unable to process all these objects quickly enough.

Ripal

The problem is back. I had switched of the machine and started it now to do some work. The build is still the old version. So the new machine is now not able to process the compact and repair command correctly. Help.

I reset the entire Windows installation. Factory Reset. The Lenovo I3 7th Gen with the latest updated version works perfectly. This Dell 5518 11 gen I7 with 11390H processor where I have reset everything. This Office 365 has gone back by 2 builds. It's working right now. I don't know what will happen when it will update. This I7 is a brand new laptop. From the few days that I have had it I feel this processor despite being an I7 (unfortunately with 4 physical cores even if I7) is slower than the I3 7th gen when using Access. I have scoured the Net to determine why this should be so. One post I found where it says that number crunching in Excel is slower in these new processors. For Access I haven't found any info regarding slow management by this new processor. But I feel that this is an issue with Access where it is not closing whatever it is opening in the background fast enough. My Old Dell I3 3rd gen is not allowing me to compact it. So this is the info I can share based on the 3 computers I have. Am scared to update Office 365 manually as of now. So will let it occur in the due course of automated updates and then observe what happens. Crossing my fingers. One other thing is that I feel that if the comp goes into sleep mode and comes back then if Access was on at the time, it gets confused. So this is why I have set the sleep option to never. Only kept the Close Screen option to a few minutes. This machine has 16 GB ram and my Lenovo I3 has 8 GB. So I don't really think that the problem has something to do with our hardware as such. It's just that Access has started to take more time to finish and close backend processes. This is what I have understood