cravincreeks
Technical User
Hello all,
I’m working on an Access 2000 app that checks in data from our field staff. They’re using the “Data On The Run” program on their PDA, which outputs data in an Access database table format. This check in process includes a lot of Insert/Update/Delete statements and if any of these fail, I’d like to rollback ALL of the transactions. So I was going to use workspace.BeginTrans. If an error occurs, in the error handler I was hoping to use workspace.Rollback. If no error occurs, then one of the last lines of the sub was going to be workspace.CommitTrans
But when I try to execute the first SQL statement using currentDB.Execute strSQL, dbFailOnError, I get runtime error ‘3464’ – Data type mismatch in query expression. Oddly, if I don’t include the dbFailOnError argument, I don’t get the error message and the SQL statement executes. But the problem here is that I wouldn’t be able to rollback this SQL statement if an error occurs.
I do have Microsoft DAO 3.6 Object library enabled in the references. Here is the code, from the beginning.
Public Sub CheckInTable (strTblName as String)
Dim wkDefault As Workspace
Set wkDefault = DBEngine.Workspaces(0)
wkDefault.BeginTrans
Dim strInsertSQL As String
strInsertSQL = "INSERT INTO tblRawRecords SELECT " & strTblName & ".*
FROM " & strTblName & ";"
Debug.Print "strInsertSQL: " & strInsertSQL
‘Note: this debug statement prints the following line:
strInsertSQL: INSERT INTO tblRawRecords SELECT NUB.* FROM NUB;
On Error GoTo errhandler
‘This is the line where the error occurs
CurrentDb.Execute strInsertSQL, dbFailOnError
‘The code would then call many other subs that do error checking and involve many
‘ Insert/Update/Delete statements. Hence, if an error occurs in any of these subs
‘ the code will go to errhandler and rollback all of these transactions. I’ve tested it
‘ and errors in these subs do in fact immediately jump to errhandler
‘no errors occurred. Commit transactions
wkDefault.CommitTrans
errhandler:
msgbox “An error occurred in the check in process. Check in aborted”
wkDefault.Rollback
End Sub
Again, my current problem is that when dbFailOnError is included, it throws the said error. But if I don’t include dbFailOnError, the code executes without any problem, suggesting that my SQL statement is valid. But removing dbFailOnError would remove my ability to rollback all of the transactions if an error occurs. I’m fairly new to using transactions in VBA/SQL code, so if you have any other words of advice, please feel free to comment.
These next two inquiries are kind of beside the point, but feel free to respond to these as well.
My access help files are not displaying any info on BeginTrans, Rollback, or CommitTrans. I’ve notice when typing code that BeginTrans and CommitTrans have an optional argument, “options as long”. What are these options?
Also, does anybody know if there is a good website that mirrors the Access help file. Many of the keywords that I click on in the index of the help files don’t display any info.
THANK YOU THANK YOU THANK YOU THANK YOU
AZ
I’m working on an Access 2000 app that checks in data from our field staff. They’re using the “Data On The Run” program on their PDA, which outputs data in an Access database table format. This check in process includes a lot of Insert/Update/Delete statements and if any of these fail, I’d like to rollback ALL of the transactions. So I was going to use workspace.BeginTrans. If an error occurs, in the error handler I was hoping to use workspace.Rollback. If no error occurs, then one of the last lines of the sub was going to be workspace.CommitTrans
But when I try to execute the first SQL statement using currentDB.Execute strSQL, dbFailOnError, I get runtime error ‘3464’ – Data type mismatch in query expression. Oddly, if I don’t include the dbFailOnError argument, I don’t get the error message and the SQL statement executes. But the problem here is that I wouldn’t be able to rollback this SQL statement if an error occurs.
I do have Microsoft DAO 3.6 Object library enabled in the references. Here is the code, from the beginning.
Public Sub CheckInTable (strTblName as String)
Dim wkDefault As Workspace
Set wkDefault = DBEngine.Workspaces(0)
wkDefault.BeginTrans
Dim strInsertSQL As String
strInsertSQL = "INSERT INTO tblRawRecords SELECT " & strTblName & ".*
FROM " & strTblName & ";"
Debug.Print "strInsertSQL: " & strInsertSQL
‘Note: this debug statement prints the following line:
strInsertSQL: INSERT INTO tblRawRecords SELECT NUB.* FROM NUB;
On Error GoTo errhandler
‘This is the line where the error occurs
CurrentDb.Execute strInsertSQL, dbFailOnError
‘The code would then call many other subs that do error checking and involve many
‘ Insert/Update/Delete statements. Hence, if an error occurs in any of these subs
‘ the code will go to errhandler and rollback all of these transactions. I’ve tested it
‘ and errors in these subs do in fact immediately jump to errhandler
‘no errors occurred. Commit transactions
wkDefault.CommitTrans
errhandler:
msgbox “An error occurred in the check in process. Check in aborted”
wkDefault.Rollback
End Sub
Again, my current problem is that when dbFailOnError is included, it throws the said error. But if I don’t include dbFailOnError, the code executes without any problem, suggesting that my SQL statement is valid. But removing dbFailOnError would remove my ability to rollback all of the transactions if an error occurs. I’m fairly new to using transactions in VBA/SQL code, so if you have any other words of advice, please feel free to comment.
These next two inquiries are kind of beside the point, but feel free to respond to these as well.
My access help files are not displaying any info on BeginTrans, Rollback, or CommitTrans. I’ve notice when typing code that BeginTrans and CommitTrans have an optional argument, “options as long”. What are these options?
Also, does anybody know if there is a good website that mirrors the Access help file. Many of the keywords that I click on in the index of the help files don’t display any info.
THANK YOU THANK YOU THANK YOU THANK YOU
AZ