Multiple Users Access

[joesmithf1]

Hi,
I am a newbie; i know how to write simple codes but not the more advance ones. Please help!

The module below create a query(qryBiweeklyReports) and then run it. It works fine if one person run it. HOWEVER, if I have two people run it simultaneously, I get something like "qryBiweeklyReports" all ready exist error. I am thinking I need to add something in the "For Each qdf..." codes block toward the beginning and a conditional statement at the end, to identify that if one person is using qryBiweeklyReports, THEN create a different name like qryBiweeklyReports2 for the second person to use. How and where would I add the codes to do such thing?

thank you!

Private Sub OK_Click()
Me.Visible = False
Dim dbs As Object
Dim qdf As Object
Dim SQL As String
Set dbs = CurrentDb
For Each qdf In dbs.querydefs
If qdf.NAME = "qryBiweeklyReports" Then
dbs.querydefs.Delete qdf.NAME
dbs.querydefs.Refresh
End If
Next qdf

'Property Not Found could mean that in your query statement, 1 or more fields does not exist in the table that you are querying from
Select Case Category
Case "CP"
SQL = "SELECT qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employee Amt], qryDedparmDedetail.FirstOfSTATUS, qryDedparmDedetail.FirstOfAGENCY, " & _
"qryDedparmDedetail.FirstOfTITLE, qryDedparmDedetail.FirstOfFORMAT_NM, qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1 AS Expr1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType " & _
"FROM qryDedparmDedetail INNER JOIN RepUnit ON qryDedparmDedetail.RepUnit = RepUnit.REPUNIT " & _
"GROUP BY qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employer Amt], qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType " & _
"HAVING (((qryDedparmDedetail.RepUnit)='CP') AND ((qryDedparmDedetail.LeftType)='01'));"


End Select

Set qdf = dbs.CreateQueryDef("qryBiweeklyReports", SQL)
DoCmd.OpenForm "FrmBiweeklyReports"
DoCmd.OpenQuery "qryBiweeklyReports"


Set dbs = Nothing

End Sub

 

[gemma-the-husky]

i assume therefore you have a number of users sharing the same copy of a networked database.

any number of threads here will explain that you should split your database into a front end and back end, then EACH USER should have an INDIVIDUAL copy of the front end - which avoids the situation you have describe.

 

[joesmithf1]

Hi, Thanks!

I have already created my database as a single component; not separated into front end and back end. Is it too late to do so now? How do I split one database into front end and back-end?

 

[KeithG]

Move all the forms,queries,reports,modules from the currentdb to a new db. Then link the tables from your original db to the new one. Now the new db is your Front end and the old db is your Back end.