Multi-User & Updating tables

[Deirdre Marie]

Multi-User & Cannot use Make-Table Query

Good Afternoon~

After about a 3 week hiatus, I've decided to dive into my ACCESS project which caused much grief last time I was updating it.

I have a database which will contain about 7k records at all times. This database will be used by about 12-20 people at any given time. I understand the importance of splitting it and plan to do so. However, my last design of it included a Make-Table query which did not work at all when more than 1 user was in the database.

OK - so remove the Make-Table query and try another way.

Easier said than done =(

The 7k record database contains information vital to modeling and predicting job outcomes. However, I wish for this database to not only use that data but to accept new data in order to better model the job. Each user has their own ID. Each user is responsible for inputting the information for their jobs in order to model it better. So in order to do this originally, I established a Make-Table Query.

The Make-Table query received information from existing tables and was then used to make a subform on the new data entry page. On this page, the users were able to see only their job information for the date range they selected rather than all 7k records. On this page they could also enter the new information for modeling the job and then, when complete, an append query was executed to add the new information to another table. IN a one-user environment, this worked marvelously.

So now, I've tried to replicate this action using a regular query as a subform to pull only the information the user needs to update (instead of all 7k records). I created unbound controls on the subform for goal and appointment information, but when a user enters a goal, it is immediately replicated for all jobs within the date range selected and not just the 1 job. I then tried adding the eventual new table to the query and making the aforementioned unbound controls, the bound ones on the new table. Unfortunately, you cannot change "query" information and therefore the user is unable to add new/different goal or appointment information.

I know this message is lengthy, but I am honestly at wits end trying to figure this out. I have this wretched feeling the answer is TOO easy and I am making this far more difficult than it really is.

Any assistance is, as always, GREATLY appreciated.

 

[Pat Hartman]

Access attempts to make every query updateable. If your query is not updateable, you have done something to prevent it. One of the things that prevents a query from being updateable is using aggregate functions such as Sum() or Avg().

Generally speaking, tables and queries are interchangeable. Therefore, wherever you were using a table, you could replace the reference with one to a query. So for example if queryA makes tableA and queryB reads tableA, you could change queryA to be a standard select query and have queryB read queryA rather than tableA.