Okay, I think the following code accomplishes what you wanted.
' ------Begin Code------------------
Option Compare Database
Option Explicit
' This code requires a reference to the Microsoft DAO 3.6 Object Library
Sub CopyTableStructure()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim oFld As DAO.Field
Dim tdfNew As TableDef
Set db = CurrentDb()
' Open rs1 on the Employees table
Set rs1 = db.OpenRecordset("Employees")
' Create a new table.
Set tdfNew = db.CreateTableDef("MyNewEmployeesTable")
With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection.
For Each oFld In rs1.Fields
.Fields.Append .CreateField(oFld.Name, oFld.Type, oFld.Size)
Next oFld
End With
' Append the new TableDef object to the database.
db.TableDefs.Append tdfNew
'Refresh database window
RefreshDatabaseWindow
ExitProc:
On Error Resume Next ' Clean up
rs1.Close
db.Close
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in CopyTableStructure procedure..."
Resume ExitProc
End Sub
' ------End Code------------------
Tom
___________________________________________
This is one of the most common errors experienced by Access 2000 and 2002 users. The
reason is that there is no reference to the DAO Object Library included by default. The
code you supplied is DAO code. In your code module, click on Tools > References. Select
Microsoft DAO 3.6 Object Library.
Then make the following changes to Bogdan's code, to "disambiguate" the declarations (ie.
avoid library reference priority issues--which one is listed first):
Change:
dim db as database, rs1 as recordset, fld as field, rs2 as recordset
to:
Dim db as DAO.Database
Dim rs1 as DAO.Recordset
Dim fld as DAO.Field
Dim rs2 as DAO.Recordset
I think it makes your code more readable to dimension each object on a separate line,
although this is not really required. You don't really need to disambiguate the first
line, since there is no Database object in the ADO library. However, it does make your
code more readable in my opinion.
This should solve the first error. However, I really can't make sense out of the rest of
his code....sorry. He is using two undeclared variables, "rs" and "oFld", in the loop.
Perhaps he meant rs1 and/or rs2, and he meant to declare oFld instead of fld....don't
really know. He may have gotten away with this if he wasn't using Option Explicit, which
means that these new variables were created as variants.....
Tom
_____________________________________
I tried this on Access 2000, and get an error on the first line, saying
'User defined type not defined'
Please help.
_____________________________________
You should create the table with the fields you want:
Fieldname - text
Fieldtype - integer
Fieldsize - integer
Suppose you name it TableStruc
Then you should use a code as follows
dim db as database, rs1 as recordset, fld as field, rs2 as recordset
set db = currentdb
' to be sure you don't merge two tables structure
db.execute "Delete from TableStruc"
set rs1 = db.openrecordset("YourTableName")
set rs2 = db.openrecordset("TableStruc")
for each oFld in rs.Fields
rs.AddNew
rs!FieldName = oFld.Name
rs!FieldType = oFld.Type
rs!FieldSize = oFld.Size
rs.Update
next
Regards,
Bogdan
_____________________________
Freelance developer
Hi,
Does anyone know how to take the table design view and
turn that into its own table? I would like the field
name, data type, and description to be viewed in a new
table (in the datasheet view), without me having to retype
everything into a new table.
Thanks,
Debbie
