Trying to merge modified copy of database with original. 2

[plantfinder]

I'm not a programmer but have built a database for my business. Data is entered through the original at my office. I'm am still in the process of refining it so I take a copy back home and work on it there. Sometimes it's a few days before I get the modifications done and bring it back to the office to hopefully replace the existing one I copied. In the meantime, the original is being used to enter data.

I realized there would be issues in trying to merge the two. I figured that I did not want to use the modified database that I took home, as there was new data inputed in the tables(in the days I was not there, more data had to be entered)in the copy left at the office. I assume that if I tried to copy those tables into my "modified database, considering that there were relationships and key numbers that were established there that would get pretty screwed up. To avoid this problem I decided to copy the new (Changed) forms, macros, queries, reports - everything but the tables from my Modified Database into the existing Office Database so that the Data, Key numbers and relationships would not change. While this appeared to work initially,for some reason, on a later opening of the database, some of the changes made seemed to not be there. After that,trying to fix what wasn't working, regardless of how many times I tried to copy a Form, for example,from the modified database (which works fine) to the office database. The form was not being found by a macro. I triple checked the name and it was there. The form is just one example of what wasn't working, there were other similar issues elsewhere.

I guess my question is what is the convention for making modifications to a copy of the database and then trying to get those modifications back into the original. I would greatly appreciate any guidance. If it matters, I do not know any VBA code.


Thanks

Mike

 

[MajP]

I am hoping you have a split Front End and Back End design. If not you need to split your database, and save a lot of pain.

 

[JoeAtWork]

I second MajP's suggestion, split your database to a frontend/backend solution. The backend contains your tables, the frontend has everything else. When you deploy your new front end, all you will need to do is relink the tables throught the Linked Table Manager.

 

[plantfinder]

Thanks for the great tips. After reading up on it, it seems like the perfect solution. I have few questions though:

1) If I make changes to the front end and go back to the office where the front end (before my modifications) and back end reside ( I will have them in the same folder on the hard drive), do I simply remove the "old front end" and insert my new "modified front end" into that folder? Assuming I haven't modified any tables or table structure, should everything work or do I actually have use the link manger (for which the documentation seems to be lacking)

2) if I do modify tables or relationships or add new tables when working on the back end I assume I would have to keep track of them somewhere and then implement those changes to the existing office copy of the back end. I would then link the tables (again, I'm hoping to find better documentation on the Link Manager). Can you tell me if this is the correct procedure to follow?.

3) I have not split the database copy yet that remains in the office and new data is being entered now. I will not be in the office for a few days but have a copy of the un-split database that I copied just before I left and took with me. Can I split the copy I have with me, work on it, take the modified front back to the office in a few days, split the existing office copy then and insert my modified front end that I've been working on?

I hope this is clear and not too long. Any further help would be appreciated.

Mike

 

[JoeAtWork]

1. Yes, as long as the table links point to where the actual backend is. The easiest solution is to do your development where the mapping is exactly the same, i.e. if in your office the backend is on Q:\DBData\MyData.mdb then if possible on your development machine have a Q drive and create the same folder.

There isn't much to the Linked Table Manager. You just select the tables you want to relink, and press OK. It will try to confirm that the backend file exists according to the current mapping, and if it doesn't it will prompt you to enter the new path. Typically, you do a Select All, tick the checkbox for "Always prompt for a new location", press OK which opens a Browse File dialog, navigate and select the backend MDB file, and press OK and in a few seconds it should refresh the links.

2. Yes, if you change the structure of the backend tables or relationships, then you will need to make corresponding changes in your office copy. Whenever I do this, I write down every change I make in a separate text file so I don't forget anything. You can only make the changes when everybody else is out of the database.

You do not need to do anything on the frontend to get the relationships. The relationships are inherited from the backend database.

3. Basically, yes. What I would do is a make a new database and import all the tables from the office copy into the new MDB. This will become the office backend file. Then use the Linked Table Manager to reset the links.

Ideally, you will want every user to have their own copy of the front-end. What is often done is to store a pristine copy of the front-end on a network folder, then write a batch file that copies network front-end to some pre-determined folder on the user's computer, and then launches Access, giving the path to the local front-end as a command line argument. The shortcut the users will use to start the application will point to the batch file (rather than the network copy of the front-end). This simplifies distribution of an updated front-end - you just copy it to the location where the batch file is copying it from.

 

[plantfinder]

Joe,

Thank you very much for your in depth and more importantly, easy to understand, answers.

I'm sure the paths will be different but is sounds like I can easily redirect it with the Link Manager by just browsing to the proper file

"3. Basically, yes. What I would do is a make a new database and import all the tables from the office copy into the new MDB. This will become the office backend file. Then use the Linked Table Manager to reset the links."

So would this be just an empty shell and would the relationships stay the same? And I guess you are suggesting this instead of splitting the Office Database?

For now, there is really only a single user but this may change in terms of having to network in the near future.

Thanks again for your help

 

[JoeAtWork]

So would this be just an empty shell and would the relationships stay the same? And I guess you are suggesting this instead of splitting the Office Database?

I'm pretty sure that by default the relationships are preserved when importing a table, and if not it is an advanced option in the import wizard to do so.

You could split the Office Database, you will end up with the same backend either way, however you won't need the front-end as you will be replacing that with your updated copy.