Delete table if it does not exist in backend

[LadyMarmalade]

Hi,
I have an update routine which checks the backend for any new/deleted tables.

Firstly, is there a simple way to do this? i.e. make it so that any added or deleted tables will update accordingly in all versions of the front end without needing to do it programmatically?

If not, I have an update routine - it successfully checks and updates any new tables that have appeared but I can't get it to handle deleted tables (i.e. deleting tables from front end that no longer exist in backend). I tried the following:

For Each TB In db.TableDefs
    On Error GoTo table_not_present
    'Temporary tables start with ~
    'All database tables have a _ in it
    If TB.Connect <> "" And Not TB.name Like "~*" And TB.name Like "*_*" Then TB.RefreshLink
    GoTo next_TB
    
table_not_present:
    deleted = deleted + 1
    Call DeleteTable(TB.name)
next_TB:
Next TB

The issue with this is that it is slow and for some reason ignores error handling sometimes.

Is there something similar to the DCount("[Name]","MSysObjects")...I can use but on another database? I already have a backendDB Database variable if that helps.

Any suggestions? There might be a standard/accepted way to handle this that I'm not thinking of!

Thanks,

Lady Marmalade

P.S. Before you ask, I cannot fundamentally change the design of the whole thing so that users aren't generating tables. It has to be done.

 

[jdraw]

Please tell us more about the application and the need to generate tables. It is not a common subject or requirement.

Also, what exactly does this mean??

all versions of the front end

Each user should be using a copy of your "approved/operational" Front end on their own PC.

Do you do Compact and Repair regularly?

 

[LadyMarmalade]

Please tell us more about the application and the need to generate tables. It is not a common subject or requirement.

The user has to import excel files as part of general usage. It necessitates creation of tables as if this were to be stored in one table, it would require several thousand fields.

Also, what exactly does this mean??
Each user should be using a copy of your "approved/operational" Front end on their own PC.

Sorry! I don't mean versions in that respect - I meant different copies of the same approved edition.

Do you do Compact and Repair regularly?

It is set to compact on close. This doesn't cause it to link new tables from the backend/delete tables that are no longer on the backend though, which is the point of my question.

Thanks for your reply! If you need any more information please ask.

 

[jdraw]

But the compact and repair should clean up "deleted" tables and other "used but unaccessable" space.

...several thousand fields

seems a bit extreme. Have you considered a holding table(temporary) and a
series of queries(routines) to move the data into appropriate, designed tables?

 

[sneuberg]

But the compact and repair should clean up "deleted" tables and other "used but unaccessable" space.

If compact and repair deleted links to tables (it doesn't) you would lose all of these links if you happened to do a compact and repair and your network was down. Ouch!

 

[LadyMarmalade]

But the compact and repair should clean up "deleted" tables and other "used but unaccessable" space.

Ah - I doubt that will work. The issue in question is that the table is removed from the backend and there is still a link to the nonexistant table in the front end. Compact and repair will clean up the saved space in the backend but not delete the link in the front end.

seems a bit extreme. Have you considered a holding table(temporary) and a
series of queries(routines) to move the data into appropriate, designed tables?

I honestly appreciate what you're doing, and I may consider it in the future, but at this point there is absolutely no chance I can restructure things like that.

 

[jdraw]

Perhaps the wrong choice of words, but my intent is if you physically remove a table from your back end, that space will be recovered via compact and repair. I'm not sure of the message, but if you refresh the links to the front end, what is the "orphan table" going to link to??? Do you get a message? Could you trap it?

 

[LadyMarmalade]

Perhaps the wrong choice of words, but my intent is if you physically remove a table from your back end, that space will be recovered via compact and repair. I'm not sure of the message, but if you refresh the links to the front end, what is the "orphan table" going to link to??? Do you get a message? Could you trap it?

There is no error message involved - none of the forms will try to access a table that has been deleted from the backend.
It's just that the front end still thinks the table is there, which is wasted space/clutter and potential confusion if anybody were to look at the tables for some reason (very unlikely).
It'd just be messy to leave a bunch of tables that link to nothing, won't be used and won't be cleared by compact and repair!

 

[sneuberg]

The issue with this is that it is slow and for some reason ignores error handling sometimes.

How slow? We've used the J Street Relinker which uses code very similar to yours to check the links and it takes a few seconds to run. Is that what you mean by slow?

I suspect you could query the backend's systems table and perhaps speed this up but some developers don't like to access those tables directly as their structure is not guaranteed to remain the same. Are you will to risk this potential problem?

When you say that it is ignoring error handling do you mean it's not delete files that is should. If this is the case could you give us some of the names that it missed. Either this condition:

TB.Connect <> "" And Not TB.name Like "~*" And TB.name Like "*_*"

is not being met or something is wrong with DeleteTable. Could you also post the code for DeleteTable?

 

[jdraw]

You may get some info from this material(found via Google) re: linking and re-linking FE/BE tables. I have not tested it.
Don't have time at the moment.


Ooops: I see sneuberg has posted while I was "searching".

Also, based on sneuberg's post ---What exactly do you mean by DELETE the table in BE---you mean a Delete or Drop command?

Delete just removes rows, not the table structure.

Good luck.

 

[sneuberg]

Actually you don't need to access the system table of the backend. If you have the path the the backend you could just loop through them like the following code does.

Sub T()

Dim db As DAO.Database
Dim tdef As TableDef
Set db = OpenDatabase("C:\PathToBackend")

For Each tdef In db.TableDefs
    Debug.Print tdef.Name
Next tdef


End Sub

And this took about 41 milliseconds to list about 43 tables. So maybe you could loop through the backend tables and within that loop , loop through the linked tables and delete base on that.

This would require you to have the frontend and backend open in the same subroutine and there's a certain way you have to do that. I believe you have to use that dbEngine()() type of notation. I'm looking for that and will let you know if and when I find it.

 

[The_Doc_Man]

It seems as though you have a confusing table location schema.

Are you saying that EACH USER could potentially import a spreadsheet and make a back-end table of it that would become potentially available to all other users? The problem would then be that no other users would SEE that back-end table unless someone told them to go look for it - because the user running in the front-end doesn't see ANYTHING in the back-end that wasn't already set up or manually/programmatically imported from the BE as a linked table.

Therefore, I am confused by your explanation.

To go deeper, if on the other hand everyone could load a worksheet to make a new FE table, that CAN'T be visible to other users without some SERIOUS design issues. It would lead to a free-for-all where nobody could trust the contents of the FE file.

This design sounds to me as though it is more than just a bit dangerous due to the potential for destructive interference.

 

[LadyMarmalade]

Actually you don't need to access the system table of the backend. If you have the path the the backend you could just loop through them like the following code does.

Sub T()

Dim db As DAO.Database
Dim tdef As TableDef
Set db = OpenDatabase("C:\PathToBackend")

For Each tdef In db.TableDefs
    Debug.Print tdef.Name
Next tdef


End Sub

And this took about 41 milliseconds to list about 43 tables. So maybe you could loop through the backend tables and within that loop , loop through the linked tables and delete base on that.

This would require you to have the frontend and backend open in the same subroutine and there's a certain way you have to do that. I believe you have to use that dbEngine()() type of notation. I'm looking for that and will let you know if and when I find it.

It's the TB.RefreshLink bit which takes time and which ignores the error handling.

In my routine, I already have both databases open as DAO.Database objects , and have the path freely available, so anything utilising that should be fine.

Thank you so much!

 

[LadyMarmalade]

It seems as though you have a confusing table location schema.

Are you saying that EACH USER could potentially import a spreadsheet and make a back-end table of it that would become potentially available to all other users? The problem would then be that no other users would SEE that back-end table unless someone told them to go look for it - because the user running in the front-end doesn't see ANYTHING in the back-end that wasn't already set up or manually/programmatically imported from the BE as a linked table.

Therefore, I am confused by your explanation.

To go deeper, if on the other hand everyone could load a worksheet to make a new FE table, that CAN'T be visible to other users without some SERIOUS design issues. It would lead to a free-for-all where nobody could trust the contents of the FE file.

This design sounds to me as though it is more than just a bit dangerous due to the potential for destructive interference.

1. People can't make any front end tables.
2. As convoluted and untrustworthy as this probably sounds, there are tables keeping track of the new tables!
3. The user never has to 'see' a table. They're not even aware they've created a table, per se - just that the object they've imported is now in the database, and available, to do things such as create safety data sheets and whatnot. It's the routines which create safety data sheets (as an example) that access the table; and the tables which keep track of this new import's existence tells it where it is.

 

[sneuberg]

This is the link that says something about having two databases open. I put that in the code below. GetBackendPath is a function we have that get the backend path on our system. You would need to replace this with your backend path. This code took about 20 milliseconds to run on our system with about 43 tables.

Sub T()

Dim dbFrontEnd As DAO.Database
Dim dbBackEnd As DAO.Database
Dim tdefFE As TableDef
Dim tdefBE As TableDef
Dim TableFound As Boolean

Set dbBackEnd = DBEngine.Workspaces(0).OpenDatabase([COLOR="blue"]GetBackendPath[/COLOR])
Set dbFrontEnd = CurrentDb
Dim St As Long

For Each tdefFE In dbFrontEnd.TableDefs
  TableFound = False
    For Each tdefBE In dbBackEnd.TableDefs
        If tdefBE.Name = tdefFE.Name Then
            TableFound = True
         End If
    Next tdefBE
    If Not TableFound Then
        Debug.Print tdefFE.Name & " was in the frontend but not the backend"
        If tdefFE.Connect <> "" Then
             Debug.Print tdefFE.Name & " was a linked table in the frontend but not the backend"
        End If
    End If
Next tdefFE
 
dbBackEnd.Close
dbFrontEnd.Close


End Sub

 

[LadyMarmalade]

This is the link that says something about having two databases open. I put that in the code below. GetBackendPath is a function we have that get the backend path on our system. You would need to replace this with your backend path. This code took about 20 milliseconds to run on our system with about 43 tables.

Sub T()

Dim dbFrontEnd As DAO.Database
Dim dbBackEnd As DAO.Database
Dim tdefFE As TableDef
Dim tdefBE As TableDef
Dim TableFound As Boolean

Set dbBackEnd = DBEngine.Workspaces(0).OpenDatabase([COLOR="blue"]GetBackendPath[/COLOR])
Set dbFrontEnd = CurrentDb
Dim St As Long

For Each tdefFE In dbFrontEnd.TableDefs
  TableFound = False
    For Each tdefBE In dbBackEnd.TableDefs
        If tdefBE.Name = tdefFE.Name Then
            TableFound = True
         End If
    Next tdefBE
    If Not TableFound Then
        Debug.Print tdefFE.Name & " was in the frontend but not the backend"
        If tdefFE.Connect <> "" Then
             Debug.Print tdefFE.Name & " was a linked table in the frontend but not the backend"
        End If
    End If
Next tdefFE
 
dbBackEnd.Close
dbFrontEnd.Close


End Sub

This is great! I can't believe I didn't think of that as it's basically the exact routine I'm doing to check if a table in the backend is not in the front end, just with the loops reversed....:banghead:

Quick question - is there any way to check if the table it finds is a system table? I've gathered that they seem to start with MSys or ~? All my tables have an underscore in them so that's a way to check but I'm worried there could be a system table with a _ in it!

Thanks,

Lady Marmalade

 

[CJ_London]

Quick question - is there any way to check if the table it finds is a system table? I've gathered that they seem to start with MSys

they do - but to be safe look at the tabledef properties for a property called 'attributes' if the property doesn't exist it will generate an error which in this context you can take to mean 'not a system object', otherwise the attribute value is 'System Object'.

If you use the documenter on a system table you can see it there

 

[sneuberg]

I didn't bother checking for system tables since I assumed that the same tables would alway be in both the frontend and backend and I don't think a system table would ever be a linked table. Maybe that isn't alway true. This web page has some code that displays the tabledef attributes. This code displays the table attributes without raising errors. It appears that system tables either have -2147483648 or 2 for an attribute and non system tables have 0.