how to delete an object in another db

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wez.k - 22 Jun 2007 22:14 GMT

Hi

Could anyone tell me if its possible to delete an object such as a table,
form, query etc in another database, and if so how is it done?

Thanks for any help.

Reply to this Message

OldPro - 22 Jun 2007 22:30 GMT

> Hi
>
> Could anyone tell me if its possible to delete an object such as a table,
> form, query etc in another database, and if so how is it done?
>
> Thanks for any help.

Use the OpenDatabase Method to get a pointer to the 'external'
database. You
could the DeleteObject Method to delete a form.

Reply to this Message

Wez.k - 22 Jun 2007 22:58 GMT

> > Hi
> >
[quoted text clipped - 8 lines]
>
> Thanks for your reply. However I cant find anything in the help files for the OpenDatabase Method. Could you tell me how to implement this?

Reply to this Message

Ed Metcalfe - 22 Jun 2007 22:34 GMT

> Hi
>
> Could anyone tell me if its possible to delete an object such as a table,
> form, query etc in another database, and if so how is it done?
>
> Thanks for any help.

Wez,

For tables you can open the database and use a drop table SQL statement,
something like this (untested):

Dim MyDB as DAO.Database

Set MyDB=OpenDatabase(<DB Path Here>)

MyDB.Execute "DROP TABLE <Table Name Here>;", dbFailOnError

MyDB.Close
Set MyDB=Nothing

Not sure about forms, reports etc.

Ed Metcalfe.

Reply to this Message

Wez.k - 22 Jun 2007 22:56 GMT

> > Hi
> >
[quoted text clipped - 22 lines]
>
> Many thanks, this works fine for tables but as you suspected not for the other objects. If possible I need to be able to delete forms, queries and reports too.

Reply to this Message

Marshall Barton - 22 Jun 2007 22:39 GMT

>Could anyone tell me if its possible to delete an object such as a table,
>form, query etc in another database, and if so how is it done?

Open the other mdb file and operate on its db object

Dim otherDb As Database
Set otherDb = OpenDatabase("path to other mdb file")

otherDb.TableDefs,Delete "sometable"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Wez.k - 22 Jun 2007 23:00 GMT

> >Could anyone tell me if its possible to delete an object such as a table,
> >form, query etc in another database, and if so how is it done?
[quoted text clipped - 5 lines]
>
> otherDb.TableDefs,Delete "sometable"

Thanks for your reply. However there seems to be a syntax error with the
TableDefs line because the code window is showing it in red. Also, will this
method work for forms, queries and reports?

Reply to this Message

Marshall Barton - 22 Jun 2007 23:49 GMT

>> >Could anyone tell me if its possible to delete an object such as a table,
>> >form, query etc in another database, and if so how is it done?
[quoted text clipped - 9 lines]
>TableDefs line because the code window is showing it in red. Also, will this
>method work for forms, queries and reports?

I don't see what you tried, so I can't spot any syntax
errors.

The Delete method will also work for queries, but not for
forms or reports.

It might be better if you used Automation so you can employ
SelectObject and DeleteObject.

OTOH, This is starting to sound like an attempt to update an
existing database with modified forms, reports, etc. If so,
it is not a good idea. Instead you should distribute a new
front end mdb and use SQL DDL statements to modify the
backend tables.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Douglas J. Steele - 23 Jun 2007 01:36 GMT

Slight typo. Marsh put comma Delete rather than period Delete.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>>> >Could anyone tell me if its possible to delete an object such as a
>>> >table,
[quoted text clipped - 26 lines]
> front end mdb and use SQL DDL statements to modify the
> backend tables.

Reply to this Message

Marshall Barton - 23 Jun 2007 02:20 GMT

>Slight typo. Marsh put comma Delete rather than period Delete.

Oh my. I have to enlarge my font - again. It's getting
difficult to see the difference.

Thanks for catching that Doug.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Wez.k - 24 Jun 2007 18:02 GMT

Thanks for your help everyone and sorry if I've wasted your time. Having
banged my head on it for a couple of days, I have now realised that I dont
have to do this at all, there's a much simpler way of achieving what I want
to do and I already know how to do it! Sometimes I cant see the wood for the
trees...

Regards.

Reply to this Message