Automation Error

[Kayleigh]

Hi
We often encounter this issue on a database which is under development and being used by end users. In short it becomes corrupt and can no longer work - the only way to resolve is to do a complete rebuild of the system ie copy all objects into a new database project and re-link the tables to sql server.
However we would like to know what causes this type of error and how we can prevent it.
Thank you

 

[arnelgp]

you need to Split your db.
create a Shared folder and put the Backend (BE) there.
Relink the FrontEnd (FE) and link using UNC address.
make many copies of the FE and give each to your user.

 

[CJ_London]

However we would like to know what causes this type of error and how we can prevent it.

Each user should have their own copy of the FE located on their local machine, they should not be sharing a copy on the server. Sharing a FE is the most common reason for corruption.

Connecting wirelessly is another - although that is more likely to impact the BE, and sql server is pretty robust in that respect

 

[Kayleigh]

We are doing exactly that - there is one master version which is copied to each user and the FE is linked to the SQL server tables using ODBC connection - is this not correct?

 

[arnelgp]

the only way to resolve is to do a complete rebuild of the system ie copy all objects into a new database project

why do you need to do that when you say you have a master db, so you can make copies from your master db?

 

[Kayleigh]

The master version is under development so it seems to corrupt every now and then.

 

[sonic8]

The master version is under development so it seems to corrupt every now and then.

What exactly do you mean by "under development"? Are there multiple developers working on the very same file? This is not going to work well and frequently will cause file corruption. Each developer must also have his own file to work on. The best way to coordinate and share the development work is by using source code control.

 

[Kayleigh]

No it is only one file that is having changes to it and primarily by myself - if there are other developers involved they would also log in to the same machine and work on the same file. (which ultimately will be the master version of the next version distributed to users)

 

[arnelgp]

if there are other developers involved they would also log in to the same machine and work on the same file

maybe that is causing all this corruption.

 

[Kayleigh]

What would you suggest?

 

[CJ_London]

so to be clear what is getting corrupted - the master FE? or the ones on the users machines?

How can you be sure the users are not using the master FE on the server?

And you didn't comment on my other suggestion

Connecting wirelessly is another - although that is more likely to impact the BE, and sql server is pretty robust in that respect

 

[Kayleigh]

It is generally the users versions being corrupted during regular use.
The users are not using the master FE as I can see there is no locking file.

I am unsure what you mean by connecting wirelessly - all users are on the same LAN?

 

[CJ_London]

connecting wirelessly means they are not using a cable from computer to LAN. i.e. via a socket on the wall.

I've been caught out before - gone to meeting with my laptop, someone asks a question so I fire up the app forgetting to plug it in. Sometimes it works (no corruption), sometimes it doesn't.

 

[Kayleigh]

I see. Well this is not the case as all users are connecting via cable.

 

[gemma-the-husky]

As you develop the front end, keep unused master copies.

MyProject v1.01
MyProject v1.02
MyProject v1.03
MyProject v1.04 etc

If a database corrupts, then go back to a copy of the latest saved version.
Save regularly while developing, every hour maybe, as if you get a problem it won't take you an hour to go back to your last save and redo.

 

[The_Doc_Man]

It is generally the users versions being corrupted during regular use.
The users are not using the master FE as I can see there is no locking file.

I am unsure what you mean by connecting wirelessly - all users are on the same LAN?

There is also no locking file when someone has the file open in Exclusive mode. (Or at least that used to be the case.)

However, I also want to ask about the thread title. Is that an actual error message or are you just saying that you have an unspecified error when you try to automate something? I.e. is your thread title descriptive ... or a quote?

 

[Solo712]

I see. Well this is not the case as all users are connecting via cable.

Not clear whether the corruption originates in the the Master FE or after distribution to users. If the latter, do they all get same errors relating to corruption ?

 

[Pat Hartman]

To be sure that the users are not actually sharing the FE on the server, make note of the last update date when you replace the master copy. Then check the date every day for a while. If the date changes, your distribution plan has failed and the users are using a shared FE.

You also need to look at what the app is doing. I get corrupted databases frequently enough to do something to protect my projects but I am the developer so I have a strong backup strategy. My users never corrupt their FE because they are not making changes to objects. Are you allowing your users to make changes to objects?

I use a batch file to distribute the FE so my users get a fresh copy of the FE each time they open the app. It is simple. Only four lines with no error trapping. This eliminates any problem with bloat.

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\ServerName\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

I don't bother with error trapping because for the first two instructions, it doesn't matter if they fail. The third line copies the FE. This would only fail if the FE went missing for some reason. And the fourth line could fail if I distributed a bad copy of the FE. If you want to add error trapping so you can give the user a customized message, that would be fine.

 

[Kayleigh]

There is also no locking file when someone has the file open in Exclusive mode. (Or at least that used to be the case.)

However, I also want to ask about the thread title. Is that an actual error message or are you just saying that you have an unspecified error when you try to automate something? I.e. is your thread title descriptive ... or a quote?

Yes this is the actual error occurring - it just pops up repeatedly with no other option than to force system to close. It most often happens after distribution although on occasion I find it in the Master FE.

@Pat Hartman I use a very similar method to distribute my FE to users when an update is ready to be released. It checks the version number upon opening, then if different it would trigger a script which is run to copy the latest version to their local workspace and deletes the current one.

Interesting point regarding users changing objects - I cannot imagine a scenario where this would be required in my database although perhaps some of the code is doing a similar action behind the scenes?

Appreciate the ideas

 

[The_Doc_Man]

A common source of "Automation Error" is that you are opening an application object (e.g. Excel workbook) and somewhere along the way you make a reference to that object through a de-referenced object variable. If you allow VBA to break on error (it's in the File >> Options >> Current Database controls) then you can hover the mouse over each part of the references on the highlighted error line. One of them will say "Nothing" and that is your culprit.