| Author |
Topic |
|
wallis1905
Starting Member
5 Posts |
 Posted - 2007-01-19 : 14:49:14
|
| Hello,Just wondering if it's possible to save design changes to MS access tables and reports etc while other users in an office network are using the same database? Where I work we have to get everybody to close access before such a change can be saved. Can this be changed?Sorry if this is real easy, I dont know a lot about it. |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-01-31 : 19:42:30
|
| Hi wallisFirstly, you should have the queries and reports in a separate .mdb file to your data (using linked tables). Each person should have a local copy of the "client" .mdb file (ie the one with the queries, forms, reports and modules). Then you can definitely make design changes to reports while people are using the system. You can then distribute the new version in an orderly fashion (by copying it to their local drive) and control the correctness of results they get.However, you can't make design changes to a table if people are accessing it. I can't see a good reason for being able to do this - enlarging a column width? I assume you're not trying to change type!You can solve this problem by using code or queries to make the change in a transactional manner - which will run OK if there is no conflict at the exact time the code runs.Post back if you need more help.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
wallis1905
Starting Member
5 Posts |
Posted - 2007-02-11 : 15:01:46
|
| ok then,Thanks for that. I guess I must live with it so. I believe that our system is set up so that all the tables, reports etc are located on a shared directory. Ah well.Thanks anyway |
|
|
|
wallis1905
Starting Member
5 Posts |
Posted - 2007-02-17 : 06:23:22
|
| Ok, I have looked into this a bit more and seem to have been sucessful. I have allowed one database to be the master and created three client "Report/Form" databases (For 11 users I guessed three should be enough to allow an unused database to be available pretty much all the time) . These only contain the Master table we use, a few queries and all reports and Forms. I have linked the main table we use in the master database to each of these three as you suggested using a macros. I'm presuming a macro is the way to go here? The reason I have not assigned a "Report/Form" database to each user is that I dont want say the report "Valves" to diverge to much from each other on individual databases. However, I know I am just minimising but not eliminating this problem. I know you mentioned transferring edited reports to the other databases to maintain consistency but of course I'm sure this can potentially cause trouble also, but perhaps with the right management rules it can be successful. Is this done with a macro too (Copy Object)?If I did give each person there own databse does this mean a seperate macro is required for each user, and for each table? I have only linked the master table so far but we have at least another 40 used tables, does this mean 40 x 11 = 440 macros? I'm presuming I'm missing something here. Any help would bve great, I'm learning a lot here. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-02-18 : 18:49:20
|
HiOK - I'm a little confused by what you've written, so maybe you could explain what your database is actually used for? Also, I'm not sure what you meant about the macros - I don't think I mentioned macros at all??There are two ways I've seen this done.----Option 1. Each user copies the "Client" database (the one with ONLY forms, queries and reports) onto their local machine and this Client is linked to the "Server" or backend database which has ALL the shared tables. If you link the tables to the master copy before each user copies it down then the link does not need to be updated.This means you have only one variant of the Client - the "master" client- which stays on the shared drive as a means of distributing it - whoever needs it copies it down to their machine - and would only re-copy it after you've made some new version.My attempt at an illustration:(shared drive) G:client.mdbserver.mdb(local drive of each user) C:client.mdb Note that the copy of the client.mdb on the local drive is identical to the copy of the client.mdb on the G:\ drive. When you want to release a new version of the client, you copy it to the G:\ drive and tell everybody to replace their existing version with the latest one.There are various ways you can manage the versioning to ensure everyone has the latest, but lets cross that bridge when we come to it.Option 2. Each user access the "Master" client directly on the shared drive rather than copying it down. The down-side of this approach is that when you want to release a new version, the file may be locked if someone is using it. I prefer option 1.Illustration of option 2.G:client.mdbserver.mdbC:--nothing--- Everyone access the G:\client.mdb directly. The benefit of this is that they are always using the latest version (they don't have to copy it down). The negative is that while anyone is using it, the client file will be locked which will mean that you will have to get everybody "off" before you can update it with a new version.----Either way, you should only have ONE version of the file on your system - each user should be using an IDENTICAL copy of the Client. Otherwise you are in management hell.You make changes to your "master" copy of the Client - and then when people want to have the updated version of the report, they copy down the entire new client version to their local machine (ensuring every report/form/query is at the latest version). Let me know if that makes any sense.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
wallis1905
Starting Member
5 Posts |
Posted - 2007-02-19 : 18:44:48
|
| Thanks again,Can you tell me which database new data must be written to after an isssue of a client.mdb to the local drives has been made? In our office users will generally be entering data or changing data sporadically at any time, with some busy data entry periods every few weeks. Is it suited to a lot of people entering data? People will need to edit and create reports pretty much every day too.I think we are currently using option 2 but we are not all close together and are currently having to go on the "hunt" for access users regularly to save changes to some work you did! Perhaps I am missing the point? Also I'm not quite sure how to implement what you are saying in practise. I know that you can link the tables via a macro "link" or "Transfer database" but it's something different you say. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-02-26 : 18:28:53
|
Hi WallisThe concept with Option 1 is that the client.mdb is linked to a central server.mdb. The data is all in server.mdb on the shared drive but everyone accesses it (to update data) via the client.mdb file on their computer. It's pretty much the same as having an application installed locally which presents a UI for a server somewhere else.You link the tables as follows:* Open the client.mdb database file.* From the database window, click tables to view the list of tables. In theory there should be no tables there (they are all in the server.mdb file).* Click "New" to create a new table and choose "Link" - then browse to the server.mdb file (on the network drive) and open it. A list of the tables in that mdb will then open and you can select as many of the tables you want to link to (or all of them). Once you've done this, the client.mdb file should be able to be circulated to as many users as you like at any location on the network, and all the copies will link back to the server.mdb file, where all the data is stored. In theory, all the queries, forms, reports and modules should be in the client.mdb which is circulated, and the only thing which should be in the server.mdb file should be Tables.HOWEVER - you sound like you might be doing something quite different.??quote:
People will need to edit and create reports pretty much every day too.
Really? In this case they can edit and create as many reports as they like in their own copy of the client.mdb file without disturbing anyone else at all. However, if you want EVERYONE to have ALL the reports created by EVERYONE then you'll need to work out some way to redistribute what everyone is doing. I can make some suggestions if this is what you want.Otherwise, my suggestion is that you build an form which allows people to "create" a report by choosing from a number of "templates" and then manipulating filter parameters. This is the usual scenario within an organisation. You're possibly creating a problem for yourself if you have multiple access developers all randomnly building new reports in Access without any central control.Anyhow, you may still have more questions, so please ask.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
wallis1905
Starting Member
5 Posts |
Posted - 2007-03-11 : 07:02:50
|
| Ok I think I understand this. I'll be trying to do this for real in a few weeks so hopefully I'll gett it right before that. I'll let you know how the testing goes! |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-03-12 : 19:25:11
|
| Yep - get back to me especially if you need more help.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
Saving changes in group