Adding an AutoNumber field to a linked table

[ebs17]

if encounter any sort of error the TBE object is lost

Hopefully errors are not the norm.
- The first measure and stylistic device used should be to work without errors. Most of the reasons for possible errors are known, so in such situations you can first check to avoid the situation, take other paths or use substitute values for missing values.
As an example: I like to point out that for me index errors are also errors. So I would very rarely fire duplicates to a unique index when doing append queries and imports.
- Unforeseeable errors and the rest can be dealt with using your existing error handling.
- Anyone who uses error handling to control the program and continues happily despite experiencing errors in the program should know what they are doing.
So, hopefully errors are not the norm.

Technically:
What does it mean when the TBE object is lost?
The real question is then whether the side BE has also disappeared as a file. If not, you can find it and renew the reference to it (GetObject). If the file disappears, all information disappears with it. You can then set up a new object again, but the previous program flow will probably not be easy to continue because of the loss. So if you have to start over, the error that caused it must be eliminated and eliminated. Self-healing object variables won't help either.

Since the proposed solution is mine: No, I have not tested or even ruled out every possible error in this solution that beginners and unintentional people could find. It's not "foolproof". But my way of working, which is based on what was described above, is sufficient.

Another consideration may be: What do you use temporary tables for and how long does each one take? So do I use a temporary backend for the entire runtime of the application? This will usually be sufficient by default.
Or, because you have mastered the creation of the temporary backend, do you only use it for certain tasks depending on the situation and then use a new one for other tasks? So by destroying the object yourself, would you prevent the loss due to errors?

 

[Martynwheeler]

I quite agree that errors should not be the norm. They were just cropping up while I was learning and testing your code. I will make sure that they are handled properly in the finished version

 

[Martynwheeler]

I am interested in your statement:
"If not, you can find it and renew the reference to it (GetObject)".
I have the filename saved, can it be assigned to the TBE object? I am struggling to achieve this
Set TBE = GetObject(Forms("frmHidden").txtTempBEFullPath.Value, "Access.Application")
throws an error of type mismatch.

It really should not throw an error as I have checked the code but it would be good to handle extreme cases.

Thanks

 

[ebs17]

I have the filename saved, can it be assigned to the TBE object?

A lost object (class is terminated)?

So you should re-instantiate the class, with the option of being able to use an existing backend in addition to creating a new backend and controlling this option from the outside.

Forms("frmHidden").txtTempBEFullPath.Value

Something like that doesn't work at all. Information is passed to a class from outside via designated interfaces.

GetObject was probably a misguided and ill-conceived idea. The fact that the temporary backend is not actually opened (using an application object) is out of the question. You probably need an OpenDatabase similar to the procedure in Init.

But I won't bother with the code because it completely contradicts the original purpose of the solution.
If you want to re-integrate existing backends - that's a different case and there are a number of solutions.