VB Forum / Database Access / May 2008

While this is far easier in ADO.NET (SqlBulkCopy method) it can also be done
in VB6--but I wouldn't. ADO classic was never intended to act as a bulk data
interface. I suggest checking out SQL DMO/SMO as well as SSIS and the BULK
COPY TSQL calls or utilities. THESE are designed to do what you need
done--and far, far faster than any ADO application.

Well. There are two other options.

1) Use DAO instead of ADO. For a local mdb, DAO will be faster and simpler
than ADO.
"How To Copy a DAO TableDef Including User-Defined Properties"
http://support.microsoft.com/kb/q217011/

Here's another sample of the simplicity:
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
      ' some sort of filter for what you want ...
      ' here where are just going to skip over the system tables
   If Not Left$(tdf.Name, 4) = "MSys" Then
       DoCmd.TransferDatabase
           transfertype:=acExport, _
           DatabaseType:="Microsoft Access", _
           DatabaseName:="C:\Test.mdb", _
           ObjectType:=acTable, _
           Source:=tdf.Name, _
           Destination:=tdf.Name, _
           StructureOnly:=True
   End If
Next tdf

DAO, Jet and MSAccess were made for each other. (Literally. <g>)
You don't have to use DAO exclusively in your app. It is ok to use DAO for
the 'DDL' and Maintenance work and ADO for the fetch 'n carry work. Just try
and avoid chewing at same time on the same item with two different models.
<g>

2) If you just have your heart set on ADO then use ADOX - "'Microsoft ADO
Ext. 2.6 for DDL and Security".

Equally straight forward - looks very much like the DAO model only with
different names. Plenty of examples on Google of how to go about using it.

There is a "Gotcha"! Although well explained in the docs it seems to bite
everyone the first time out. So here's a "heads up" - to save some
aggravation, and save you a trip back here. <g>

When you are creating a new Field, many properties beyond the basics (ie,
name size, type, ...) are not available until the Field is appended to a
Fields collection. So it usually takes two passes to set up all the
properties.

Create with basic properties, append, circle back and set extended
properties.

[Makes sense if you think about it. How does a Field know it has a Scale
property, until it first learns it is a Number? <g>]

hth
-ralph