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

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/ASPscr...013101-1.shtml





Sal

Hi Mach,

You may want to take a look at this article: http://Imar.Spaanjaars.com/QuickDocI...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.

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-