问题描述:

I'm having an issue with my databases. I have multiple access databases that share the same pool of users and passwords. They reference the table of users and passwords by a linked table to the 'master' database (the backend that holds the tables for users and passwords). I also have a table that stores the current users and the databases that they are logged into. The problem I'm encountering is that my logout method is not actually logging them out. This is what it is roughly:

 'this code is run on click of exit button

Public Sub logout(UserName As String, database As String)

On Error Resume Next

Dim dbMine As DAO.database

Set dbMine = CurrentDb

Dim qr As String

qr = "DELETE * FROM tblCurrentUsers WHERE username = '" & UserName & "' AND Database = '" & database & "' ;"

'debug.print qr

dbMine.Execute qr

Application.Quit

End Sub

The problem is, the records don't seem to be deleting. Do I need to set my database object to the source table instead of referencing the linked table that exists in the database on which the code is run? If so, do I just reference that database by relative path?

网友答案:

Add dbFailOnError option when executing query to catch the error details. See what error you get; that could help you in resolving of your issue.

Public Sub logout(UserName As String, database As String)
On Error GoTo mError:
Dim dbMine As DAO.database
Set dbMine = CurrentDb
Dim qr As String
   qr = "DELETE * FROM tblCurrentUsers WHERE username = '" & UserName & "' AND Database = '" & database & "' ;"
    'debug.print qr
   dbMine.Execute qr, dbFailOnError
  Application.Quit
Exit Sub
mError:
  MsgBox "Error: " & Err.Description
End Sub
网友答案:

Try using

Set dbMine = DBEngine.Workspaces(0).Databases(0)

instead of

Set dbMine = CurrentDb

I've had similar issues when using CurrentDb. According to http://msdn.microsoft.com/en-us/library/office/bb237861(v=office.12).aspx,

The CurrentDb method creates another instance of the current database, while the DBEngine.Workspaces(0).Databases(0) syntax refers to the open copy of the current database.

Perhaps there's some subtle difference in the way DAO or Access handles the "new instance of the current database".

相关阅读:
Top