How can i add a username to a table name

[Aietoe]

Hi!

Using Access, i get the UserId with the following code when i open a form:

Private Sub Aperçu_Enter()
Dim sUser As String
Dim sComputer As String
Dim lpBuff As String * 1024

'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

End
End Sub

When executing a query, is there a way i can create a new table using fixed caracters + the UserId(sUser)?

Here is what i would like to have as the table name:
Tblx-Userxx

Thanks

 

[Nancy2]

I've never done it and am a little confused about what you want --- but I would try:

dim db as database

Set db = currentdatabase


dbs.Execute "CREATE TABLE Tblx-" & sUser & "(fill in the fields)"

close db

Question - you said when executing a query - of course there is not event to call this routine - would need to open some sort of form...

 

[Nancy2]

sorry -- to fast with the click --

not
dbs.Execute "CREATE TABLE Tblx-" & sUser & "(fill in the fields)"

in this example

db.Execute "CREATE TABLE Tblx-" & sUser & "(fill in the fields)"

 

[Aietoe]

Hi Nancy2!

What you have told me to do works fine, but it does not exactly answer my problem... Let me explain more what i want.

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 an error message..
« Couldn't lock table xxxxxx currently in use by user yyyy»
or « Table xxxx already exits»
or « You tried to lock table xxxx while opening it, but the table can't be locked because it is currently in use....»

What i would like to do is to "personnalise" the name of the table created by my query so that many users could execute a same query at the same time?

PS: The query is part of a full application with forms, report, macros... etc.

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

 

[Nancy2]

It should still work -- here's a simpler, but not a efficent way - sorry just quick and dirty with one of my databases.

I would run something like this instead of the query - or run the DAO example under a command button.

In a nutshell, you need to fill the table name with the contents on the variable. Did not work with a dash in it however (tbx-).

You cannot use a make docmd.openquery since there is no arguement feed for the table name.

You can fill the criteria with variables filled from a form you need to.

What happens after the temp table is created?

Sub test()

Dim strTableName As String
Dim strUser As String

strUser = "zzz"
strTableName = "tbx" & strUser

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT TripNo, TripDate INTO " & strTableName & " FROM tblTrip WHERE (((tblTrip.TripDate)=#6/4/2001#))"
DoCmd.SetWarnings True

End Sub

Hope this helps, let me know if I am still off base.

Nancy