Copy and Rename Access Table from Excel VBA

[Guest]

I would like to create a copy of an Access Table using VBA that is run in
Excel. In order to do this, I have tried the following:

Set db = OpenDatabase(ThisWorkbook.Path & "\Deterministic_VSM_v3.mdb")

db.DoCmd.CopyObject , Name, "acTable", ExistingName

Set db=Nothing

Whenever I execute this code I receive an error message that says: "Compile
Error: Method or Data Member not found." So, it looks like DoCmd cannot be
used in Excel's VBA. Does anybody know of a different way of doing this or if
it is even possible to copy Access tables from VBA that is located in Excel?

 

[Douglas J. Steele]

To honest, I don't know whether it'll work from Excel. However, your syntax
is incorrect. The 2nd argument is supposed to be the constant acTable, not a
string "acTable":

db.DoCmd.CopyObject , Name, acTable, ExistingName

If Excel doesn't recognize that constant, its value is 0:

db.DoCmd.CopyObject , Name, 0, ExistingName

 

[Dirk Goldgar]

To honest, I don't know whether it'll work from Excel. However, your
syntax is incorrect. The 2nd argument is supposed to be the constant
acTable, not a string "acTable":

db.DoCmd.CopyObject , Name, acTable, ExistingName

If Excel doesn't recognize that constant, its value is 0:

db.DoCmd.CopyObject , Name, 0, ExistingName

That won't work, because db is a DAO database object, not an Access
application object. James could either create an instance of Access and
automate that to call DoCmd.CopyObject, or else copy the table using all
DAO methods. That latter is a bit complicated (copying all indexes and
properties, even the Access-specific ones), but I know I once saw a
KnowledgeBase article containing the code.

 

[Dirk Goldgar]

That latter is a bit complicated (copying all
indexes and properties, even the Access-specific ones), but I know I
once saw a KnowledgeBase article containing the code.

Yes, here it is:

http://support.microsoft.com/kb/217011
How To Copy a DAO TableDef Including User-Defined
Properties

 

[Guest]

Thanks for the help. I will try this out.

James

Yes, here it is:

http://support.microsoft.com/kb/217011
How To Copy a DAO TableDef Including User-Defined
Properties

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the help. I will try this out.

Note that the function there copies the table *design*. After doing
that, you'll have to run an append query to copy the data from one table
to the other.

 

[Guest]

This works beautifully. Thanks!

Note that the function there copies the table *design*. After doing
that, you'll have to run an append query to copy the data from one table
to the other.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)