I have split a database containing tblRDL. A form named "Entry" is used to input data that is saved to tblRDL. The backend is on a network drive and the frontend is on my PC's local drive. This works fine. I have copied the frontend database for another user to enter data. Due to volume and primary key issues, the data needs to be stored on two separate tables. The new user's table is tblRXS. I have changed the names of the table in the frontend everywhere possible that I know. I have added tblRXS to the backend as well. But, when I enter data into form "Entry" from the frontend database with tblRXS, the data is still saved to tblRDL on the backend. I have also used the linked table manager to update the links, but that does not work. I basically want several frontend databases on several different machines to save data to several different tables on one backend database. Any ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Change Table Link To BE Database
- Thread starter Roblin
- Start date
- Thread starter
- #3
Do these databases use user-level security? If so, there are some things you can do in selecting tables based on the user name. Just a thought.
That said, changing record sources for your entry form is not a particularly difficult task. Go ahead and link to [red]all[/red] user tables. Then use Select queries pointing to the table each user needs as the form's record source. Your form need not be changed at all. Just point the queries to the appropriate table(s). If you have comboboxes or DLookUp statements, etc., they can use the query as their source also.
You may want to add a routine to relink to the tables when the application opens - especially if you plan to add more user tables in the future. It just so happens I submitted an idea recently to do just that.
That said, changing record sources for your entry form is not a particularly difficult task. Go ahead and link to [red]all[/red] user tables. Then use Select queries pointing to the table each user needs as the form's record source. Your form need not be changed at all. Just point the queries to the appropriate table(s). If you have comboboxes or DLookUp statements, etc., they can use the query as their source also.
You may want to add a routine to relink to the tables when the application opens - especially if you plan to add more user tables in the future. It just so happens I submitted an idea recently to do just that.
- Thread starter
- #6
Thanks Remou. I had tried relinking and even creating new tables, but it didn't work.
MoLaker, I was hoping I could do something similar to what you suggested and not have to change the form at all. I have a function in a module that grabs the user's network ID in the frontend:
Function ap_GetUserName() As Variant
Dim strUserName As String
Dim lngLength As Long
Dim lngResult As Long
strUserName = String$(255, 0)
lngLength = 255
lngResult = wu_GetUserName(strUserName, lngLength)
ap_GetUserName = Left(strUserName, InStr(1,strUserName, Chr(0)) - 1)
strUserName = ap_GetUserName
End Function
I have a table for each (about 15) of what will be current users in the back end. All that I really use the user ID function for is to display their name on the forms at this point. I really wanted to use it so that I could just set up a table on the backend when a new user is added and have a query or code that would point to the correct table based on the function. It would make installing it on the users machines much easier. I have very limited coding skills. Is it possible to do that? I looked at the code you posted, but it's pretty complex to me.
MoLaker, I was hoping I could do something similar to what you suggested and not have to change the form at all. I have a function in a module that grabs the user's network ID in the frontend:
Function ap_GetUserName() As Variant
Dim strUserName As String
Dim lngLength As Long
Dim lngResult As Long
strUserName = String$(255, 0)
lngLength = 255
lngResult = wu_GetUserName(strUserName, lngLength)
ap_GetUserName = Left(strUserName, InStr(1,strUserName, Chr(0)) - 1)
strUserName = ap_GetUserName
End Function
I have a table for each (about 15) of what will be current users in the back end. All that I really use the user ID function for is to display their name on the forms at this point. I really wanted to use it so that I could just set up a table on the backend when a new user is added and have a query or code that would point to the correct table based on the function. It would make installing it on the users machines much easier. I have very limited coding skills. Is it possible to do that? I looked at the code you posted, but it's pretty complex to me.
Certainly, it would be possible to make the correct links based on their network ID. Your function is a good start. Now, look into how to create a QueryDef from code. You should be able to build a QueryDef with the source table defined based on the user's network ID.
Unfortunately, I am not going to be able to follow up with you on this due to a personal situation (sorry for the bait n switch). Hopefully, someone will pick up the reins and assist you where necessary.
MoLaker
Unfortunately, I am not going to be able to follow up with you on this due to a personal situation (sorry for the bait n switch). Hopefully, someone will pick up the reins and assist you where necessary.
MoLaker
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question