Sandra Smith
asked on
Dynamically name a query with Users ID
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
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Multiple users with a common frontend on a share will cause problems sooner or later and that's a fact.
ASKER
Due to logistics, each user cannot have their own front end. Bottom line, this is a never-ending nightmare (sorry, I am going to vent), I have a deadlne next Friday and the "program manager" of this disaster is still making signifcant structural/logical/format/ data changes. This mess has been going on for almost a year now and no matter how much I try to get them to sign-off on decisions, no one wants to make a choice or be responsbile. I know the life of a programmer means constant shifts, but I will be so glad ot get out of here, what started out has a really good project has degenerated into a political muddy quagimire. Ah, got that off my chest. So, actually, the "program manager" solved the whole issues with one fell swwop yesterday afternoon in that only one person for the whole company can do this task. So, there went the whole problem. Previously each unit was going to have a person resposnible for their personnel data. Ok, let's see how long THAT decision is going to last. But for now, thank you for the suggestions/dialog but the problem is moot.
ASKER
Am going to share out the points as there really was no way out of this. I did actually, however, get a version of what I wanted to do to work so I was pleased with the learning but again, this is all moot at this point. I think I need to hide my gun......
Sounds like you're working with a client I let go recently ...