Deleting table in another database

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Is it possible to delete a table via code in another mdb,
and is so, could someone please provide me or point me in
the direction of some sample code to do this.

Thanks in advance.

Mark
 
Mark said:
Is it possible to delete a table via code in another mdb,
and is so, could someone please provide me or point me in
the direction of some sample code to do this.
Click to expand...


You have to use OpenDatabase to get to the other database's
TableDefs collection:

Dim dbOther As DAO.Database
Set dbOther = OpenDatabase("path to other mdb file")
dbOther.TableDefs.Delete "nameoftable"
dbOther.Close
Set dbOther = Nothing
 
Marsh,

Thanks for the help. To expand on that, how do I
manipulate/create a primary key then in a table in
another db.

Maybe it would be easier if I explained what I'm trying
to do. Currently I'm pulling down 1/2 million+ records
from an Oracle server. In doing so, I can pull the
records if I use a Make Table query, but I get an ODBC
error when I use an Append query.

To get around this, I created a temporary table and then
appended the records from there to my indexed table. But
I bulk at running two queries, when I should be able to
do it in one.

So what I was thinking of doing was deleting the indexed
table, running the Make table query and then creating the
indexes and primary keys again.

Any thought's?

Thanks again.

Mark
 
Mark said:
Thanks for the help. To expand on that, how do I
manipulate/create a primary key then in a table in
another db.

Maybe it would be easier if I explained what I'm trying
to do. Currently I'm pulling down 1/2 million+ records
from an Oracle server. In doing so, I can pull the
records if I use a Make Table query, but I get an ODBC
error when I use an Append query.

To get around this, I created a temporary table and then
appended the records from there to my indexed table. But
I bulk at running two queries, when I should be able to
do it in one.

So what I was thinking of doing was deleting the indexed
table, running the Make table query and then creating the
indexes and primary keys again.
Click to expand...


I know nothing about Oracle stuff so I may not be any help,
but I don't see how an append can fail when a make table
succeeds unless there is an index violation or a data type
mismatch. I would try to do the append by hand (from the
File - Import menu) to see if I could get more info on
what's going wrong. The reason I wouldn't jump into
recreating the indexes immediately is because you'll likely
get the same violation adding the indexes as you're getting
with the append query.

In any case, you can Execute an ALTER TABLE query with a
CONSTRAINT clause to recreate the indexes. Check Help for
SQL DDL statements for details. Or alternatively, you can
use the DAO CreateIndex method.
--
Marsh
MVP [MS Access]


 
Marsh,

Thanks again, as for the problem appending to a table
with Oracle, I sent several days trying to resolve the
issue with no success. The message I was getting was
record deleted, which doesn't make sense for the table I
was pulling from, but hey that's Oracle.

Have a great weekend.

Mark
-----Original Message-----
Mark said:
Thanks for the help. To expand on that, how do I
manipulate/create a primary key then in a table in
another db.

Maybe it would be easier if I explained what I'm trying
to do. Currently I'm pulling down 1/2 million+ records
from an Oracle server. In doing so, I can pull the
records if I use a Make Table query, but I get an ODBC
error when I use an Append query.

To get around this, I created a temporary table and then
appended the records from there to my indexed table. But
I bulk at running two queries, when I should be able to
do it in one.

So what I was thinking of doing was deleting the indexed
table, running the Make table query and then creating the
indexes and primary keys again.
Click to expand...


I know nothing about Oracle stuff so I may not be any help,
but I don't see how an append can fail when a make table
succeeds unless there is an index violation or a data type
mismatch. I would try to do the append by hand (from the
File - Import menu) to see if I could get more info on
what's going wrong. The reason I wouldn't jump into
recreating the indexes immediately is because you'll likely
get the same violation adding the indexes as you're getting
with the append query.

In any case, you can Execute an ALTER TABLE query with a
CONSTRAINT clause to recreate the indexes. Check Help for
SQL DDL statements for details. Or alternatively, you can
use the DAO CreateIndex method.
--
Marsh
MVP [MS Access]


me
in
Click to expand...

.
Click to expand...
 
Back
Top