troubleshooting Question

Dynamically name a query with Users ID

Avatar of Sandra Smith
Sandra SmithFlag for United States of America asked on
Microsoft DevelopmentMicrosoft Access
7 Comments3 Solutions364 ViewsLast Modified:
I havse an ACCESS 2003 database that could have users trying to save data at the same time.  There is a process that check records and without getting into a lot of detail, I would like to create a querydef with the query name created dynamicall with the user's ID at the end so if more than one user is running threprocess, they don't crash into each other trying to use the same query.  I don't even know if that is posisible, but I am attaching my starting point.
Public Sub AppendSPOT(strUserId As String, dteActivityDate As Date)
Dim strDelete As String, strAppendSPOT As String, strUserId As String
Dim strNoSpotData As String, qdfNoSpotData As QueryDef
 
Set db = CurrentDb
'Selects data from the production database to see if any spot data
'already exists.  If it has all been deleted, either by user or system,
'then there should be no data in the EmpStats table either.  The row count
'returned for this query satisfies this criteria of rows existing or not
strNoSpotData = "SELECT tblProductionData.UserID, tblProductionData.SystemUserID, " & _
    "tblProductionData.ActivityDate, tblProductionData.WFLWID, tblWorkFlows.DSRID " & _
    "FROM tblProductionData INNER JOIN tblWorkFlows ON " & _
    "tblProductionData.WFLWID = tblWorkFlows.WFLWID " & _
    "WHERE tblProductionData.UserID = '" & strUserId & "' AND " & _
    "tblProductionData.ActivityDate = #" & dteActivityDate & "# AND " & _
    "tblWorkFlows.DSRID = 'DSR3' "
 
Set qdfNoSpotData = db.CreateQueryDef("qryNoSpotData & strUser & ", strNoSpotData)
Set rs = CurrentDb.OpenRecordset("qryNoSpotData & strUser & ")
 
If rs.RecordCount > 0 Then
    DoCmd.RunSQL strDelete  'For this example, deleted
    DoCmd.RunSQL strAppendSPOT  'For this example, deleted
Else
    DoCmd.RunSQL strDelete  'For this example, deleted
End If
 
    DoCmd.DeleteObject acQuery, ("qryNoSpotData & strUser & ")
Set rs = Nothing
Set qdfNoSpotData = Nothing
Set db = Nothing
    
 
End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros