Code to change a table definition

Hi
I've borrowed some of the audit trail code that some very
kind people have previously posted up here. I have it
working very well, but with 1 problem.
It works by creating atemporary audit table, that on
commit of the data, posts it to a permenant audit table.
The temporary table is created at runtim using the code
you see below.
Problem - the form that the audit functions are attached
to sometimes have field values deleted, which is fine,
howver the temp audit table has its proprty set to Not
allow zero lenght strings.

Having not written the code myself (and still on a VBA
learning curve), how can I change the settings of a
couple of fields in this temporary space to allow zero
lenght strings? I need to add it somwhere in the code
below, that creates the temp space.
Thanks
Tim
************************************************** ******
ption Compare Database
Option Explicit

Sub CreateTempDB()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'MsgBox Directory 'used to test which directory is being
used

'delete the created file
Kill Directory & "AuditTemp.mdb"

'recreate file
Set dbNew = CreateDatabase(Directory & "AuditTemp",
dbLangGeneral)
dbNew.Close

'copy local copy of 'Detail' to new database
DoCmd.CopyObject Directory
& "AuditTemp.mdb", "rAuditTemp",
acTable, "AuditTableBlank"

'reattach table from new database
Set tbl = db.CreateTableDef("rAuditTemp")
tbl.Connect = (";DATABASE=" & Directory & "AuditTemp.mdb")
tbl.SourceTableName = "rAuditTemp"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub

Sub DestroyTempDB()
On Error GoTo Err_DestroyTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'delete the created file
Kill Directory & "AuditTemp.mdb"

Exit_DestroyTempDB:
Exit Sub

Err_DestroyTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_DestroyTempDB
End If

End Sub

Sub LinkAuditTable()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "AuditTable"

'create string for drive where program exists
Directory = GetPath(db.Name)


'reattach table from new database
Set tbl = db.CreateTableDef("AuditTable")
tbl.Connect = (";DATABASE=" & Directory
& "AuditTableRemote2k.mdb")
tbl.SourceTableName = "AuditTable"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs("SomeTable")
Set fld = tdf.Fields("SomeField")
fld.AllowZeroLength = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim" wrote in message
...
Hi
I've borrowed some of the audit trail code that some very
kind people have previously posted up here. I have it
working very well, but with 1 problem.
It works by creating atemporary audit table, that on
commit of the data, posts it to a permenant audit table.
The temporary table is created at runtim using the code
you see below.
Problem - the form that the audit functions are attached
to sometimes have field values deleted, which is fine,
howver the temp audit table has its proprty set to Not
allow zero lenght strings.

Having not written the code myself (and still on a VBA
learning curve), how can I change the settings of a
couple of fields in this temporary space to allow zero
lenght strings? I need to add it somwhere in the code
below, that creates the temp space.
Thanks
Tim
************************************************** ******
ption Compare Database
Option Explicit

Sub CreateTempDB()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'MsgBox Directory 'used to test which directory is being
used

'delete the created file
Kill Directory & "AuditTemp.mdb"

'recreate file
Set dbNew = CreateDatabase(Directory & "AuditTemp",
dbLangGeneral)
dbNew.Close

'copy local copy of 'Detail' to new database
DoCmd.CopyObject Directory
& "AuditTemp.mdb", "rAuditTemp",
acTable, "AuditTableBlank"

'reattach table from new database
Set tbl = db.CreateTableDef("rAuditTemp")
tbl.Connect = (";DATABASE=" & Directory & "AuditTemp.mdb")
tbl.SourceTableName = "rAuditTemp"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub

Sub DestroyTempDB()
On Error GoTo Err_DestroyTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'delete the created file
Kill Directory & "AuditTemp.mdb"

Exit_DestroyTempDB:
Exit Sub

Err_DestroyTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_DestroyTempDB
End If

End Sub

Sub LinkAuditTable()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "AuditTable"

'create string for drive where program exists
Directory = GetPath(db.Name)


'reattach table from new database
Set tbl = db.CreateTableDef("AuditTable")
tbl.Connect = (";DATABASE=" & Directory
& "AuditTableRemote2k.mdb")
tbl.SourceTableName = "AuditTable"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub

Where abouts does that fit in with the code I've provided?
I've tried around the 'recreate file
area, but keep getting 'Object variable not set' errors
Cheers
Tim


-----Original Message-----
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs("SomeTable")
Set fld = tdf.Fields("SomeField")
fld.AllowZeroLength = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim" wrote in
message
...
Hi
I've borrowed some of the audit trail code that some
very
kind people have previously posted up here. I have it
working very well, but with 1 problem.
It works by creating atemporary audit table, that on
commit of the data, posts it to a permenant audit
table.
The temporary table is created at runtim using the code
you see below.
Problem - the form that the audit functions are
attached
to sometimes have field values deleted, which is fine,
howver the temp audit table has its proprty set to Not
allow zero lenght strings.

Having not written the code myself (and still on a VBA
learning curve), how can I change the settings of a
couple of fields in this temporary space to allow zero
lenght strings? I need to add it somwhere in the code
below, that creates the temp space.
Thanks
Tim

************************************************** ******
ption Compare Database
Option Explicit

Sub CreateTempDB()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'MsgBox Directory 'used to test which directory is
being
used

'delete the created file
Kill Directory & "AuditTemp.mdb"

'recreate file
Set dbNew = CreateDatabase(Directory & "AuditTemp",
dbLangGeneral)
dbNew.Close

'copy local copy of 'Detail' to new database
DoCmd.CopyObject Directory
& "AuditTemp.mdb", "rAuditTemp",
acTable, "AuditTableBlank"

'reattach table from new database
Set tbl = db.CreateTableDef("rAuditTemp")
tbl.Connect = (";DATABASE=" & Directory
& "AuditTemp.mdb")
tbl.SourceTableName = "rAuditTemp"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub

Sub DestroyTempDB()
On Error GoTo Err_DestroyTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'delete the created file
Kill Directory & "AuditTemp.mdb"

Exit_DestroyTempDB:
Exit Sub

Err_DestroyTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_DestroyTempDB
End If

End Sub

Sub LinkAuditTable()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "AuditTable"

'create string for drive where program exists
Directory = GetPath(db.Name)


'reattach table from new database
Set tbl = db.CreateTableDef("AuditTable")
tbl.Connect = (";DATABASE=" & Directory
& "AuditTableRemote2k.mdb")
tbl.SourceTableName = "AuditTable"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub


.

Have not gone through your process, but that's the process for setting the
property of the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim" wrote in message
...
Where abouts does that fit in with the code I've provided?
I've tried around the 'recreate file
area, but keep getting 'Object variable not set' errors
Cheers
Tim


-----Original Message-----
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs("SomeTable")
Set fld = tdf.Fields("SomeField")
fld.AllowZeroLength = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim" wrote in
message
...
Hi
I've borrowed some of the audit trail code that some
very
kind people have previously posted up here. I have it
working very well, but with 1 problem.
It works by creating atemporary audit table, that on
commit of the data, posts it to a permenant audit
table.
The temporary table is created at runtim using the code
you see below.
Problem - the form that the audit functions are
attached
to sometimes have field values deleted, which is fine,
howver the temp audit table has its proprty set to Not
allow zero lenght strings.

Having not written the code myself (and still on a VBA
learning curve), how can I change the settings of a
couple of fields in this temporary space to allow zero
lenght strings? I need to add it somwhere in the code
below, that creates the temp space.
Thanks
Tim

************************************************** ******
ption Compare Database
Option Explicit

Sub CreateTempDB()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'MsgBox Directory 'used to test which directory is
being
used

'delete the created file
Kill Directory & "AuditTemp.mdb"

'recreate file
Set dbNew = CreateDatabase(Directory & "AuditTemp",
dbLangGeneral)
dbNew.Close

'copy local copy of 'Detail' to new database
DoCmd.CopyObject Directory
& "AuditTemp.mdb", "rAuditTemp",
acTable, "AuditTableBlank"

'reattach table from new database
Set tbl = db.CreateTableDef("rAuditTemp")
tbl.Connect = (";DATABASE=" & Directory
& "AuditTemp.mdb")
tbl.SourceTableName = "rAuditTemp"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub

Sub DestroyTempDB()
On Error GoTo Err_DestroyTempDB
Dim Directory As String

Dim dbNew As Database, db As Database
Dim tbl As TableDef, length As Integer
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "rAuditTemp"

'create string for drive where program exists
Directory = GetPath(db.Name)

'delete the created file
Kill Directory & "AuditTemp.mdb"

Exit_DestroyTempDB:
Exit Sub

Err_DestroyTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_DestroyTempDB
End If

End Sub

Sub LinkAuditTable()
On Error GoTo Err_CreateTempDB
Dim Directory As String

Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb

'delete the linked table
db.TableDefs.delete "AuditTable"

'create string for drive where program exists
Directory = GetPath(db.Name)


'reattach table from new database
Set tbl = db.CreateTableDef("AuditTable")
tbl.Connect = (";DATABASE=" & Directory
& "AuditTableRemote2k.mdb")
tbl.SourceTableName = "AuditTable"
db.TableDefs.Append tbl

Exit_CreateTempDB:
Exit Sub

Err_CreateTempDB:
If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
ElseIf Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateTempDB
End If

End Sub


.