I'm using Allen Brown's code for audit logging
( http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud
table doesn't populate with the tracking info at all. I think it might be a
problem with the table set-up. I just can't find the problem. These are
the fields in my table:
Table1
ID (primary key) AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTmpTable1
audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTable1
audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audID (primary key) AutoNumber
I didn't substitute anything in the code with audID, I just used 'ID' in
place of InvoiceID (I'm not sure what audID is for). I commented out the
error handler and checked the code line by line. I'm getting a runtime
error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL."
When I comment that out, I also get other errors with db.Execute sSQL
(Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does
anyone have any suggestions?
Thanks,
Brad 4 2733
On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton"
<br***********@ comcast.net> wrote:
What is the value of sSQL at that time?
-Tom. I'm using Allen Brown's code for audit logging (http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud table doesn't populate with the tracking info at all. I think it might be a problem with the table set-up. I just can't find the problem. These are the fields in my table:
Table1
ID (primary key) AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTmpTable1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTable1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo audID (primary key) AutoNumber
I didn't substitute anything in the code with audID, I just used 'ID' in place of InvoiceID (I'm not sure what audID is for). I commented out the error handler and checked the code line by line. I'm getting a runtime error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL." When I comment that out, I also get other errors with db.Execute sSQL (Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does anyone have any suggestions? Thanks,
Brad
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
This is the function:
Function AuditEditEnd(sT able As String, sAudTmpTable As String, sAudTable As
String, _
sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As
Boolean
On Error GoTo Err_AuditEditEn d
'Purpose: Write the audit trail to the audit table.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's AfterUpdate event. Example:
' Call AuditEditEnd("t blInvoice", "audTmpInvoice" ,
"audInvoice ", "InvoiceID" , Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database
Dim sSQL As String
Set db = DBEngine(0)(0)
If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser )
" & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable
& ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " &
sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser )
" & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName () AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True
Exit_AuditEditE nd:
Set db = Nothing
Exit Function
Err_AuditEditEn d:
Call LogError(Err.Nu mber, Err.Description , conMod & ".AuditEditEnd( )", ,
False)
Resume Exit_AuditEditE nd
End Function
Thanks.
"Tom van Stiphout" <no************ *@cox.net> wrote in message
news:kv******** *************** *********@4ax.c om... On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton" <br***********@ comcast.net> wrote:
What is the value of sSQL at that time? -Tom.
I'm using Allen Brown's code for audit logging (http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud table doesn't populate with the tracking info at all. I think it might be a problem with the table set-up. I just can't find the problem. These are the fields in my table:
Table1
ID (primary key) AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTmpTable 1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTable1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo audID (primary key) AutoNumber
I didn't substitute anything in the code with audID, I just used 'ID' in place of InvoiceID (I'm not sure what audID is for). I commented out the error handler and checked the code line by line. I'm getting a runtime error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL." When I comment that out, I also get other errors with db.Execute sSQL (Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does anyone have any suggestions? Thanks,
Brad
On Thu, 21 Apr 2005 19:01:30 -0500, "Bradley Burton"
<br***********@ comcast.net> wrote:
I think I see the problem. Your INSERT INTO statement at runtime looks
something like this:
INSERT INTO tblTest(audType , audDate, audUser )
SELECT 'Insert', #5/21/2005 12:00:00#, 'BBurton', SomeTable.*
FROM SomeTable
WHERE (SomeTable.Some ID=123);
This is really the value I was asking you to supply. Just set a
breakpoint at that point in the code, and in the Immediate window
write:
?sSQL
Then create a new query, switch to SQL view, and paste in this text,
and try to run it. You'll find that the query processor will complain
because of a syntax error.
Looking at the sql, you are inserting into 3 fields, but you are
sending 3 values plus however many fields SomeTable has. You have to
supply as many values as you are specifying fields for.
Code like this would likely work:
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) "
& _
"SELECT 'Insert' AS Expr1, Now() AS Expr2,
NetworkUserName () AS
Expr3 & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField &
" = "
& lngKeyValue & ");"
-Tom. sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3, " & sTable & ".* " & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
This is the function:
Function AuditEditEnd(sT able As String, sAudTmpTable As String, sAudTable As String, _ sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean On Error GoTo Err_AuditEditEn d 'Purpose: Write the audit trail to the audit table. 'Arugments: sTable = name of table being audited. ' sAudTmpTable = name of the temp audit table. ' sAudTable = name of the audit table. ' sKeyField = name of the AutoNumber field. ' lngKeyValue = Value of the AutoNumber field. ' bWasNewRecord = True if this was a new insert. 'Return: True if successful 'Usage: Called in form's AfterUpdate event. Example: ' Call AuditEditEnd("t blInvoice", "audTmpInvoice" , "audInvoice" , "InvoiceID" , Me.InvoiceID, bWasNewRecord) Dim db As DAO.Database Dim sSQL As String Set db = DBEngine(0)(0)
If bWasNewRecord Then ' Copy the new values as "Insert". sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3, " & sTable & ".* " & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");" db.Execute sSQL, dbFailOnError Else ' Copy the latest edit from temp table as "EditFrom". sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _ " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;" db.Execute sSQL ' Copy the new values as "EditTo" sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3, " & sTable & ".* " & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");" db.Execute sSQL ' Empty the temp table. sSQL = "DELETE FROM " & sAudTmpTable & ";" db.Execute sSQL, dbFailOnError End If AuditEditEnd = True
Exit_AuditEdit End: Set db = Nothing Exit Function
Err_AuditEditE nd: Call LogError(Err.Nu mber, Err.Description , conMod & ".AuditEditEnd( )", , False) Resume Exit_AuditEditE nd End Function
Thanks.
"Tom van Stiphout" <no************ *@cox.net> wrote in message news:kv******* *************** **********@4ax. com... On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton" <br***********@ comcast.net> wrote:
What is the value of sSQL at that time? -Tom.
I'm using Allen Brown's code for audit logging (http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud table doesn't populate with the tracking info at all. I think it might be a problem with the table set-up. I just can't find the problem. These are the fields in my table:
Table1
ID (primary key) AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTmpTabl e1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTable1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo audID (primary key) AutoNumber
I didn't substitute anything in the code with audID, I just used 'ID' in place of InvoiceID (I'm not sure what audID is for). I commented out the error handler and checked the code line by line. I'm getting a runtime error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL." When I comment that out, I also get other errors with db.Execute sSQL (Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does anyone have any suggestions? Thanks,
Brad
It turned out that it was a stupid mistake by me. The ID field wasn't on my
form. Thanks for the help though.
Brad
"Tom van Stiphout" <no************ *@cox.net> wrote in message
news:do******** *************** *********@4ax.c om... On Thu, 21 Apr 2005 19:01:30 -0500, "Bradley Burton" <br***********@ comcast.net> wrote:
I think I see the problem. Your INSERT INTO statement at runtime looks something like this: INSERT INTO tblTest(audType , audDate, audUser ) SELECT 'Insert', #5/21/2005 12:00:00#, 'BBurton', SomeTable.* FROM SomeTable WHERE (SomeTable.Some ID=123);
This is really the value I was asking you to supply. Just set a breakpoint at that point in the code, and in the Immediate window write: ?sSQL
Then create a new query, switch to SQL view, and paste in this text, and try to run it. You'll find that the query processor will complain because of a syntax error. Looking at the sql, you are inserting into 3 fields, but you are sending 3 values plus however many fields SomeTable has. You have to supply as many values as you are specifying fields for.
Code like this would likely work: sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3 & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-Tom. sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3, " & sTable & ".* " & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
This is the function:
Function AuditEditEnd(sT able As String, sAudTmpTable As String, sAudTable As String, _ sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean On Error GoTo Err_AuditEditEn d 'Purpose: Write the audit trail to the audit table. 'Arugments: sTable = name of table being audited. ' sAudTmpTable = name of the temp audit table. ' sAudTable = name of the audit table. ' sKeyField = name of the AutoNumber field. ' lngKeyValue = Value of the AutoNumber field. ' bWasNewRecord = True if this was a new insert. 'Return: True if successful 'Usage: Called in form's AfterUpdate event. Example: ' Call AuditEditEnd("t blInvoice", "audTmpInvoice" , "audInvoice ", "InvoiceID" , Me.InvoiceID, bWasNewRecord) Dim db As DAO.Database Dim sSQL As String Set db = DBEngine(0)(0)
If bWasNewRecord Then ' Copy the new values as "Insert". sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3, " & sTable & ".* " & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");" db.Execute sSQL, dbFailOnError Else ' Copy the latest edit from temp table as "EditFrom". sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _ " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTabl e & ".audDate DESC;" db.Execute sSQL ' Copy the new values as "EditTo" sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _ "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName () AS Expr3, " & sTable & ".* " & _ "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");" db.Execute sSQL ' Empty the temp table. sSQL = "DELETE FROM " & sAudTmpTable & ";" db.Execute sSQL, dbFailOnError End If AuditEditEnd = True
Exit_AuditEdi tEnd: Set db = Nothing Exit Function
Err_AuditEdit End: Call LogError(Err.Nu mber, Err.Description , conMod & ".AuditEditEnd( )", , False) Resume Exit_AuditEditE nd End Function
Thanks.
"Tom van Stiphout" <no************ *@cox.net> wrote in message news:kv****** *************** ***********@4ax .com... On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton" <br***********@ comcast.net> wrote:
What is the value of sSQL at that time? -Tom.
I'm using Allen Brown's code for audit logging (http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud table doesn't populate with the tracking info at all. I think it might be a problem with the table set-up. I just can't find the problem. These are the fields in my table:
Table1
ID (primary key) AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTmpTable 1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo
audTable1
audType Text audDate Date/Time audUser Text ID AutoNumber Defect Number End-user Text Date Date/Time Summary Memo audID (primary key) AutoNumber
I didn't substitute anything in the code with audID, I just used 'ID' in place of InvoiceID (I'm not sure what audID is for). I commented out the error handler and checked the code line by line. I'm getting a runtime error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL." When I comment that out, I also get other errors with db.Execute sSQL (Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does anyone have any suggestions? Thanks,
Brad This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Keith |
last post by:
Hi
I am developing an ASP application which will interact with a SQL database.
A requirement of the application is that there is a full audit trail of any
modifications to data.
I am struggling a bit to get my head round how to do this - just the
concept.
|
by: Zlatko Matić |
last post by:
Hello.
I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate,type,UserName.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...Therefore...
|
by: JohnO |
last post by:
Thanks to Serge and MarkB for recent tips and suggestions.
Ive rolled together a few stored procedures to assist with creating
audit triggers automagically. Hope someone finds this as useful as I've
found it educational.
Note:
- I build this for use in a JDEdwards OneWorld environment. I'm not
sure how generic others find it but it...
|
by: Bradley Burton |
last post by:
I've successfully set up Allen Brown's code for audit logging
(http://allenbrowne.com/ajbAudit.txt), but I think there is something wrong.
It creates only one log of what the user did. The next time the user edit's
or updates the form it will overwrite the previous log record in the table
in the table. So basically my aud table shows only...
|
by: Parag |
last post by:
Hello,
I have been assigned the task to design the audit trail for the ASP.NET web
application. I don't know what the best practices for such audit trails are.
Our application one dedicated user name and password to perform the database
operations. I need to capture all the operations which are performed on the
database. Also I need to able...
| |
by: Oscar Santiesteban Jr. |
last post by:
I need help...here is the problem.
Last weekend, the servers in our datacenter where moved around. After this
move, and maybe coincidental, 1 server is performing very poor. After
running a trace with SQL Profiler, I saw the problem which was later
confirmed with another tool for SQL server performance monitoring. It seems
that all...
|
by: sara |
last post by:
I use Allen Browne's Audit Trail code in everything I do and I love
it.
Recently, I've run into a problem I can't figure out.
I have a database with about 35 lookup tables. I am creating an
"Admin" screen to allow the head social worker to maintain items in
the list. (This is a volunteer project for a non-profit that is trying
to help...
|
by: babamc4 |
last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne) and this is working great, edit, insert etc is working bar when I try to delete a record in one of my subforms (I'm in test stage at the mo) I get a run...
|
by: zandiT |
last post by:
hello
i'm using the microsoft audit trail example ACC2000: How to Create an Audit Trail of Record Changes in a Form and im having a problem with my recordset.
in the example they are using a bound form so the text box called tbAuditTrail has a control source of the AuditTrail field in the table. My form is unbound. i tried to conncet my...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |