How can i name a table from a username

[Aietoe]

Hi!

On an openform event, i get the userId with the following code:

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

Since my query (that create a table)is used by multiple users, is there a way i can name the table created by using the userId(sUser)so they won't interfere each other?

Thanks

 

[THoey]

You could probably do this, but I wouldn't want to have to maintain this system. Every form, report, query you create would end up being a nightmare to write. I STRONGLY suggest that you just add a column to your table that specify's the user name and then just limit the data that the user can see, by using their user id in the where clause... Terry M. Hoey

 

[Aietoe]

Hi Terry!

Thanks for the solution, but unfortunetely because of the # of users and also the size of the files, i don't think that this will be possible.
Also, i don't see why i should have to modify my forms or my reports.

Since the creation of the tables using the userID seems possible, could you tell me how to do this so i could at least give it a try.

Thanks a lot.

 

[THoey]

If you are going to have any forms or reports that will pull info from these tables, you will have to create a copy of each form and report for each table or will have to make your code jump through hoops to get one form/report to work with an unlimited number of tables.

Sorry, but when I said you could probably do this I did not mean to imply that I knew how to do it. I have never learned because I have never been in the situation where I had a table per user. I am sure someone will come along that will be able to help...

Best of luck... Terry M. Hoey

 

[mymou]

Hi Aietoe

Just as a shot in the dark, have you split the db?

This could be a soultion, as each user would have their own 'front end' with their own 'temp table'. Wouldn't need new tables for each user - just a separate front end for each user.

Just sounds easy.


Stew "Even a stopped clock tells the right time twice a day."

 

[mymou]

If you really want to start creating tables like that (and im sure that you dont!!!)

use SQL in code.
DoCmd.RunSQL "CREATE TABLE TableName(FirstName TEXT, LastName TEXT);", -1

Stew
"Even a stopped clock tells the right time twice a day."