0

I've automated some old access files at work. (What previously was clicking a button in a form, waiting an hour, repeat 4 times, is now running it all one go on open, then closing and compacting the database).

The files are stored on a shared drive that is locally on the server that we try to run them on.

I can run the files on my PC, both while on the network and through VPN. I cannot, however, run them on the server. When i open them, access starts up and shows the getting started-view and nothing happens.

When running them through a scheduled task, as intended (they take a few hours, so the idea is to run them during the night on the server), I just end up with a locked access file and a process with no GUI for access that further hinders removal of the lock file. After a weekend, I have 9 processes and all the files have locks and nothing is run.

The weird part is that I get no error messages. Furthermore, just a few weeks ago, when I was building them, I could run them, but they'd only perform half of the VBA scripts (it's exporting a couple of CSVs for example, and they didn't generate new ones, when run on the server, but keep working fine from my PC). Then, after a week off from work, the above stated behavior began instead.

At this point, I've probably spent a whole work week troubleshooting this and gotten nowhere. Any help is appreciated. Haven't found anything from google searches that matches this behavior...

June7
  • 19,874
  • 8
  • 24
  • 34
Björn Brorsson
  • 15
  • 3
  • At this point, I'm not sure if there is something wrong with my VBA scripts, file ownership (the user trying to run the file has full rights to the entire catalog that it runs in) or the office setup. What frustrates me is that I can run it perfectly from my own PC, but I can't have that running stuff in the middle of the night for 6 hours straight. The server is running anyway and is used for this kind of thing and common file areas and that's it. – Björn Brorsson Sep 11 '19 at 20:29
  • 1
    FWIW running any Office application headless isn't a supported or recommended scenario. Any random `MsgBox`, application alert, or run-time error could silently halt execution, and no one would know until it's too late. Consider setting up and migrating the Access back-end (it's got a back-end, right?) to an actual database server (e.g. SQL Server Express, free) with SQL Agent jobs instead. – Mathieu Guindon Sep 11 '19 at 20:49
  • What login is the scheduled task running under? Does that login have access to all the network locations it needs to do its thing? – Mathieu Guindon Sep 11 '19 at 20:52
  • Unfortunately, there is no access back end. This is built by someone who left the company 5+ years ago and is so deeply integrated in the daily workings of half the company that it's scary. They do fetch some data from the Oracle database we have that runs the main web application, others from a csv that gets put on the file server, some from other access files that is run manually weekly (also automated but can't run), but this is for the behind the scenes workings of managing the data we get before importing it into the Oracle database, and generating some statistics. It's a clusterfuck... – Björn Brorsson Sep 11 '19 at 20:59
  • It's running off a service account that has full access to the whole disk that it's running things on. But I get the same (lack of) errors when running it from my AD admin account login, just by opening the access file. – Björn Brorsson Sep 11 '19 at 21:00
  • That isn't much to go on... try implementing some logging? – Mathieu Guindon Sep 11 '19 at 21:11
  • 3
    As mentioned, headless automation of any Office application simply isn't a supported scenario. I think that it would be more productive to migrate them to a platform that supports the headless automation. As a short term solution, you could consider using a commercial product such as FMS' Total Agent or a third party task scheduler. I've found that Windows Task Scheduler finds plenty to be desired and isn't very reliable. We've used [this product](https://www.splinterware.com/products/scheduler.html) for different applications and it works much better than Windows' scheduler. – this Sep 11 '19 at 21:19
  • 2
    We obviously need to know what the database actually does (forms, queries, reports, modules, etc.) and see some code. Check if you have read/write permission to location on server which Access needs if it creates folders, exports files, etc. Possibly error handling suppresses errors, `On Resume Next` or `Goto`. You need to carefully test the local version and remote version for every step of process. As of now, this question is too broad. – Parfait Sep 11 '19 at 21:19
  • The behavior is the same for all 3 automated access files. The first uses data from a file that gets generated from the web application daily (csv) as well as ODBC connected tables from our Oracle database. It essentially creates a few tables in the access file with data from those tables and files. This was done by a button press on a form, but I've changed it to trigger on file enter. Upon finishing up, it compacts the file and closes access. – Björn Brorsson Sep 11 '19 at 21:30
  • @this, just migrate, left handed ;) If code is not too crappy, it can work reliable (of course not recommebed). All errors need to be trapped and instead of Msgboxes log to a file or a table. I have a sync to Google Cal running every 5 mins (form timer) and can rely on that (most times). @ Björn: any infos in win event logs (especially Office Alerts)? Runtime or full Office on server? Same bitness? Check references. – ComputerVersteher Sep 11 '19 at 21:33
  • @computerVersteher no, the event log just states access opened. There is nothing to indicate that it even tried to open the files I tell it to. The office on the server is full office, 64-bit, just like on my PC. – Björn Brorsson Sep 11 '19 at 21:37
  • Following on my previous reply; The second one fetches the data from the first one and some additional data from the ODBC Oracle DB and is then used as the database for another access file. A third one uses another csv and some ODBC Oracle DB tables to generate 5 Excel files with statistics. – Björn Brorsson Sep 11 '19 at 21:37
  • 2
    "the event log just states access opened" - implement some trace logging in the VBA code to write to the event log and see what's stalling, or at least what the last successful operation was. – Mathieu Guindon Sep 11 '19 at 21:43
  • You know`Applications and Services Logs > Microsoft > Microsoft Office Alerts`?DB complies? ODBC with DSN? Running DB in normal window mode doesn't show errors? – ComputerVersteher Sep 11 '19 at 21:44
  • You tried your personal frontend on server (copy there) as compacting on xlose may corrupt db. Any non temptaty data dtoref im zhaz dbs? if not omit compact on close, just have a share with script, that copies a fresh db to exec path and starts it. That can also be used to have the actual version, afrer an update. What about patch status on server? Any updates installed since it went worse? Any Mapped drives missing on server? – ComputerVersteher Sep 11 '19 at 21:57
  • @this now we can focus on a better solution. Any suggestions what to use. I first thouhjt on VBSxrpt, but it seems to [lack some essentials like error handling](https://learn.microsoft.com/en-us/previous-versions//30593abb(v=vs.85)?redirectedfrom=MSDN). At the monent I think of a`Powershell-Script or`should we better create a`C#-Service`? – ComputerVersteher Sep 12 '19 at 16:50

1 Answers1

0

In a strange tun of events, it appears to be working now. Found that Office hadn't installed all updates. Did that and it began to work....

Björn Brorsson
  • 15
  • 3
  • Servers and updates have a special relationship. Can you post what updates where missing? DB creates csv mainly? What size, as the execution time seems ridiculously long. Done by sql or vba? – ComputerVersteher Sep 12 '19 at 16:40
  • Unfortunately, no. I'm not the admin for the server. – Björn Brorsson Sep 16 '19 at 20:35
  • The DB has to calculate stats of about 2 million products, then exporting those stats in a few different ways to one CSV and four Excel-docs. Done by VBA running sql scripts, mainly. Not even sure why we do it in Access to begin with... :p – Björn Brorsson Sep 16 '19 at 20:37