update and append

[MvP14]

I split my database. In the BE I have a table Sourcestab. In the FE I used a make table query to create Sourcestab_fe.

I have a form to add records to Sourcestab (BE).

I also have a search form searching through Sourcestab_fe.

On opening the search form, I want to update Sourcestab_fe (add new records from Sourcestab, delete records that have been deleted from Sourcestab and change modified records in Sourcestab).

I try to use the following SQL on opening the search form:

UPDATE Sourcestab_fe RIGHT JOIN Sourcestab ON Sourcestab_fe.Id = Sourcestab.Id 
SET Sourcestab_fe.Id = Sourcestab.Id, Sourcestab_fe.NR = [Sourcestab].[NR], 
Sourcestab_fe.Author = [Sourcestab].[Author], 
Sourcestab_fe.Date = [Sourcestab].[Date], Sourcestab_fe.Title = [Sourcestab].[Title];

Trying to run the SQL I get an error message: "Can't update Sourcestab_fe.Id. The field can't be updated".

I've been trying to figure out where it's going wrong, but I'm falling short. Who can help? Thanks in advance.

 

[John Big Booty]

Why are you putting a new table in the FE of your DB

The normal schema for a split DB, is that ALL the Tables reside in the BE of the DB in a central location (server) and then the FE's (which contain all the forms, queries, etc., but not tables) reside on each individual users computer.

 

[MvP14]

Because I have users do a query in the FE table, where they can mark the results they want to print.
If I have users mark the results in the BE table, I get conflicts: if two users do a search in the same table at the same time, and they both mark results they want to print, then they get each others results.
If there is a different way to solve this, I'll be happy to learn about it.

 

[boblarson]

Why are you putting a new table in the FE of your DB

The normal schema for a split DB, is that ALL the Tables reside in the BE of the DB in a central location (server) and then the FE's (which contain all the forms, queries, etc., but not tables) reside on each individual users computer.

Yeah you got me confused too.

 

[Catalina]

If the database has a username/password system in place you could store the results in a BE table along with the username.
Then you can restrict the user to see only those records that has his/her username attached to it.

If you store it in the FE the results may get lost when you have to update the FE with a new version. Also, if a user borrows someone else's computer (in case his own is unavailable) he will not be able to get to his own data. If it is stored in the BE he can access it from any machine.

Catalina

 

[MvP14]

I'll try to put it clearer: originally, I made a db for personal use. It contains bibliographic references. In my original db I added a search form, where I could select all the works of a certain author, ... Because I don't always want to print all the works of a certain author, I added a Yes/No box. Going through the search results, I set to yes the ones I want to print, and on choosing print, I have a report, with filter Yes/Nobox = Yes.

Now, some other users will use the db as well, so I chose to split. They can add/modify records in the BE table.

But I figured that if several users simultaneously search the BE table and check the results they want to print simultaneously, they would end up printing each others results through the report.

So I figured I'd solve this by making a FE table and link the search form to the FE table, so each user would check results in the FE table and thus only print his own retained search results.

But again, if there is a better solution, I'll be happy to hear.

EDIT: I do not have usernames/passwords in place. I also think that the use of the db will be too limited to currently invest in installing usernames/passwords.

EDIT: oh, and one more thing: the FE table will never contain data that are not in the BE table, since the fields in the form that contains the results are locked; all data are necessarily in the BE table. The FE table is simply a local copy of the BE table for searching and marking purposes. The search results of a individual users should not be stored, since they are only momentarily relevant. But of course, in this system I do need to update/append the FE table on opening the search form, else it will not contain all the data, and that's where I got stuck.

 

[John Big Booty]

I would probably have a table (in your BE) that records a ReferenceID and the UserID of the user who selected that reference for printing, as the user selects the jobs he wishes to print. In that way a number of user could be selecting various references for printing at the same time. Once the user has finalised his selections and printed the jobs, you would use a delete query to remove all that users print requests from the table.

 

[MvP14]

Okay, I get that and I'll look into it.

Say however for now I want to distribute a fe_version without users.

1. Is getting rid of the fe_table and just run the risk that people will do a simultaneous search preferable to distributing a version with the fe_table?

2. I figured a bonus of the fe_table is that users would also be able to perform searches when not connected to the network with the BE db (e.g. laptopusers on train/plane, ...). Can I achieve this, with checkbox ability, without having a FE_table?

3. Say I want to go with the fe_table in the current version: should it be possible to get the update query running, or is that just impossible in the current setting?

4. I'll look into adding users to the Db, but after having given it some thought I also consider going for a search log table in the BE, adding a record with time stamp everytime a new search is conducted, using that field in the search log to record refID in a BE table. This way I still don't have users, I don't need the fe_table, I don't need the delete query and I get to follow the use of the db. Would this solution be equally sound as the users option, or are there shortcomings I'm overlooking?

Thank you for the useful feedback.