MS Access 2016 Table read-only, unable to update from scheduled macro?

[pelizdr]

New user to forum, I have an odd issue with a macro stopping due to a table being "read only" (macro kicks off a bunch of VBA scripts to update various tables and massage data). The database and macro have not changed in a number of months, the only change I can think of is MS updates for Office 2016. I generally call the macro using a scheduled task. When the macro gets to the point of importing data into a local table, the macro stops and says unable to import data due to the table being read only. If I run the same macro after opening the database myself (and taking my time to scroll down and launch the macro), it runs just fine. I tried to add a simple counting loop to see if this was some kind of odd timing issue, but adding two seconds before the query doesn't seem to help.



Wondering if anyone else has experienced this, and how they got around it. I would much rather NOT wake up at 3:00 am to run this process lol.

[ranman256]

is this a single all-in-1 database with no other users?
or
a split database where others can use?

are there any open forms when the macro runs?

[pelizdr]

there are no other users, this database is a back end to update MS SQL tables, the only form that is open is the switchboard on open, users access the data from a completely different front end. This process has been chugging along for awhile, just recently (like within the last week or so) has had issues running the VBA to import data into a local table (the table is located in Access, not SQL).

[aytee111]

Windows 10 - a REALLY bad operating system. MS screws things up on a daily basis.

[isladogs]

Newer versions of Access are less forgiving of code errors than older versions but generally that's a good thing.

Does your SQL table have a PK field?
Does it have any Boolean fields where you haven't set a default value?
Can it be edited in SQL server?
Can it be edited manually in Access?

[pelizdr]

Newer versions of Access are less forgiving of code errors than older versions but generally that's a good thing. - I don't believe this is a code error, the update query begins to run and tells me the table is read-only

Does your SQL table have a PK field? - this is not an SQL table, this is a local Access table, but yes, the table does have a PK
Does it have any Boolean fields where you haven't set a default value? no
Can it be edited in SQL server? - N/A this is a local Access table, not an SQL table
Can it be edited manually in Access?

I responded to your comments, the issue I don't believe is code, I can open the database manually and kick off the exact same macro that launches the VBA code without issue, when I kick off the macro from command line/task scheduler, it tells me the local table is read only. I didn't even know you could set Access tables to be read-only. What I think may be happening is the database isn't opening "enough" on launch before the macro kicks off.

[isladogs]

Not sure why that would make it read only ,,,,

Anyway, try & test that by running something else first
OR use the sleep API function to make the database wait for a couple of seconds then run your code

Paste this line in the declarations section of a standard module:

Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

The causes a delay of the specified number of milliseconds

Usage example - causes 2 second delay

Sleep 2000

Suggest you put that in a function along with your other code
Then run that function using an autoexec macro at startup

[pelizdr]

So, this is humorous, and only MS people would get this. I changed nothing, and the process ran fine this morning. Instead of building a module (as simple as that one is), I had stuffed this in a couple weeks ago.

Twait = Time
Twait = DateAdd("s", 30, Twait)
Do Until Tnow >= Twait
Tnow = Time
Loop

[isladogs]

Or just use

Sleep=30000