Update query -> Dlookup -> Table refresh error (one of the other)

  • Thread starter Thread starter mehmeh via AccessMonster.com
  • Start date Start date
M

mehmeh via AccessMonster.com

Phew, I'm pretty stumped on this one. Basically I am running a quick check to
see if the user has write permissions to the database (Is there a simple way
to check this?) the security is at file level rather than through access
though.
Anyway, it writes a value to [seccheck] in the table "programversion" then
looks up the value to see if it was correctly written. This is a little
overkill because it will throw an error at the time of trying to write the
value however I thought is better going through with the check anyway:

Dim stDocName As String
Dim strSQL As String
Dim stwrite As String
Dim stcompare As String

'Note: Thsi is not meant to be a foolproof way of keeping people out,
it's just to stop people accidently wandering in.

stwrite = 1 + DMax("[seccheck]", "programversion", "ID = 1")

'write the value of stwrite to where it was looked up before being
incremented
strSQL = "UPDATE programversion SET programversion.seccheck = '" &
stwrite & "' WHERE ((ID=1));"
CurrentDb.Execute strSQL

'look up the value writen
stcompare = DLookup("[seccheck]", "programversion", "ID = 1")

'Check if the write was successful.
If stwrite = stcompare Then
'allow them access
stDocName = "admin functions"
DoCmd.OpenForm stDocName, , , , , acDialog
Else
MsgBox "Sorry, you do not have the correct access permissions"
GoTo Exit_cmdadmin_Click
End If


Note: the err. also contains the above message to cover for the update
query's error.

Now, this worked when I first done it (I obviously have the correct
permissions) and has worked the odd time since however most usually it
doesn't.
After some fault finding it appears that the value writes correctly but the
dlookup drags up the old value. I'm assuming I need to refresh the table in
between? If so then that's a problem because I don't know how to (haha), I've
tried all the usual programversion.refresh, docmd requery, and currentdb.
programversion.refresh. None of the work because it can't find the table. It
is a linked table and isn't the record source of the form where the code lays.


Hellllllppppp

:) much appreciated.
 
Phew, I'm pretty stumped on this one. Basically I am running a quick check to
see if the user has write permissions to the database (Is there a simple way
to check this?) the security is at file level rather than through access
though.
Anyway, it writes a value to [seccheck] in the table "programversion" then
looks up the value to see if it was correctly written. This is a little
overkill because it will throw an error at the time of trying to write the
value however I thought is better going through with the check anyway:

Dim stDocName As String
Dim strSQL As String
Dim stwrite As String
Dim stcompare As String

'Note: Thsi is not meant to be a foolproof way of keeping people out,
it's just to stop people accidently wandering in.

stwrite = 1 + DMax("[seccheck]", "programversion", "ID = 1")

'write the value of stwrite to where it was looked up before being
incremented
strSQL = "UPDATE programversion SET programversion.seccheck = '" &
stwrite & "' WHERE ((ID=1));"
CurrentDb.Execute strSQL

'look up the value writen
stcompare = DLookup("[seccheck]", "programversion", "ID = 1")

'Check if the write was successful.
If stwrite = stcompare Then
'allow them access
stDocName = "admin functions"
DoCmd.OpenForm stDocName, , , , , acDialog
Else
MsgBox "Sorry, you do not have the correct access permissions"
GoTo Exit_cmdadmin_Click
End If

Note: the err. also contains the above message to cover for the update
query's error.

Now, this worked when I first done it (I obviously have the correct
permissions) and has worked the odd time since however most usually it
doesn't.
After some fault finding it appears that the value writes correctly but the
dlookup drags up the old value. I'm assuming I need to refresh the table in
between? If so then that's a problem because I don't know how to (haha), I've
tried all the usual programversion.refresh, docmd requery, and currentdb.
programversion.refresh. None of the work because it can't find the table. It
is a linked table and isn't the record source of the form where the code lays.

Hellllllppppp

:) much appreciated.
Click to expand...

May it would work okay if you put in a short delay before checking,
maybe 5 seconds....?

Public Function Delay(Seconds As Long)
Dim TimeWaiting As Long
Dim TimeStarted As Long
Dim dStartTime As Date
Dim dEndTime As Date
TimeWaiting = 0
dStartTime = Time()
TimeStarted = (Format(dStartTime, "s") + (Format(dStartTime, "n") *
60) + (Format(dStartTime, "h") * 3600))

While TimeWaiting < Seconds
dEndTime = Time()
DoEvents
TimeWaiting = (Format(dEndTime, "s") + (Format(dEndTime, "n") *
60) + (Format(dEndTime, "h") * 3600)) - TimeStarted
Wend
End Function

Also, in Microsofts' DAO reference it says:
In a Microsoft Jet workspace, you can include the dbFlushOSCacheWrites
constant with CommitTrans, This forces the database engine to
immediately flush all updates to disk, instead of caching them
temporarily. I don't know if this would work in your situation, but
it's worth a look.
 
H
i,
Thank you very much for the reply. I'm still working on the delay however the
second option didn't work :( .

Something I forgot to mention is that after running the script it tells me
"You do not have exclusive access to the database at this time..." so I have
to exit the application and open it again before I can edit. I'm presume it's
the SQL script still running, if that's the case then "why" springs to mind...



Phew, I'm pretty stumped on this one. Basically I am running a quick check to
see if the user has write permissions to the database (Is there a simple way
Click to expand...
[quoted text clipped - 53 lines]
May it would work okay if you put in a short delay before checking,
maybe 5 seconds....?

Public Function Delay(Seconds As Long)
Dim TimeWaiting As Long
Dim TimeStarted As Long
Dim dStartTime As Date
Dim dEndTime As Date
TimeWaiting = 0
dStartTime = Time()
TimeStarted = (Format(dStartTime, "s") + (Format(dStartTime, "n") *
60) + (Format(dStartTime, "h") * 3600))

While TimeWaiting < Seconds
dEndTime = Time()
DoEvents
TimeWaiting = (Format(dEndTime, "s") + (Format(dEndTime, "n") *
60) + (Format(dEndTime, "h") * 3600)) - TimeStarted
Wend
End Function

Also, in Microsofts' DAO reference it says:
In a Microsoft Jet workspace, you can include the dbFlushOSCacheWrites
constant with CommitTrans, This forces the database engine to
immediately flush all updates to disk, instead of caching them
temporarily. I don't know if this would work in your situation, but
it's worth a look.
Click to expand...
 
Update:

A 5second delay works! Ialso tried a 3second delay but that didn't. I have to
admit that whilst I'm pleased it's working I'm not really convinced that
waiting 5seconds each time is the proper answer.

Interestingly it still doesn't allow me exclusive access even after that time.
 
Update #2:

After soem more searching I've been left with the following which works as I
wanted. I have to be honest that I'm not sure how it is different, this has
the full scope of my knowledge already.

Dim stDocName As String
Dim strSQL As String
Dim stwrite As String
Dim stcompare As String

Dim dbConn As ADODB.Connection
Set dbConn = CurrentProject.Connection

'Note: Thsi is not meant to be a foolproof way of keeping people out,
it's just to stop people accidently wandering in.
stwrite = 1 + DMax("[seccheck]", "programversion", "ID = 1")

'write the value of stwrite to where it was looked up before being
incremented
dbConn.BeginTrans
strSQL = "UPDATE programversion SET programversion.seccheck = '" &
stwrite & "' WHERE ((ID=1));"
dbConn.Execute (strSQL)
dbConn.CommitTrans
Set dbConn = Nothing

'look up the value written
stcompare = DLookup("[seccheck]", "programversion", "ID = 1")

'Check if the write was successful.
If stwrite = stcompare Then
'allow them access
stDocName = "admin functions"
DoCmd.OpenForm stDocName, , , , , acDialog
Else
MsgBox "Sorry, you do not have the correct access permissions"
GoTo Exit_cmdadmin_Click
End If

Thank you for the help
 
Back
Top