MLH wrote:
What might a FN look like to put the following lines in if such
FN accepted an array of desired fieldnames and fieldtypes?
I would like to have the functional lines that follow in a FN
that accepted an array of data that looked like this:
"VehicleJobID", dbLong
"WannaBill", dbBoolean
10 If TableOrQueryExists("", "TempTable") Then DoCmd.DeleteObject
acTable, "TempTable"
20 Dim dbs As Database, tdf As TableDef, fld As Field
30 Set dbs = CurrentDb
40 Set tdf = dbs.CreateTableDef("TempTable")
50 Set fld = tdf.CreateField("VehicleJobID", dbLong)
60 tdf.Fields.Append fld
70 Set fld = tdf.CreateField("WannaBill", dbBoolean)
80 tdf.Fields.Append fld
90 tdf.Fields.Refresh
100 dbs.TableDefs.Append tdf
110 dbs.TableDefs.Refresh
120 Set dbs = Nothing
130 Set tdf = Nothing
I'm sure lines 50, 60, 70 and 80 will change - to what, I don't know.
Abstract out a little.... something like this... (test first...)
I'm passing in an array of (Fields(column 0) and Types (column 1)) so I
can process the lot inside the routine. then I can just loop through
the array and do the field creation and appending the field to the
table.
public sub AddFieldsToTable(byval strTable as string, byval
arrFieldsAndTypes As Variant)
dim intCounter as integer
10 If TableOrQueryExists("", strTable) Then DoCmd.DeleteObject
acTable, strTable
20 Dim dbs As Database, tdf As TableDef, fld As Field
30 Set dbs = CurrentDb
40 Set tdf = dbs.CreateTableDef(strTable)
for intCounter = lbound(arrFieldsAndTypes) to ubound(arrFieldsAndtypes)
50 Set fld = tdf.CreateField(arrFieldsAndTypes(intCounter,0),
arrFieldsAndtypes(intCounter,1)
60 tdf.Fields.Append fld
next intCounter
90 tdf.Fields.Refresh
100 dbs.TableDefs.Append tdf
110 dbs.TableDefs.Refresh
120 Set dbs = Nothing
130 Set tdf = Nothing
End Sub
