creating 100 table with wba 1

[yesilkalem]

lo I have to create lots of table using vba.

create table aaa
(
bbb char(8) not null,
ccc char(12),
ddd float,
fff char(2) not null,
ggg char(2) not null,

);


create table zxz
(
hhh float,
jjj float,
hhh char(2)
);


like this.

I have code but It does not work ..


please help..
thanks






Inappropriate post?
If so, Re

 

[MichaelRed]

the general syntax shown in your post resembles "C" language constructs, so perhaps it is simply in hte wrong form/language?

If, on the other hand, you actually want (or HAVE to) use VB(A), search the Ms. A. and VB fora, as there are several examples of generating tables via code. I would generally place the tables, fileds and properties in a table and use a couple of loops to do the actual work, as illustrated in the code below. Try "CreateTable" and CreateField"

Option Compare Database
Option Explicit

    Type Links
        tblName As String
        fldName As String
        rptName As String
        rptField As String
        rptStart As Integer
        rptLen As Integer
        FileNum As Integer
        StorName As String
    End Type
Public Function basDocuTbls()
    Dim dbs As Database
    Dim rst As Recordset
    Dim tblTables As Recordset
    Dim qryDelTblTables As QueryDef

    Dim Idx As Integer          'Table Names
    Dim Jdx As Integer          'Fields

    Dim strTableName(11) As String

    strTableName(1) = "tblInventory"
    strTableName(2) = "tblBookStore"
    strTableName(3) = "tblCourse"
    strTableName(4) = "tblDecision"
    strTableName(5) = "tblPublisher"
    strTableName(6) = "tblPurchOrd"
    strTableName(7) = "tblPurchOrdDet"
    strTableName(8) = "tblReceive"
    strTableName(9) = "tblReceiveDetails"
    strTableName(10) = "tblReturn"
    strTableName(11) = "tblReturnDet"

    Set dbs = CurrentDb
    Set qryDelTblTables = dbs.QueryDefs("qryDelTblTables")
    qryDelTblTables.Execute
    Set tblTables = dbs.OpenRecordset("tblTables")

    For Idx = 1 To UBound(strTableName)                 'Loop for each Table
        Set rst = dbs.OpenRecordset(strTableName(Idx))
        For Jdx = 0 To rst.Fields.Count - 1             'Loop for each field in the table
            With tblTables
                .AddNew
                    !tblName = rst.Name
                    !fldName = rst.Fields(Jdx).Properties("Name")
                    !fldType = rst.Fields(Jdx).Properties("Type")
                    !fldSize = rst.Fields(Jdx).Properties("Size")
                    !fldDescription = rst.Fields(Jdx).Properties("Description")
                .Update
            End With
        Next Jdx
    Next Idx

End Function

Although, in htis specific instance, the field names and properties are obtained from existing tabledef objects, the procedure is similar enough to one which would use a table to see the approach.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

A serious question here is why you would create so many tables? Just store all your data in one table and use a field to differentiate the data. Then you can query them apart to your heart's desire.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.