Run-time error 3033 - You do not have the necessary...

[greg32246]

I have some code that is run on startup of the database that checks the version number of the client front-end and updates the front-end if a new version is found.

I originally found this method in another post:

http://www.tek-tips.com/viewthread.cfm?qid=1538641

For the most part, this method has worked great. However, on occassion, I get run-time error 3033: You do not have the necessary permissions to use the <database> object.

When I hit the "debug" button to see where the code is crashing, it highlights a DoCmd.RunSql command that I have placed in the code that runs an append query to insert a record of the update into a log. The log is a linked table.

I don't understand what is causing this error. There is no security set up on this DB. The error only happens on occassion, not every time. The object that the error is referencing is the database itself, not a table.

I do believe that it is the append query that is the problem. I have this executing after the new version is copied to the client machine, but before the Shell command to open the new instance of the DB. I can tell that the copy has completed before the crash of the code because the new version is on the client machine and there is no record of it in the log, so the log entry has failed.

Typically, when the error occurs, I can just end the code, close the database, reopen it, and the client is now on the new version. I can then update the client again on the same machine, same user, etc. and it works fine. The error only happens on occassion.

Anyone have any ideas?

I know my code is sloppy and my naming conventions are a joke, but if it helps, I have posted the code below.




Option Compare Database

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long


Public Function AccessVersionControlUtility()
'variables for client side
Dim myidentifier As String
Dim strVerClient As Double

'objects used to query SQL table to return Master info
Dim db As Database
Dim rs As DAO.Recordset
Dim mysql As String

'server side variables - master db info
Dim myDatabaseName As String
Dim strVerServer As Double 'current Master version
Dim strSourceFile As String 'path to Master
Dim mySuspend As String
Dim myRequired As String
Dim myAsk As String

'additional environment variables used for Log
Dim myUser As String
Dim myWorkstation As String

'sql string used to append data to log
Dim mysqlLog As String

'additional variables used by copy command and to restart access
Dim strDestFile As String
Dim strAccessExePath As String, lngResult As Long

'get the client side version info first
myidentifier = Nz(DLookup("[ThisDBIdentifier]", "[tblVersion]"), 0)
strVerClient = Nz(DLookup("[VersionNumber]", "[tblVersion]"), 0)

'get the Master info by querying AccessVersionControl table and put into variables
mysql = "SELECT dbo_AccessVersionControl.DatabaseName, dbo_AccessVersionControl.DatabaseIdentifier, dbo_AccessVersionControl.FullMasterPath, dbo_AccessVersionControl.CurrentVersionNo, dbo_AccessVersionControl.AskBeforeUpdate, dbo_AccessVersionControl.UpdateRequired, dbo_AccessVersionControl.Suspend From dbo_AccessVersionControl WHERE (((dbo_AccessVersionControl.DatabaseIdentifier)='" & myidentifier & "'));"

Set db = CurrentDb
Set rs = db.OpenRecordset(mysql)

If rs.EOF = True Then
MsgBox "The Microsoft Access Version Control Utility has encountered an error. The Database Identifier could not be found. Please notify the Database Coordinator.", vbCritical, "Error"
DoCmd.Quit
Exit Function
End If

rs.MoveFirst

myDatabaseName = rs.Fields("DatabaseName").Value
strVerServer = rs.Fields("CurrentVersionNo").Value
strSourceFile = rs.Fields("FullMasterPath").Value
mySuspend = rs.Fields("Suspend").Value
myAsk = rs.Fields("AskBeforeUpdate").Value
myRequired = rs.Fields("UpdateRequired").Value

Set rs = Nothing
Set db = Nothing


'populate the additional environment variables
myUser = Environ("Username")
myWorkstation = Environ("ComputerName")

'start of the utility logic

'if the client version matches the master version
If strVerClient = strVerServer Then
'do nothing return control to calling form
Exit Function

'otherwise if the versions do not match then copy the master to the client machine (with qualifications)
ElseIf strVerClient <> strVerServer And mySuspend = "Y" Then
'database has been put into a suspend state and update will not continue
MsgBox "The master database is currently being maintained. Update is not possible at this time.", vbOKOnly, ""
Exit Function
ElseIf strVerClient <> strVerServer And mySuspend = "N" And myAsk = "Y" Then
'user is given the opportunity to bypass update
response = MsgBox("There is a new version of " & myDatabaseName & " available on the network. Do you want to download the new version now?", vbYesNo, "New Version Found")
If response = vbYes Then
'user has chosen to update to new version so update
strDestFile = CurrentProject.FullName

'Determine path of current Access executable
strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "

If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.
MsgBox "The Master copy of the database could not be located. Please see the database coordinator.", vbCritical, "Error"
DoCmd.Quit
Exit Function
Else 'copy the new version of app over the existing one.
lngResult = apiCopyFile(strSourceFile, strDestFile, False)
End If

'record the update in the log
DoCmd.SetWarnings False
mysqlLog = "INSERT INTO dbo_AccessVersionControlLog ( DatabaseIdentifier, DatabaseName, NewVersion, OldVersion, UserName, Workstation ) SELECT '" & myidentifier & "' AS Expr1, '" & myDatabaseName & "' AS Expr2, '" & strVerServer & "' AS Expr3, '" & strVerClient & "' AS Expr4, '" & myUser & "' AS Expr5, '" & myWorkstation & "' AS Expr6;"
DoCmd.RunSQL (mysqlLog)
DoCmd.SetWarnings True

'Modify strDestFile slightly so that it can be used with the Shell function
strDestFile = """" & strDestFile & """"

MsgBox "Application Updated. Please wait while the application restarts.", _
vbInformation, "Update Successful"

'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus

DoCmd.Quit
Else
'user has chosen not to update to new version
If myRequired = "Y" Then
MsgBox "The latest update is required for proper database operation. Access to database denied. Please see the Database Coordinator for assistance.", vbCritical, "Update Required"
DoCmd.Quit
Else
MsgBox "You are working on an outdated version of this database. Please update your database version as soon as possible.", vbCritical, "Warning"
Exit Function
End If
End If
Else
'user was not given the option to update so update proceeds automatically
strDestFile = CurrentProject.FullName

'Determine path of current Access executable
strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "

If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.
MsgBox "The Master copy of the database could not be located. Please see the database coordinator.", vbCritical, "Error"
DoCmd.Quit
Exit Function
Else 'copy the new version of app over the existing one.
lngResult = apiCopyFile(strSourceFile, strDestFile, False)
End If

'record update in the log
DoCmd.SetWarnings False
mysqlLog = "INSERT INTO dbo_AccessVersionControlLog ( DatabaseIdentifier, DatabaseName, NewVersion, OldVersion, UserName, Workstation ) SELECT '" & myidentifier & "' AS Expr1, '" & myDatabaseName & "' AS Expr2, '" & strVerServer & "' AS Expr3, '" & strVerClient & "' AS Expr4, '" & myUser & "' AS Expr5, '" & myWorkstation & "' AS Expr6;"
DoCmd.RunSQL (mysqlLog)
DoCmd.SetWarnings True

'Modify strDestFile slightly so that it can be used with the Shell function
strDestFile = """" & strDestFile & """"

MsgBox "Application Updated. Please wait while the application restarts.", _
vbInformation, "Update Successful"

'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus

DoCmd.Quit

End If

End Function

 

[joelflorendo]

Has it actually successfully updated to the latest version without the error? Or does the error really only occur if there is a version update? Not that this solves the problem but that would narrow out the randomness of the error...

I'm wondering if your overwrite of the old version with the new version is causing a problem with the connection to the backend. Have you tried updating your log table before you call the api to overwrite the old version?

 

[greg32246]

Joel,

If there is no version update, then there is no error.

Sometimes when there is a version update, there is no error. Other times when there is a version update, there is an error.

Every time there is an attempted version update, I have been able to confirm that the new version is now on the client machine, but when there is an error there is no entry in the log. Since the log entry is the next command in the code after the update, it makes sense that the log entry is the problem.

I had thought about moving the log entry to the point before the overwrite of the file like you suggest. It just seemed to be logically out of place, but since I am in no way confirming the result of the copy, it really doesn't matter.

I have been getting some different errors now that point to the permissions on the log table. That confirms what I already thought, which was that it has something to do with the log, either with the SQL Server table itself or the command I am using for the append. I have to admit, I am very new to SQL Server, but I set this table up the same way I have other tables. The only thing that is different is that I have never appended to a SQL Server table using Docmd.RunSQL. In the past, I have just run a saved query or I have opened the table via a (table)recordset object and used a .ADD statement.

At this point, I have commented out the log commands and I am waiting to see if I get any errors reported from users on the network. So far, I have not had any reported, so I might just do away with the log.