Random table name

[Tupacmoche]

Is there a way when using a make table query to append a random number to the table name so individual user don't over write the table. So, if the base name is Mytbl with the addition of the random number it would be Mytbl456, or user 1 and Mytbl898 for user 2 ect.

 

[Ranman256]

Use the DateTime...
VName = format(date,"yymmdd-hhnn")

 

[Tupacmoche]

In order to know who is logged in I use a function =GetUserName(). Can this be done for the table name? Would this work Select Distinct mytable.persons into =GetUserName()

from mytable ?

 

[The_Doc_Man]

That would depend on what punctuation is imposed by your login. For instance, if your login name is Fred.B.Goode, that wouldn't work because the dot character has special meaning in the context of a table name. (It introduces a table property selection.)

I'm going to read something into this question, and if I'm wrong, I'm wrong. You are trying to keep separate user temporary data separate, aren't you? If so, you are going about it all wrong. If I correctly guessed your goal, your correct approach is to split the database into front end and back end (FE/BE split, and lookup "Splitting a database.")

If you do, then each user copies a private copy of the FE and just shares the BE. If you do that, then any temporary tables can be in the FE and anything more permanent goes into the BE. Since users have private copies for the FE, they don't see anyone else's FE tables, only their own. So, no chance of overlap.

If that is NOT your goal, because you were keeping data separate for each user in a created table, but the structures for all of these tables are the same, you should instead have a single table of that structure with a user ID number. You get that user ID number by looking up the name via GetUserName() in a table of authorized users. Then if the user wasn't authorized, terminate the application. If the user WAS authorized, you just tag user records with the ID of the user to whom those records apply as one extra field.

If you do that, your users only see their own data but you don't have the problem of creating randomly named (or programmatically names) tables. You would quickly find that you were getting a badly bloated database with all those tables.

 

[Tupacmoche]

You hit in right on the head. And, I appreciate your elegant solution. Now, some clarification. The database is already split and there are about twenty (20) users that can login to this application at any given time. It is run from a terminal server that these users RDP into. So, while the database is split all these users are on the same box at the same time. A table is made that has the data of user1 and user2 and user3 if they get on at the same time. So, there data becomes commingled with other users data when they print a report. Given this more detailed explanation will your solution work for my scenario?

 

[gemma-the-husky]

You hit in right on the head. And, I appreciate your elegant solution. Now, some clarification. The database is already split and there are about twenty (20) users that can login to this application at any given time. It is run from a terminal server that these users RDP into. So, while the database is split all these users are on the same box at the same time. A table is made that has the data of user1 and user2 and user3 if they get on at the same time. So, there data becomes commingled with other users data when they print a report. Given this more detailed explanation will your solution work for my scenario?

Each user should have a distinct copy of the database. Then you don't need to arrange for different table names, as users won't interfere with each other.
If you still need the unique table name, why not simply "base table name" & " username"

 

[Frothingslosh]

This thread is effectively a continuation of this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=290907 because the OP doesn't want to create a split database and assign each user a copy.

 

[static]

If table1 exists returns table1_1

Private Sub Command0_Click()
    Debug.Print NewTableName("table1")
End Sub

Function NewTableName(tname As String) As String
    s = tname
    Do Until TableExists(s) = False
        i = i + 1
        s = tname & "_" & i
    Loop
    NewTableName = s
End Function

Function TableExists(tname) As Boolean
On Error Resume Next
    TableExists = Len(CurrentDb.TableDefs(tname).Name)
End Function

 

[The_Doc_Man]

You can try to pound 20 square pegs into one round hole OR you can bit the bullet and assure that each RDP user logs in to a unique directory/folder. Put the COPY of the FE in the unique folder. Let them all access the BE in a shared folder. OR you can try to re-invent the wheel with tedious issues of building tables dynamically on the fly until your database gets so bloated that you have to manually take it down and clean out the crud. Your choice.

Read my post at the end of that other thread to see why you DO NOT want to have your users share the database in the way you are describing.


Thanks, Frothingslosh, for pointing out that this user has a history of not taking suggestions.

 

[Tupacmoche]

This is a follow-up to what 'The Doc Man' suggested. I have implemented his suggestion, I have added a column for UserID and load it by using GetUserName() function. Now, when records are inserted into the table they can be distinguished by user1, user2, user3 ect. Now, to the new problem. Even using this approach Access is failing in the following way. I tested having another user update the table simultaneously and the table that is written to gets corrupt. Sometimes the other users record get my ID, on other occasions rows are created for both user1 and user2 with the same records with our own IDs.

Here is the code: Please note that there is a dialog box before this code that asked for a "job ID" for job selection.

INSERT INTO [Buyback template_t] ( ID, OrdNum, Cust, CustLocation, Ref, NetRef, price, Cost, Salesperson, Addr1, Addr2, City, State, Zip, Fax, freight, freightout, offset, offsetNote, UserId )

SELECT [BuyBack t3].ID, [BuyBack t3].OrdNum, [BuyBack t3].Cust, [BuyBack t3].CustLocation, [BuyBack t3].Ref, [BuyBack t3].NetRef, [BuyBack t3].price, [netref]*[price] AS Cost, [BuyBack t3].Salesperson, [BuyBack t3].Addr1, [BuyBack t3].Addr2, [BuyBack t3].City, [BuyBack t3].State, [BuyBack t3].Zip, [BuyBack t3].Fax, [BuyBack t3].freight, [BuyBack t3].freightout, [BuyBack t3].offset, [BuyBack t3].offsetnote, =GetUserName() AS UserID

FROM [BuyBack t3]; :banghead:

 

[Frothingslosh]

Are you using a split database the way we advised you to? These sound precisely like the shenanigans that splitting the database helps prevent.

 

[Tupacmoche]

Yes, the DB is split on a terminal server but both parts are in the same directory. So, the back end and front end are in the same place. All the users RDP into this terminal server to access the application. Does this make a difference?:banghead:

 

[Frothingslosh]

Yes, they each need to use their own copy. Over on the other thread, Paul and Doc suggested giving each user a personal directory, and you put a copy of the front end in each one, then have the users run that particular copy.

Basically, you're still running into the side effects of multiple people trying to run the same procedures and queries from the same database at the same time, and that's something Access often chokes on. Get them to each run their own copy, and you may well find that all these problems vanish.

If you're worried about keeping the front ends updated after ward, do a search on 'front end update' or 'front end auto-update' and you should get a list of a bunch of different approaches.

 

[Tupacmoche]

Using the current approach is it possible to 'Lock' the table (Access 2007) when the records are written which is a few seconds and do this for all users and immediately release when done? Can this be implemented in Access 2007 with success ?

 

[Frothingslosh]

That would be far more work - and lower ROI - than just doing what we've repeatedly told you has to be done. And it won't stop the errors generated by multiple users trying to execute the same procedure at the same time.

 

[Tupacmoche]

Would you kindly provide any links for that solution. I would like to review the effort and then decide.

Best

 

[The_Doc_Man]

Tupacmoche - if EVERYONE shares the same front-end file in the same folder, you have done NOTHING to help your problem. We are telling you to have DISTINCT and SEPARATE front end files for each user. If people RDP into the same account with the same name, you have no security. If people have equal RDP access to the same folder, you STILL have no security. You need to arrange for everyone to have a distinct home directory into which you can drop a distinct COPY of the true front-end. Otherwise, you are spinning your wheels uselessly and going nowhere fast, about like the "stuck in the mud" scene from the movie "My Cousin Vinnie."

If you don't like having continued problems, take the suggestions being offered to you by Frothingslosh, Dave ("Gemma"), and me. If you enjoy unwieldy databases, keep it all together. It's your app so it's your choice.

 

[Frothingslosh]

Your desired solution is a stop-gap that fails to address the underlying problem and won't even work 100% of the time. The solution you've been given repeatedly, on the other hand, will. Your solution is the equivalent of taking a couple aspirin to cure your appendicitis, and will, in the long run, prove equally ineffective.

You've been told what you need to do to fix this. If you bow to reality, anyone who has posted on this thread will happily help you. If you continue to refuse, you're almost certainly going to have to do it on your own.

Also, I have no links to give you because it's a terrible solution that no competent database developer intentionally does. Our goal is to lock only the records we need as we need them; locking an entire table every time someone decides to run an append query is just terrible design and utterly defeats the purpose of having a multi-user database to begin with.

ETA: I have been fiddling with Access off and on since Access 2 was a thing back in 1995. Doc has been involved in database management and computer work for quite a bit longer than that, and did so for the Department of Defense. He was even, if I recall correctly, an Access MVP at one point. If you're not going to listen to me, then listen to him; when it comes to both Access and database development, the man knows what he's talking about.