Deleting a table in the backend using VBA

[Giem]

Hey all,

I am trying to delete a table in the backend database using code in the
button on the frontend.
I know there was a post already answered concerning this, the answer using
this example:

Dim dbCurr As DAO.Database

Set dbCurr = OpenDatabase("E:\Folder\BackEnd.mdb")
dbCurr.TableDefs.Delete "MyOldTable"

My problem is, it's not actually deleting the table. I know this because
afterward there is a query executed that remakes the table. It gives me the
error "Table 'X' already exists." If you open the backend after stepping past
the delete part, it is still there as well, And I am making sure the backend
is not open or locked in any way when this is running.

I was also wondering about DeleteObject, having the backend database checked
as a library reference. As I understood, Access will look at a library object
first with certain syntax? There is a link to this table in question in the
frontend, for querys used in the frontend afterwards. I want to avoid getting
that included in the mix.

Anyway, an explanation of what could be missing on my part for the DAO
method, alternatives to the DAO method, making a make table query to just
shut up and do it and wipe the old one, or another way of doing it altogether
is greatly appreciated.

 

[Douglas J. Steele]

That code should work.

Are you sure that the code is actually running?

Are you sure that that's the correct database to which your front-end is
linked?

 

[Jeff Boyce]

You've described "how" you want to do something (delete a back-end table
from a front-end). If you'll provide a bit more specific description of
"why" and "what", the folks here in the newsgroup may be able to offer
alternative (and more specific) suggestions.

For example, if you need a place to temporarily store an intermediate work
product, you could create (in the back-end) a "temp" table, then use queries
in the front-end to clear it out (delete query) and reload it with the new
set of data (append query).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Giem]

It appears to be running. If I put a breakpoint before it and step through,
it goes through it and appears to execute it. I can't find anything obvious.

In the definitions of the subroutine I have:

Dim dbCurr As DAO.Database

In the body of the subroutine I have:

Set dbCurr = OpenDatabase("\\server\share\giem1.mdb")
dbCurr.TableDefs.Delete "tblExplodedBOM"

giem1.mdb is the backend, and tblExplodedBOM is the table in question. I
only changed the server directory to something generic for this post.

I have access to this backend, but maybe there is some other constraint
causing a problem across a network? Still scratching my head. Hopefully this
helps. If it makes sense that this should work I'm wondering if I may have a
problem beyond the scope of this newsgroup.

 

[Giem]

Sure. The table holds the output of a recursion routine that appends each
subcomponent of a BOM as it drills down into the BOM (Bill of materials).
After this is finished the table can be manipulated further for different
uses. I have been working on some tests changing some of the stand-alone
databases into frontend/backend types. Currently this routine deletes and
then recreates the table prior to using it for the next drilldown. I hope
this helps, and thanks.

 

[Jeff Boyce]

So, rather than deleting and recreating the entire table, consider using a
delete query to empty it and an append query to reload it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Christy Wyatt]

--
Christy Wyatt

I like to use the Make Table. It automatically wipes out the old table.

 

[Giem]

Thanks guys.