|
Subject:
|
Automatically create tables
|
|
Posted By:
|
Machiaveli280278
|
Post Date:
|
11/3/2003 3:04:40 AM
|
Hi everyone,
Who can help me to automatically create tables by using a table. I have a table where i store tabledefinitions such as the table name, fieldname, datatype, etcetera. What i would like to work is that table are created by the values of that table.
Example:
-in field1, i have the name of the table (e.g. tblOrders)
-in field2, i have the name of the fields (e.g. customerId)
-in field3, i have the datatype of the fieldname (e.g. numeric)
-in field4, i have the characterlength of the fieldname (e.g. long integer)
How can i automate this proces without manually create the tables.
Greetings
Mach
|
|
Reply By:
|
sal
|
Reply Date:
|
11/3/2003 9:48:31 AM
|
check this site for some sample code for the ADOX library. Also, you can look under ADO - ADOX in MS Access Help for more info.
http://www.4guysfromrolla.com/ASPscripts/PrintPage.asp?REF=/webtech/013101-1.shtml
Sal
|
|
Reply By:
|
Imar
|
Reply Date:
|
11/3/2003 2:20:14 PM
|
Hi Mach,
You may want to take a look at this article: http://Imar.Spaanjaars.com/QuickDocID.aspx?QUICKDOCID=143
It describes how to create tables from an ASP page, but the principle is the same for VB and VBA.
The code is written like this:
Dim catNewDB ' As ADOX.Catalog
So all you need to do is remove the ' to make it a strongly typed example.
HtH,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
pjm
|
Reply Date:
|
9/26/2006 4:20:48 PM
|
My first suggestion would be to make Field3 match the Access field types (ie. not "Numeric", but "Integer", "Long", "Double", etc.) and make Field4 the actual length. You really only need to store the length for text fields since you won't need to specify the length for the numeric datatypes.
Dim db As Database, rs As Recordset
Dim tdf As TableDef, fld As Field
Dim r As Long, nRecords As Long
Set db = CurrentDb()
rs = Openrecordset(DefinitionTableName, dbOpenDynaset)
rs.MoveLast
nRecords = rs.RecordCount
rs.MoveFirst
Set tdf = db.CreateTableDef(rs("Field1"))
For r = 1 To nRecords
if rs("Field3") = "Text" then
Set fld = tdf.CreateField(rs("Field2"), rs("Field3"), rs("Field4"))
else
Set fld = tdf.CreateField(rs("Field2"), rs("Field3"))
end if
tdf.Fields.Append fld
fld.AllowZeroLength = True
Next r
tdf.Fields.Refresh
db.TableDefs.Append tdf
db.TableDefs.Refresh
rs.Close
I realize that this code only allows for one table to be defined and there may be other things that I haven't accounted for, but I do have to leave you some things to do.
-Phil-
|
