 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / November 2004appending to a front end machine
Hi, I've got a database where it may be required for the manager to append a record from a backend database on the network, to a frontend database stored on a local machine. I have got the database set up so that when users login, it automatically records their computer name in a table. Is there anyway to transfer data from the backend into a specific table on the frontend using the computer name? Could an append query do this? Thanks It is not a good idea to store any data in the front end, other than infrequently-changing lookup tables, for example, US States and Canadian Provinces and their abbreviations, or company structure). It is perfectly reasonable, however to have some local work tables in a separate database on the local machine, linked from the front end just as the backend tables are linked. If the use of the data you mention is temporary, then you'll find an example on MVP Tony Toews' site for creating a temporary database to contain the temporary tables. If you'll give us a little more information on what you have and what you are trying to _accomplish_, perhaps someone could provide a more specific suggestion. Larry Linson Microsoft Access MVP > Hi, > [quoted text clipped - 9 lines] > > Thanks Thanks for the reply. Data is only stored on the frontend temporarily. Basically, the database is designed to manage callback appointments. When they log in, the user selects the callbacks they want to handle. These get appended into a table on the frontend and a flag set on the backend to say it is in use. When the user has dealt with the callback, it is updated on the backend and deleted from the front end. What I want to achieve is this: - A separate frontend has been built as an overview tool. This shows any outstanding callbacks, new callbacks or callbacks currently unassigned. - This is used by 2 or 3 team leaders who, ideally, will be able to assign specific callbacks to specific users. - This would involve appending one or two records from the backend into the temporary table on the specific frontend that the desired user is using. Is that a bit clearer? Would it be possible to do this? Thanks > It is not a good idea to store any data in the front end, other than > infrequently-changing lookup tables, for example, US States and Canadian [quoted text clipped - 28 lines] > > > > Thanks You will find an example of using CreateDatabase to create a temporary database to hold your temporary tables at MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr/ . The reason for doing that is to avoid frequent compacting that would be needed if you created temporary tables in your front end, or temporary records which you deleted -- Access does not recover the space used by deleted tables or records until you compact. However, if you create the temporary tables in a temporary database, when you are done with them, you can just remove the links and delete the temporary database. If you want the "overview" application to be able to look at what is in the temporary tables, that will complicate matters somewhat, but you will be able to create a table in the main backend database through which you pass the path and file name of the temporary database that is in use, if any. Link it from the users' front ends and from the team leaders' front ends. If I understand your requirements, you would create the temporary table from the team leaders' front end, link it, fill it with data, and pass the path and file name via the table in the main back end. On the other hand, it seems to me that you could simplify the design just by adding a few fields in the back end... "team", "team member assigned", "actively-in-use"... and not worrying about the temporary tables. You would have to determine some way to "assign" records to the proper team, allow the team leader to assign them to a team member, and set the actively-in-use flag. Then, when the team member is finished, he/she would reset the actively-in-use flag. Yes, you can do what you want. Yes, you can do it in a way that won't require frequent compacting. Yes, it will take some VBA skills, but it isn't "rocket science". Have I answered your question? If not, post back and clarify for me. Larry Linson Microsoft Access MVP > Thanks for the reply. > [quoted text clipped - 49 lines] > > > > > > Thanks Thanks again for the reply. It's a good idea about the temp database and I may well use this. There's only one table that would need this and it may well be beneficial. I think I've maybe not explained the situation quite right (or I'm not understanding your response fully). Here's more of a complete process: - Users have a frontend running all the time. - Users can add a callback as and when necessary. When saved this gets sent to backend. - Each day when the user logs in, they check all callbacks on the backend for that day and choose theirs. - These then get appended to frontend and flagged on backend. - When the user completes these they are updated on backend and deleted from frontend. In addition to this, we would like to be able to do the following: - Team Leaders have overview facility. - Any callbacks in backend that have not been claimed, team leaders should be able to assign these to a specific person. Even if I use the CreateDatabase method, this would be stored on hard drive, for performance reasons. Linked tables on the network have tended to slow database quite considerably in the past. This means that the Team Leader would, ideally, be able to append records into the temporary database. I do have a reasonable amount of experience in VB so coding isn't something that worries me. Your help is much appreciated > You will find an example of using CreateDatabase to create a temporary > database to hold your temporary tables at MVP Tony Toews' site, [quoted text clipped - 98 lines] > > > > > > > > Thanks >Linked tables on the network have tended to slow >database quite considerably in the past. The three most common performance problems in Access 2000 or newer are: - LDB locking which a persistent recordset connection or an always open bound form corrects (multiple users) - sub datasheet Name property set to [Auto] should be [None] - Track name AutoCorrect should be off If the problem is for everyone when starting up the MDB then it likely needs a decompile. For more information on these, less likely causes, other tips and links to MS KB articles visit my Access Performance FAQ page at http://www.granite.ab.ca/access/performancefaq.htm Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm I'm afraid the reason the databases run slow on our network is down to two things: - Poor network speed in general - Computers that are 6 years old and very much outdated. > >Linked tables on the network have tended to slow > >database quite considerably in the past. [quoted text clipped - 20 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm >- Each day when the user logs in, they check all callbacks on the backend >for that day and choose theirs. >- These then get appended to frontend and flagged on backend. >- When the user completes these they are updated on backend and deleted from >frontend. But why bother even putting them in the front end? Leave the data in the backend? Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.