Query executed by many users 1

[Aietoe]

I have a query that create a table, and that can be executed by many users.
When it happens that 2 users execute it at the same time, they receive one of the following message:
- Couldn't lock table xxxxxx currently in use by user yyyy
- Table xxxx already exits
- You tried to lock table xxxx while opening it, but the table can't be locked because it is currently in use....

Is there any way to "personnalise" the name of a table so that many users could execute a same query at the same time?

Thanks in advance for any help. I greatly appreciate your time and consideration.

Aietoe

 

[Amiel]

Yes there is. The question would be,
how to name the table to identify the individual user and guarantee that that user gets results from the table that they created.

How about a local table (in a local database) on the users machine. In addition to providing a local copy of the query result which may improve performance. It can reduce network traffic during that particular user session.

Other solutions involve creating a locking scheme which is far more involved than simply creating a local copy.

Further, if the size of the result sets is large then using a naming scheme might be the best way to go. Take a look at the API function GetMachineName() or GetUserName() and use this to name the table. B-)


More? let me know.







Amiel
amielzz@netscape.net

 

[Aietoe]

Thanks a lot.
I just join this forum; i never thought i would get an answer that fast.

I think the local table is a good idea and i'll try it.


Thanks again!

 

[Aietoe]

Hi Amiel!

After all that time, i'm coming back to ask you your help in using a naming scheme because the local databases are much too large.

I get the userName using the following code:

Dim sUser As String
Dim sComputer As String
Dim lpBuff As String * 1024
Dim sUsager

'Get the Login User Name
GetUserName lpBuff, Len(lpBuff)
sUser = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
lpBuff = ""

'Get the Computer Name
GetComputerName lpBuff, Len(lpBuff)
sComputer = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
lpBuff = ""


MsgBox "Login User: " & sUser & vbCrLf & _
"Computer Name: " & sComputer

At this point, i don't know how to use "sUser" to create my tables...

Thanks for your help!