Not sure where to put - catastrophic loss of data

[The_Vincester]

In my department we use several "linked" databases (from an MSSQL machine). Yesterday we had a catastrophic loss of data when another part of IT mistakenly wiped our machine and reinstalled a new OS. There were several things that failed, including us having an EXTERNAL backup of the .mdf files. Yeah, failures all around, we've learned our lesson, but are now in recovery mode.

Obviously the front ends weren't affected, but we loss all data, and to me - even worse - the structure behind the scenes (schemas). We at least want to get to a point where we can quickly get back up and running again. We lost 5 years of data, which is bad, but we have 6+ database that affect production.

OK, obviously this is difficult, but I've been looking at the DB. The linked connection somehow stores the structure of the connected DB. I get an ODBC time initially, but can then look at the design, and the structure IS there. I would like to make this structure local if nothing else. I've tried just copying and pasting, but it simply times out - I'm sure it's continually trying to connect.

Is there a command line option to do this? Do I have any chance in duplicating the structure? We do have some Excel backups of single tables, but there are a lot of supporting tables that each application depends on.

To quote Star Wars: Help me Obi Wan (and Access World Forums), you're my only hope!

 

[Galaxiom]

The linked connection somehow stores the structure of the connected DB.

I don't know how this would be.

It may be that the backend database was originally local and the Relationships diagram is still there. Some developers will replicate the relationships in the front end for reference too.

However beware that the SQL backend may have been changed over time without the relationships diagram in the FE being updated.

 

[gemma-the-husky]

I bet you could extract all the field and table names by processing the SQL of all your queries.

that might help

 for each qdef in currentdb.querydefs
     process qdef.sql
     (table.field syntax)
 next

maybe split the qdef.sql based on a space separator, and then look for array members including a full stop

don't open the queries to look at them, as that will screw them I think.
probably capture the sql in a table to preserve them before trying to do anything else.

[edit - you can probably interrogate the relationships with DAO as well. if that helps]

 

[Galaxiom]

Brilliant idea Dave. Should be able to recover the joins too.

But the Process sub won't be trivial.