Code to change a table definition

[Tim]

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

 

[Allen Browne]

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

 

[Tim]

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

 

[Allen Browne]

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