Allen Browne's ErrorLog Function + SQL Server problem

[Rob Hofkens]

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors in a
table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all is
well with table itself.

When I use the original code I get an error about dbSeeChanges and I don't
know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly +
dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record to the
table.
When I go through the code with trace, I see that the code that does this is
executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error mesage
or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As
String, _
strCallingProc As String, Optional vParameters, Optional bShowUser As
Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf & _
"Please write down the following details:" & vbCrLf & vbCrLf & _
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf
& vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function

 

[Allen Browne]

If you are more comfortable with SQL, you could execute an Append query
statement to add the values to the log table:
"INSERT INTO ...

If you are not familiar with writing SQL statements, you can mock up a query
in Access using any literal values, and then switch to SQL View ()View menu)
to get an example of the query string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors in a
table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all is
well with table itself.

When I use the original code I get an error about dbSeeChanges and I don't
know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly +
dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record to
the table.
When I go through the code with trace, I see that the code that does this
is executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error
mesage or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As
String, _
strCallingProc As String, Optional vParameters, Optional bShowUser As
Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf & _
"Please write down the following details:" & vbCrLf & vbCrLf & _
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription &
vbCrLf & vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function

 

[Rob Hofkens]

Thank you Allen that would be an alternate solution which I am going to try.
I wonder though why I cannot get the original code to work without the
dbSeeChanges error ?
And why rs.AddNew doesn't add the record when using ADO.

Btw. I am a bit late to respond here, so my appologise for it.
Had a little vacation last week

Rob.

If you are more comfortable with SQL, you could execute an Append query
statement to add the values to the log table:
"INSERT INTO ...

If you are not familiar with writing SQL statements, you can mock up a
query in Access using any literal values, and then switch to SQL View
()View menu) to get an example of the query string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors in a
table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all is
well with table itself.

When I use the original code I get an error about dbSeeChanges and I
don't know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly +
dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record to
the table.
When I go through the code with trace, I see that the code that does this
is executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error
mesage or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As
String, _
strCallingProc As String, Optional vParameters, Optional bShowUser As
Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf & _
"Please write down the following details:" & vbCrLf & vbCrLf & _
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription &
vbCrLf & vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function

 

[Allen Browne]

You used OpenRecordset on CurrentDb.
That's a DAO object, so you would get a DAO Recordset not an ADO one.

dbSeeChanges has limited application anyway.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen that would be an alternate solution which I am going to
try.
I wonder though why I cannot get the original code to work without the
dbSeeChanges error ?
And why rs.AddNew doesn't add the record when using ADO.

Btw. I am a bit late to respond here, so my appologise for it.
Had a little vacation last week

Rob.

If you are more comfortable with SQL, you could execute an Append query
statement to add the values to the log table:
"INSERT INTO ...

If you are not familiar with writing SQL statements, you can mock up a
query in Access using any literal values, and then switch to SQL View
()View menu) to get an example of the query string you need to create.

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors in a
table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all is
well with table itself.

When I use the original code I get an error about dbSeeChanges and I
don't know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly +
dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record to
the table.
When I go through the code with trace, I see that the code that does
this is executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error
mesage or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As
String, _
strCallingProc As String, Optional vParameters, Optional bShowUser As
Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf &
_
"Please write down the following details:" & vbCrLf & vbCrLf & _
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription &
vbCrLf & vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function

 

[Rob Hofkens]

I think I just got another "Ahaa" experience

So when I use the AttachDSNLessTable code to connect my tables to the SQL
server, they are added to the CurrentDB and that means they are DAO ?

And if thats the case I should continue to use DAO code and not bother with
ADO unless it's realy needed ?

I wonder though why " Dim cn As ADODB.Connection" and the " Set cn =
CurrentProject.Connection" doesn't give some kind of error since I
apperently set a DAO connection to a ADO connection variable?

I hope you find the time to answer me these last questions about this
subject to me.

Thanx a lot Allen !

Rob.

You used OpenRecordset on CurrentDb.
That's a DAO object, so you would get a DAO Recordset not an ADO one.

dbSeeChanges has limited application anyway.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen that would be an alternate solution which I am going to
try.
I wonder though why I cannot get the original code to work without the
dbSeeChanges error ?
And why rs.AddNew doesn't add the record when using ADO.

Btw. I am a bit late to respond here, so my appologise for it.
Had a little vacation last week

Rob.

If you are more comfortable with SQL, you could execute an Append query
statement to add the values to the log table:
"INSERT INTO ...

If you are not familiar with writing SQL statements, you can mock up a
query in Access using any literal values, and then switch to SQL View
()View menu) to get an example of the query string you need to create.

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors in
a table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all
is well with table itself.

When I use the original code I get an error about dbSeeChanges and I
don't know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly +
dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record to
the table.
When I go through the code with trace, I see that the code that does
this is executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error
mesage or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription
As String, _
strCallingProc As String, Optional vParameters, Optional bShowUser
As Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf &
_
"Please write down the following details:" & vbCrLf & vbCrLf & _
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription &
vbCrLf & vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function

 

[Douglas J. Steele]

Linked tables are neither DAO nor ADO. The method you use to link them is
irrelevant: the important thing is that you've set a specific property for
them.

CurrentProject.Connection will always return an ADO connection: there's no
such thing as a DAO connection.

There's absolutely nothing wrong with using both DAO and ADO in the same
application. The critical thing is that you know when you're using each, and
that you disambiguate the objects appropriately.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think I just got another "Ahaa" experience

So when I use the AttachDSNLessTable code to connect my tables to the SQL
server, they are added to the CurrentDB and that means they are DAO ?

And if thats the case I should continue to use DAO code and not bother
with ADO unless it's realy needed ?

I wonder though why " Dim cn As ADODB.Connection" and the " Set cn =
CurrentProject.Connection" doesn't give some kind of error since I
apperently set a DAO connection to a ADO connection variable?

I hope you find the time to answer me these last questions about this
subject to me.

Thanx a lot Allen !

Rob.

You used OpenRecordset on CurrentDb.
That's a DAO object, so you would get a DAO Recordset not an ADO one.

dbSeeChanges has limited application anyway.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen that would be an alternate solution which I am going to
try.
I wonder though why I cannot get the original code to work without the
dbSeeChanges error ?
And why rs.AddNew doesn't add the record when using ADO.

Btw. I am a bit late to respond here, so my appologise for it.
Had a little vacation last week

Rob.

"Allen Browne" <[email protected]> schreef in bericht
If you are more comfortable with SQL, you could execute an Append query
statement to add the values to the log table:
"INSERT INTO ...

If you are not familiar with writing SQL statements, you can mock up a
query in Access using any literal values, and then switch to SQL View
()View menu) to get an example of the query string you need to create.

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors in
a table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all
is well with table itself.

When I use the original code I get an error about dbSeeChanges and I
don't know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly +
dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record
to the table.
When I go through the code with trace, I see that the code that does
this is executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error
mesage or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription
As String, _
strCallingProc As String, Optional vParameters, Optional bShowUser
As Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf
& _
"Please write down the following details:" & vbCrLf & vbCrLf &
_
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription &
vbCrLf & vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function

 

[Rob Hofkens]

"The critical thing is that you know when you're using each, and that you
disambiguate the objects appropriately."

Well, I think that's where I go wrong then.
I have to take some more time to study both ADO and DAO some more to fully
understand how it works.

Thanx for your answer Douglas !

Rob.

Linked tables are neither DAO nor ADO. The method you use to link them is
irrelevant: the important thing is that you've set a specific property for
them.

CurrentProject.Connection will always return an ADO connection: there's no
such thing as a DAO connection.

There's absolutely nothing wrong with using both DAO and ADO in the same
application. The critical thing is that you know when you're using each,
and that you disambiguate the objects appropriately.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think I just got another "Ahaa" experience

So when I use the AttachDSNLessTable code to connect my tables to the SQL
server, they are added to the CurrentDB and that means they are DAO ?

And if thats the case I should continue to use DAO code and not bother
with ADO unless it's realy needed ?

I wonder though why " Dim cn As ADODB.Connection" and the " Set cn =
CurrentProject.Connection" doesn't give some kind of error since I
apperently set a DAO connection to a ADO connection variable?

I hope you find the time to answer me these last questions about this
subject to me.

Thanx a lot Allen !

Rob.

You used OpenRecordset on CurrentDb.
That's a DAO object, so you would get a DAO Recordset not an ADO one.

dbSeeChanges has limited application anyway.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen that would be an alternate solution which I am going to
try.
I wonder though why I cannot get the original code to work without the
dbSeeChanges error ?
And why rs.AddNew doesn't add the record when using ADO.

Btw. I am a bit late to respond here, so my appologise for it.
Had a little vacation last week

Rob.

"Allen Browne" <[email protected]> schreef in bericht
If you are more comfortable with SQL, you could execute an Append
query statement to add the values to the log table:
"INSERT INTO ...

If you are not familiar with writing SQL statements, you can mock up a
query in Access using any literal values, and then switch to SQL View
()View menu) to get an example of the query string you need to create.

Hello everyone

I have a problem with some code and I cannot find the problem
The code I use is from Allen Brown and its purpose it to log errors
in a table.
Here is the link to the original code
http://www.allenbrowne.com/ser-23a.html

I use Access as a Front End 2003 and SQL Server 2000 as Back End.
The table "tLogError" is linked via a DSNless connection.
I am able to add records directly into the table so I figure that all
is well with table itself.

When I use the original code I get an error about dbSeeChanges and I
don't know what to do about that?
It has something to do with the SQL Server Indentify column.
I tried to change the line:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
to:
Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly
+ dbSeeChanges)

But that didnt work

So I modified the code to use ADO but the code doesn't add a record
to the table.
When I go through the code with trace, I see that the code that does
this is executed.
But when it is done I don't see a new record in the table?
So althoug the code is executed it doesn't work and there is no error
mesage or something that indicates that something is wrong.

Can someone help me plz ?

Here is my modified code to use ADO:

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription
As String, _
strCallingProc As String, Optional vParameters, Optional bShowUser
As Boolean = True) As Boolean

' Author: Allen Browne, (e-mail address removed)

On Error GoTo Err_LogError

Dim strMsg, strSQL As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset

Select Case lngErrNumber
Case 0
Debug.Print strCallingProc & " called error 0."
Case 2501 ' Cancelled
'Do nothing.
Case 3314, 2101, 2115 ' Can't save.
If bShowUser Then
strMsg = "Record cannot be saved at this time." & vbCrLf & _
"Complete the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, strCallingProc
End If
Case Else
If bShowUser Then
strMsg = "Error " & lngErrNumber & ": " & strErrDescription
MsgBox strMsg, vbExclamation, strCallingProc
End If

Set cn = CurrentProject.Connection
cn.BeginTrans
strSQL = "SELECT * FROM tLogError"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
' Waarden toekennen aan de velden
rs![ErrNumber] = lngErrNumber
rs![ErrDescription] = Left$(strErrDescription, 255)
rs![ErrDate] = Now()
rs![CallingProc] = strCallingProc
rs![UserName] = CurrentUser()
rs![ShowUser] = bShowUser
If Not IsMissing(vParameters) Then
rs![Parameters] = Left(vParameters, 255)
End If

rs.Update
rs.Close
LogError = True

End Select

Exit_LogError:
Set rs = Nothing
Exit Function

Err_LogError:
strMsg = "An unexpected situation arose in your program." & vbCrLf
& _
"Please write down the following details:" & vbCrLf & vbCrLf &
_
"Calling Proc: " & strCallingProc & vbCrLf & _
"Error Number " & lngErrNumber & vbCrLf & strErrDescription &
vbCrLf & vbCrLf & _
"Unable to record because Error " & Err.Number & vbCrLf &
Err.Description
MsgBox strMsg, vbCritical, "LogError()"
Resume Exit_LogError
End Function