Deleting a table in another MDB

[G Gerard]

Hello

I can create a table in another database (AnotherMdb) by running the
following SQL from the main database:

SELECT Table1.* INTO NewTable IN "C:/AnotherMdb.mdb" FROM Table1

How would I then delete the NewTable object located in AnotherMdb.mdb from
the main database?

I an using MSAccess 2000

Thanks

G Gerard

[Rich P]

Greetings,

I was not able to drop a table in a foreign DB using sql, but I was able
to do it using DAO code:
-----------------------------------------------
Dim db As DAO.Database, tdf As TableDef

Set db = DBEngine.OpenDatabase("C:\CodeTest\db2test.mdb")
For Each tdf In db.TableDefs
If tdf.Name = "tblxyz942" Then
db.TableDefs.Delete tdf.Name
Exit For
End If
Next
--------------------------------------------------
HTH,
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Lyle Fairfield]

G Gerard wrote:

Hello

I can create a table in another database (AnotherMdb) by running the
following SQL from the main database:

SELECT Table1.* INTO NewTable IN "C:/AnotherMdb.mdb" FROM Table1

How would I then delete the NewTable object located in AnotherMdb.mdb from
the main database?

I an using MSAccess 2000

Thanks

G Gerard

DBEngine(0)(0).Execute "DROP TABLE [C:\AnotherMdb.mdb].[Table1]"

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:lO***************@news.uswest.net:

I was not able to drop a table in a foreign DB using sql, but I
was able to do it using DAO code:
-----------------------------------------------
Dim db As DAO.Database, tdf As TableDef

Set db = DBEngine.OpenDatabase("C:\CodeTest\db2test.mdb")
For Each tdf In db.TableDefs
If tdf.Name = "tblxyz942" Then
db.TableDefs.Delete tdf.Name
Exit For
End If
Next
--------------------------------------------------

Why not just do this:

Set db = DBEngine.OpenDatabase("C:\CodeTest\db2test.mdb")
db.TableDefs.Delete "tblxyz942"

Why the For/Each loop?

The only reason I can think of would be to avoid an error, but the
error is easily trapped for.

On the other hand, your method does have the beauty of avoiding
raising an error, which I consider a good thing.

On the issue of DDL, I can't see why you couldn't execute DDL SQL to
drop a table in a foreign database using DAO, or just using an IN
'[database path/name]' clause using the local database object.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rich P]

Yes. I did not try this:

DoCmd.RunSQL "Drop Table [C:\testmdb\db1.mdb].tbl123x"

this did work fine. It was the square brackets that I was missing
earlier.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:Yi***************@news.uswest.net:

Yes. I did not try this:

DoCmd.RunSQL "Drop Table [C:\testmdb\db1.mdb].tbl123x"

this did work fine. It was the square brackets that I was missing
earlier.

I didn't know that worked! I thought you'd have to use:

Drop Table tbl123x IN 'C:\testmdb\db1.mdb'

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rich P]

I just tried

Drop Table tbl123x IN 'C:\testmdb\db1.mdb'

but did not work. I also tried

Drop Table tbl123x IN [C:\testmdb\db1.mdb]

and

Drop Table tbl123x IN ('C:\testmdb\db1.mdb')

--------------------------------------------------
the only syntax that is working on my workstation is

Drop Table [C:\testmdb\db1.mdb].tbl123x


Rich

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:N2***************@news.uswest.net:

I just tried

Drop Table tbl123x IN 'C:\testmdb\db1.mdb'

but did not work. I also tried

Drop Table tbl123x IN [C:\testmdb\db1.mdb]

and

Drop Table tbl123x IN ('C:\testmdb\db1.mdb')

--------------------------------------------------
the only syntax that is working on my workstation is

Drop Table [C:\testmdb\db1.mdb].tbl123x

Did you try:

Dim db As DAO.Database

Set db = DbEngine.OpenDatabase("C:\testmdb\db1.mdb")

db.Execute "Drop Table tbl123x", dbFailOnError

db.Close
Set db = Nothing

I can't see why that wouldn't have worked.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/