Database Catalog

[sims.anderson2010]

I have been tasked with cataloging our databases at my place of employement and then moving them into a centralized location. There are over 300 databases and they are interlinked. If I move one, it breaks many. Is there an easy way to begin cataloging the interlinking of these databases. Can I build a databse that will help me to run a report on which databases link to which. Is there a template for this? Any suggestions would be helpful

Thanks in advance

Sims​

 

[Rx_]

Are you talking a catalogue of databases? I am reading this as a central database of the catalogues in multiple databases.

The latter might be a tough task in Access. Not that it couldn't do it, but there are probably better tools.

A long-time friend of mine does this kind of thing in SQL Server XML with custome XML object creation and distribution in SSRS.
When companies merge, they need cataloge, inventory, ordering, accounting and other related items integrated.
He was showing me some very interesing JIT manufacturing to delivery that allowed each group to create a professional catalogue of local inventory in XML - SSRS then could publish it to PDF, web services, or other DB replication. The inventory was in hundreds of databases as you describe. The XML layer (I am leaving out many other layers such as Biz Talk) keeps things in sync as other databases change sturctures.

 

[sims.anderson2010]

We have 200+ databases that interlink. I want to catalogue how they interlink so that when I begin to move them, I can make corrections in the databases that are gonna break becuase they can't find the database I moved.

 

[jdraw]

Where are these data bases located?

You might be able to do a Dir on the various drives and folders and get all files with >mdb or .accdb

Put that list into an Access table. Then using 1 database, open each of the other data bases in your list in turn -- recording tables, queries, forms, reports and modules and sufficient info to locate each.

You have the basis of an all encompassing data base dictionary.

How do you run a viable organization with

over 300 databases and they are interlinked.

and no apparent documentation?

 

[sims.anderson2010]

I work for a state government agency. This is an inherited mess. It is a problem of multiple people over many years overseeing with no oversight. I'm tasked with cleaning up the mess, Moving all into a central location (they are spread among many different folders), removing redundant databases (there will prob be many), etc. :banghead: :banghead: :banghead:. Our mighty tax dollars at work.

 

[jdraw]

I take it this is US state govt.
How do you know there are 300 databases?
What versions are involved?
What is your background?
What is the realistic expectation?

 

[sims.anderson2010]

Yes in the US

How do I Know?
Doing a *.mdb and *.accdb search with FileLocator Pro brings up 435 databases. Around 100 are in a "Trash" folder I set up for databases I know are not in use and won't affect any others. The rest are spread around our server.

What Versions Involved?
2003, 2007, 2010

My Background
Self Taught. Have programmed in VBA, PHP, Javascript, ASP. Have been working with Access about 1 year, heavily, sporadically before that.

Realistic Expectation
At some point want to have all databases moved into the central location. I understand that this will take time due to the interlinking (have to move one fix the others, move one fix the others, move one fix the others, etc).

I just want a way to catalogue the links so that I can print it out and keep up with what I'm moving and what it will break.

 

[jdraw]

Does File locator Pro allow you to save a File list y Drive and Folder etc?

Can you get a list of fullpath to each database?

I have done that on local machine for my own use. I used Dir commands to get Folders and *.mdb files, then put that list in a Table.
From within that database, I opened each other database in turn and recorded all Tables and Fields, Queries and their related SQL etc. and stored the results in a Table.

I can then search by Table name, field name or folder to do some analysis.
I realize it isn't exactly what you are trying to do , but in might be a step in the right direction.

Big question is how do you stop others from changing things while you're trying to put some discipline on the situation???

 

[sims.anderson2010]

Most people only input data. They do not mess with structure or anything that I think would throw a wrench in what I'm doing. I am currently the only "Access guy" at our facility, so I "think" I will be ok on this.

I understand that there is a lot of footwork here. Some of the things output from a database are with macros to a file location, so there is not even a linked table. I will have to physically open the macros and look through them recording locations.

Just trying to find a way to document the links (Macro and table, oh yeah, and pass-through queries) so that I have something I can look at and update as I update the databases and move them.

Just looking for hints. I understand that this is gargantuan.