If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Code to change a table definition
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 |
#3
|
|||
|
|||
Code to change a table definition
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 . |
#4
|
|||
|
|||
Code to change a table definition
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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
customizing "documentor" output | W Garrard | Database Design | 12 | May 24th, 2004 03:07 AM |
Change Default Pivot Table Summary | Larry Kayser | Worksheet Functions | 1 | March 24th, 2004 09:54 PM |