List of db objects date/time of last update

[RenaG]

I have a db that was put into production because a number of users needed to start using it but there is still a lot of work I will be doing on it. I am trying to keep a list of forms, tables, etc that I update so that I can remember which ones to export to the production db. But I am afraid I will get caught up in what I am doing and forget to write them down. It would be really helpful if there was someway I could generate a list of the db objects and the last update date/time. Does anybody know of a way to do this?

TIA!
~RLG

 

[boblarson]

You wouldn't have this problem if you would just split the database and give them each a copy of the frontend. Then you can simply work on the frontend and then redeploy when ready and nothing is messed up or an issue.

 

[RenaG]

You are right and I have looked into splitting the db. But it wasn't in my court to make that decision. With the issues I am now dealing with perhaps I could make a case to split the db. However, I still will have this problem with modifications to tables and would still find a list of objects changed useful.

Thanks for your input.

~RLG

 

[boblarson]

, I still will have this problem with modifications to tables and would still find a list of objects changed useful.

Thanks for your input.

~RLG

I don't know that there's a reliable way to get that information. At least I've never seen one.

 

[RenaG]

Hey Bob,

Well, if you don't know of a way then there is a very good chance that there isn't a way!

Thanks for replying.

~RLG

 

[gemma-the-husky]

i am sure you can iterate the containers collection for each of the datbaase objects, and get at this info

from memory its something like


dim doc as document

for each doc in containers("tables").documents
....
next


hope this gets you started.

----
I agree with bob though. each user should have a separate copy of the database anyway, and the data should be split - then you just issue a complete new copy of the front end database

 

[RenaG]

Sorry for the long gap in time.

In our set up, each user doesn't not have access to their hard drive. Everything is done from the network. It is my understanding that a typical split of the db would be to put the forms, reports, etc on the user's computer and the tables on a shared drive. In my environment, should I make separate folders for each user so each can still have their own copy of the db (making a shortcut to their desktop that points back to their folder)? Or just have one copy of the db that everybody would run?

TIA for your help!
~RLG

 

[boblarson]

You would give each their own copy of the frontend in a separate folder. Do NOT run the same file for everyone. Not only will that be a greater risk of corruption but you will likely experience other detrimental effects.

 

[RenaG]

Hi Bob,

I have been reading through the link you sent in another thread:
http://www.kallal.ca/Articles/split/index.htm

He talks about adding a new table once the db has been split but his instructions are for 2003 (I think). How would I go about it in 2007? Do I import it from the developmental BE to the production BE and then go in the Linked Table Manager?

TIA!
~RLG

 

[boblarson]

Yep, you linkto the development BE and then when time to move to production you use linked table manager to do the relinking.

 

[RenaG]

Hi Bob,

I am playing with this to make sure I understand the steps before doing it for real. I have a BE and FE of a copy of my production DB. In my development area, I split my db there. Then I imported some new tables into the "production" db from the development db. When I try to link these tables (which do show up in the Navigator pane) - with the "Always prompt for new location" checked - I drill down to the shared drive where the "production" BE lives; click on the BE file and it comes up with an error window that says:

The Microsoft Office Access database engine could not find the object 'CancerSubtype'. Make sure the object exists and that you spell its name and the path name correctly.

What am I doing wrong?

TIA!
~RLG

 

[boblarson]

Are you sure you IMPORTED the new tables from the development db (BE) into the production db (BE) and not linked them accidentally?

 

[vbaInet]

Just fyi MSysObjects has a DateUpdate field so you can use that.

 

[RenaG]

Well, let me backup and ask this. After I split the production DB and the development DB (which I have already modified with changes). I just copied the development FE over the production FE and then tried to import the new tables. Is that how you update the FE?

~RLG

 

[boblarson]

The process is this:

1. If new tables are needed in the backend, I create them in the development backend.
2. I link the new tables in the development backend to the development frontend.

3. When time to move to production I then will import the new tables into the production backend from the development backend.

4. Then I just have to copy my development frontend to my spot for production and then relink to the production backend using linked table manager.

Does that help?

 

[RenaG]

Yes, thank you, that was very helpful! I copied the development FE into the production FE before importing the tables. Would you do it the same way if the tables are pre-existing tables that have been updated (such as look-up tables) - import them into the BE before updating the FE?

Thank you, thank you, thank you for all your help!

~RLG

 

[boblarson]

I would do everything in the backend first and then deal with it in the frontend. Most of the time there is no need to do anything but relink on moving to production.

 

[RenaG]

Alrighty!! Thanks again. Have a great day.

Cheers!
~RLG

 

[RenaG]

Ok, I'm back with another question. When I imported into the Production BE a table that I had modified in development, it created a new table with a 1 at the end of the name. I tried just changing the name of the new table by removing the 1, which it seemed willing to do, except then I got a message that said that I couldn't rename the table until I deleted its relationships to other tables. What a pain in the you-know-where. It looks like it would be easier to hand modify these tables than to try importing them. Unless you have a way around this hassle.

~RLG

 

[boblarson]

That is a pain but it is something that, if you have Referential Integrity defined (and that is good to have defined), you do have to unlink them in order to delete because otherwise it can't maintain the R.I.

I wasn't thinking that you were modifying existing tables to add fields (as that should be a very, very RARE occurence if you have the database designed properly), but were just importing new tables which didn't exist before. If there are only a few fields, I will usually just manually make the change. If it is something that I can't do that with, I will delete the relationships and rename the old and import the new.

Make sure you have turned off Name AutoCorrect (which can corrupt things) before doing any of this.