Updating multiple tables 1

[Microbe]

Greetings,

I have a bunch of tables (identical) that I want to add a single field to.

Sure, I could go through them one at a time and do it, but it is pretty tedious. Is there a way of replicating a field across tables?

Even better, is there any way of creating a "table template" on which to base tables so that updating the template causes all tables based on it to also update.

Or am I just asking too much of Access )

Thanks in advance

Steve

Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au

 

[PaulF]

These functions will loop thru the MsysObjects table and when they encounter an entry with a Type of 1 (Non-Linked Table) and whose name doesn't start with "Msys" (system table), it will add a new field, or delete a field depending on which one you use. If you only want to update certain tables and not all tables (excluding Linked and System tables), make a new table with all of the table names you want to update, then instead of looping thru the MsysObjects table, loop thru this new table. Then change the code to exclude this table in the update process. To use the Add function, just pass a line of code such as

AddTableField("TestTextField","dbText",30)
or
AddTableField("TestYesNoField","1&quot

To Delete a Field just pass a line of code such as

DeleteTableField("TextTextField&quot

Public Function AddTableField(strField, strType As String, Optional intLength)
Dim db As DAO.database, rst As DAO.Recordset
Dim tdf As TableDef, varType As Integer
Select Case strType
Case "1", "dbBoolean", "Boolean"
varType = 1
Case "2", "dbByte", "Byte"
varType = 2
Case "3", "dbInteger", "Integer"
varType = 3
Case "4", "dbLong", "Long", "Numeric", "dbNumeric"
varType = 4
Case "5", "dbCurrency", "Currency"
varType = 5
Case "6", "dbSingle", "Single"
varType = 6
Case "7", "dbDouble", "Double"
varType = 7
Case "8", "dbDate", "Date", "Time", "Date/Time"
varType = 8
Case "9", "dbBinary", "Binary"
varType = 9
Case "10", "dbText", "Text", "String"
varType = 10
Case "11", "dbLongBinary", "OLE"
varType = 11
Case "12", "dbMemo", "Memo"
varType = 12
Case Else
varType = 10
End Select
Set db = CurrentDb
Set rst = db.OpenRecordset("MsysObjects&quot
With rst
.MoveFirst
Do While Not .EOF
If rst!Type = 1 And Left(rst!Name, 4) <> &quot;Msys&quot; Then
Set tdf = db.TableDefs(rst!Name)
With tdf
On Error Resume Next 'In case field already exists
.Fields.Append .CreateField(strField, varType, intLength)
End With
End If
.MoveNext
Loop
End With
End Function


Public Function DeleteTableField(strField As String)
Dim db As DAO.database, rst As DAO.Recordset
Dim tdf As TableDef
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;MsysObjects&quot
With rst
.MoveFirst
Do While Not .EOF
If rst!Type = 1 And Left(rst!Name, 4) <> &quot;Msys&quot; Then
Set tdf = db.TableDefs(rst!Name)
With tdf
On Error Resume Next ' in case field doesn't exist
.Fields.Delete (strField)
End With
End If
.MoveNext
Loop
End With

End Function


HTH
PaulF