Set Affinity for Access 2016 in Windows 10 Command Prompt?

Since you have tried, I doubt it, but I have forwarded your question to some who know better.

Not all executables respect that parameter, so msaccess obviously doesn't (same here with win10 and access 2016).

I wonder if the following could be useful in this situation.  I have never used it though.

http://affinitychanger.sourceforge.net/

Gustav - Thanks!  Hope lives.

McKnife - You probably are correct, but I will hold off that depressing conclusion (for now).

Daniel - Unfortunately, a 3rd-party tool is not allowed in this situation.

Gustav,

Any feedback to you from "some who know better"?

(Still clinging to hope on my end.)

Do you want to script that, or would doing it manually suffice? Task manager can set CPU affinity, too. Go to it's details tab, right click the access process and set affinity there.

McKnife,

I can set affinity from the Task Manager or code, but the issue is a custom tool (in production for years now) that compacts all backends on a given PC.  It piggybacks on Access, itself, to do all the work (and exception handling) of compact/repair, by using command-line switches.  For each backend file, it shells:

"C:\Program Files (x86)\Microsoft Office\ Office16\MSACCESS.EXE" <Database Filename> /Compact

The “/compact” switch tells Access to open, compact/repair the given database file, and close.  By using intrinsic Access functionality, we avoid re-inventing the wheel, in which custom code would need to:

1.      Check for all possible exception conditions (e.g. someone logged into the backend), and abort if necessary.

2.      Rename the backend file with some intuitive designation (e.g. insert “PreCompactBackup” immediately before the “.MDB”).

3.      Run the “CompactRepair” command, to compact/repair the (old) renamed file to a (new) file with the original name.

4.      Delete the (old) re-named file.

5.      Hope that nothing goes wrong, because I know of no way to wrap file system changes in a “transaction” that code can “commit” or “roll back”.

If there were some way to specify in shell commands that the Access thread use only four cores, it would allow us to minimally modify the existing scheme, and still piggyback on Access, itself, to handle everything that possibly could go wrong.

The reason we need to limit compact/repair threads to only four cores is because of known issues with Access running on more than that, which (among other things) can destroy backends above a certain size.

Yes and no. Jim Dettman pointed me to this old thread:

MSAccess Database slow between 2012 Hyper-V virtual machines

But I'm not sure if it relates to your scenario.
What is your issue actually?

Gustav,

I detailed the issue in my reply to McKnife (above), but bottom line: We want to avoid Access shredding backends while doing compacts/repairs on Windows 10 systems with 12-core CPUs.

We ran into the "System Resource Exceeded" error straight out of the box, on a simple operation to add less than 2,000 records to a large Table Definition (using the AddNew method of a DAO Recordset inside a loop).

We found literature detailing this issue, and found a workaround by setting the Affinity of the Access thread to only four processors, using the Task Manager.   Additional literature suggests this same Affinity issue can corrupt a database during compact/repair, and that REALLY got our attention.

We have code ready to deploy, which limits Affinity to four processors at startup, but in the link you cite,  Jim defines the issue as occurring when Access runs "on multi-core CPUs under a 64 bit OS," and suggests the fix is to set the "MaxBuffers" to 2^16.  His post is from 2013, addresses Access 2010, and gives a link to KB2726928, which suggests a hotfix package.

Questions:

1. Is the solution Jim gives still relevant?  One would hope that MS has fixed this issue in Access 2016, but our experience (with brand new, fully patched, 12-core systems running Windows 10) suggests otherwise.

2. If his advice still holds, then does it substitute for limiting Affinity to four cores (i.e. setting "MaxBuffers" to 2^16 allows one to run worry-free on all 12 cores)?

3. We use Access 2016 to run MDE/MDB files.  Does that matter to either of the above questions (i.e. is our scenario ACE, Jet, or both)?

Can't tell, sorry.

1. I've never met the issue, and I've never had a machine with 12 cores. My Xeon workstations have 4 cores only
2. I don't think these two settings are interrelated, but Jim may know
3. That could be worth checking out. These days, I rarely use mdb files

Gustav,

How do I pull Jim into this thread?

I have dropped him a note, and gave you a link in a previous post.

He might be busy with real work, though.

1. It's my understanding that it has been long since fixed, although this may be a closely related issue.  Could also be something entirely different.

  That said, setting MaxBuffers to 65535 is not going to hurt.  You can use dbEngine.SetOption at the start of the app to set it.

2.  Not exactly.   What the setting does is makes the cache large enough to avoid the garbage clean-up issue.  JET/ACE is still running on multiple cores.

3.  No.  JET and ACE are one in the same thing (more or less).  ACE has had some improvements, but it's basically JET 4.0 and works fundamentally the same way in terms of database structure, page handling, and the cache.

Jim.

Jim,

Thanks for the insight.

What [setting MaxBuffers to 65535] does is makes the cache large enough to avoid the garbage clean-up issue.  JET/ACE is still running on multiple cores.

So, apparently, the original issue (back in Access 2010) was some sort of JET/ACE "garbage clean-up issue" that only occurred on systems with more than four cores?

Yes.   It was related to allocating pages in the working set for the process   If I understood correctly (not a lot of detail was available on exactly what was going on), allocation of new pages would occur across multiple threads (JET/ACE has three by default), and they would block one another if each thread was on a different core.  

Why that came into play under a 64 bit OS and not 32's I don't know.

My memory also tells me that when I tested setting the affinity, I found that Access would not honor it, and yet several people said it solved their problem. Not sure if that was a manual vs a shortcut thing or what.  I've never went back to it because it's such an obscure issue.  

Windows does a pretty good job of scheduling multiple threads and I'm not aware of too many cases where having them caused issues (some games run better when set to run on less CPUs).

I think this is probably a problem Microsoft should look at (too much guess work on our parts) and I believe gustav has already passed it along to them.

Jim.

Jim,

Thanks for the details.

My memory also tells me that when I tested setting the affinity, I found that Access would not honor it, and yet several people said it solved their problem. Not sure if that was a manual vs a shortcut thing or what.

Well, setting it manually (per Task Manager) on the already-running Access 2016 process definitely solved our problem, but (as I mentioned upthread) trying to set it per Windows Command fails.

We will set MaxBuffers to 2^16 for sure, but what are your thoughts about startup code to limit Access 2016 to only four of the 12 cores?  (We have tested it, and checking the Affinity of the process in Task Manager says it works.)  Would you recommend it, given posts like this: Compact & Repair MDB Problem with Multi-Core CPU?

Given that we saw "System Resource Exceeded" on Access 2016 -- and that we worked around it by setting Affinity to 4 cores manually (per Task Manager) on the already-running process -- would you recommend the 4-core startup code?

Our biggest fear is corrupting a backend when it compacts.

Bill

PS: Are you aware of anyone (other than us) who still sees Multi-Core issues in Access 2016?

...this is so funny...

I tried it again... and it just works.
cd "c:\Program Files (x86)\Microsoft Office\Office16"
start /affinity 1 MSACCESS.EXE - CPU 0 alone
start /affinity 3 MSACCESS.EXE - CPU 0+1
start /affinity 7 MSACCESS.EXE CPU 0+1+2
start /affinity f MSACCESS.EXE CPU 0+1+2+3

Right as expected. Please note the hexadecimal notation needs to be used as shown here: https://blogs.msdn.microsoft.com/santhoshonline/2011/11/24/how-to-launch-a-process-with-cpu-affinity-set/

McKnife,

I can recreate what you did:

1. Execute a Command Line to change directories to Office 16.
2. Execute the Command Line "start /affinity 1 MSACCESS.EXE" (substituting any appropriate hex value for "1").

What I need, however (pending feedback from Jim that would make it a moot point), is a single Command Line.

For example:

start /affinity 1 "C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE"

or (to avoid using quotes):

start /affinity 1 C:\PROGRA~2\MICROS~1\Office16\MSACCESS.EXE

Unfortunately, neither of these works.

(Sorry if I did not make the single Command Line need clear in my posts upthread.)

ETA: The following Command Line (devoid of any path) also does not work:

start /affinity 1 MSACCESS.EXE

Couldn't you call a batch file or PowerShell script doing:

C:
cd C:\PROGRA~2\MICROS~1\Office16
start /affinity 1 "MSACCESS.EXE <params>"

Select allOpen in new window

Yes, it's perfectly unclear why it needs to be just one line.
But well, what about just combining these 2 lines into one

cd "c:\Program Files (x86)\Microsoft Office\Office16" & start /affinity f MSACCESS.EXE

Select allOpen in new window

Gustav and McKnife,

If I enter the following (single line) in the Command Prompt window, it does exactly what I want:

C: && cd "C:\Program Files (x86)\Microsoft Office\Office16" && start /affinity 1 MSACCESS.EXE <MDB Filename> /wrkgrp <MDW Filename> /user <User ID> /pwd <Password> /compact

I need to issue the equivalent of this from VBA, using the Shell command:

VBA.Interaction.Shell PathName:="C: && cd " & """C:\Program Files (x86)\Microsoft Office\Office16""" & " && start /affinity 1 MSACCESS.EXE <MDB Filename> /wrkgrp <MDW Filename> /user <User ID> /pwd <Password> /compact"

Unfortunately, when I try that, I get an "Invalid procedure or call argument.

Is the Shell command capable of executing multiple Commands with the "%%" delimiter from VBA?

ETA: Just to be clear, this is not the result of a syntax error in the double-quotes around the Office path.  I get the same result with:

VBA.Interaction.Shell PathName:="C: && cd C:\Temp"

What I meant is, call the command file:

VBA.Interaction.Shell PathName:="d:\path\commandfile.cmd"

Select allOpen in new window

Gustav,

Unfortunately, a batch file is not allowed; it must be done by code alone.

You could create it on the fly in the %temp% folder - no one would know …
An old trick for command scripts for ftp.exe.

Gustav,

That is an interesting idea, but there are strict security protocols in place that I must not violate.

If there were some way of placing a batch file in memory (i.e. a virtual disk?) and executing it from there, then that would work.

Do you know if (or how) this is possible with VBA?

I guess that adding the application path of office to the path variable would do to make the original code work. Could be considered.

I could pass a single line to the Shell command in VBA that would open an instance of the Command Prompt (with the "/c" syntax instructing it to terminate after executing the Command), and then pass it my (single line) Command:

VBA.Interaction.Shell PathName:="cmd.exe /c" & " c: && cd " & """C:\Program Files (x86)\Microsoft Office\Office16""" & " && start /affinity 1 MSACCESS.EXE <MDB Filename> /wrkgrp <MDW Filename> /user <User ID> /pwd <User Password> /compact"

Is this accepted practice?  Is it stable? What are the known risks?

If it works, fine. One has to use those tricks and tools, that are left in the toolbox.

I know the organization runs (among others) Symantec Endpoint Protection and CylancePROTECT.

Does anyone know if these prevent an application (i.e. VBA in Access 2016) from opening the Command Prompt and executing Windows Commands in it?

If so, then the above might not work.

Those tools seem to be focused on protecting the network.

Windows 10, however, can be set up with Ransomware Protection  that may prevent applications to write to the disk. If so, and an application tries to do that, a warning message is sent to the user. But this should not affect msaccess.exe.

I would just check it out.

Gustav,

I will pursue the strategy of VBA opening the Command Prompt and executing Windows Commands in it.  If there are no security impedements, then this will allow VBA to compact external backends with the specified number of cores (i.e. four out of 12) on shelled threads -- but I remain perplexed as to why the "start /affinity 1 MSACCESS.EXE" syntax only works if the current directory is "C:\Program Files (x86)\Microsoft Office\Office16".

Two questions raised in posts above, however, remain unanswered:

1. Is compacting a database on more than four cores still an issue in Windows 10/Access 2016?

2. Should I implement startup code in all databases to limit their thread to four cores in Windows 10/Access 2016 (or does setting MaxBuffers to 2^16 eliminate any risk)?

I would value the insight of experts here (including Jim) on those.

Must I open them as separate EE questions, or is this one appropriate (enough)?

1. Is compacting a database on more than four cores still an issue in Windows 10/Access 2016?

 No idea.   Your the first I've heard of having this problem.

2. Should I implement startup code in all databases to limit their thread to four cores in Windows 10/Access 2016 (or does setting MaxBuffers to 2^16 eliminate any risk)?

 Don't know.   Not sure if they are a result of one in the same problem or two distinct ones.

 This is so far deep in the weeds and rare of a problem that I think Microsoft is the only one that will have an answer to this...and that usually takes time.   They usually can confirm fairly quickly if it's reproducible and the "why", but fixes often take months.  

 I would move forward with whatever you need to do to run a C&R on one core for now since you know that works.

Jim.

why the "start /affinity 1 MSACCESS.EXE" syntax only works if the current directory is "C:\Program Files (x86)\Microsoft Office\Office16".

Because, I guess, the name of the executable cannot be resolved from the full path.
My answers to 1. and 2. are the same as Jim's.

I would leave this in the box of mysteries we all have at the desk and use the method that seems to work for you.

Given the "knowns" and "unknowns" discussed in this Question, I think the best strategy (at this point) would be:

1. Set MaxBuffers to 2^16.

2. Code VBA to read an optional "MaxCoreCount" argument (at startup) from the "/cmd" command-line switch in the logon shortcut.

Number 1 is a no-brainer; Number 2 allows flexibility to limit the maximum number of Cores in the Access thread, by simply changing the Target field of the logon shortcut prior to start up.

The "greater-than-four-Cores" issues cited in pre-Windows 10/Access 2016 environments may or may not be solved at this point.  Who knows?  (Maybe not even Microsoft.)  It seems prudent to build in some flexibility to deal with it, one way or another, moving forward.

Do any experts here counsel otherwise?

1. seems fine.
But 2. I guess, should be set on the command line that launches Access, not as a command line parameter to Access:

C:
cd C:\PROGRA~2\MICROS~1\Office16
start /affinity 1 "MSACCESS.EXE <params>"

Select allOpen in new window

Gustav,

Sorry for the confusion.  (Face-to-face communication is always more effective than the limited forum of online posting.)

You are correct that my original Question ("Set Affinity for Access 2016 in Windows 10 Command Prompt?") has been answered by a variation of the syntax you give:

VBA.Interaction.Shell PathName:="cmd.exe /c" & " c: && cd " & """C:\Program Files (x86)\Microsoft Office\Office16""" & " && start /affinity 1 MSACCESS.EXE <MDB Filename> /wrkgrp <MDW Filename> /user <User ID> /pwd <User Password> /compact"

This assumes, of course, that client security does not prevent VBA from opening the Command Prompt and executing Windows Commands in it.  I still need to test that in the client environment.  If it works, then I need to close this Question and award points.

My original Question addressed an existing application that Shelled out command lines to compact backends, and I hope we have solved that challenge by modifying the Shell command as above.

My previous post, however, addressed an additional issue raised in discussions here: The risk of any Access database running on more than four Cores.  Given that potential risk (and the historical citations that suggest it), my previous post addressed how to mitigate it when launching any database via logon shortcut (which is above and beyond the original command-line Question):

"C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE" <MDB Filename> /wrkgrp <MDW Filename> /cmd MaxCPUCount=4

Again, sorry for the confusion.  Hopefully, this post clarifies my previous one.  If not, then I will try again.

Oh, it is perfectly clear now. Thanks!

Due to scheduling issues at the client site, I have not yet been able to test any of the potential solutions discussed in this thread; however, I hope to complete that testing in the client environment by end of week, and subsequently post results.  Hopefully, I will be able to close this Question and award points at that time.

Jim,

You wrote above:

That said, setting MaxBuffers to 65535 is not going to hurt.

This thread, however, suggests -- to my horror -- that it just might.  Although its posts are from a few years back, and address two different scenarios than mine (MS Access 2002 Runtime on Server 2008, and 64-bit Access 2010 on Windows 7 Ultimate), both describe database corruption from Compact & Repair where the MaxBufferSize was too high (i.e. >=50000), rather than too low.

Your thoughts?

<<Your thoughts?>>

 Never heard of that.

Jim.

I need to close this question and award points.  Because the route from start to finish was not straightforward, my initial thought is to divide points between Gustav and McKnife for their persistence, with honorable mention to Jim for his additional insights.

Does that seem reasonable?

No problems here...do as you see fit.

Jim.

I guess I technically came up with a solution on my own (VBA opening the Command Prompt and executing Windows Commands in it), but that never would have been possible without insights from Gustav, McKnife, and Jim.  Thanks to you all!

You are welcome!