how to export odbc information from access 2010

If you would please explain what you're doing, and in what way it won't work, someone might be able to suggest a fix.

I can visualize (not write it offhand to a message, but I'm sure it can be done) some VBA code which would search through directories on a network (not CDs or DVDs sitting on someone's desk of course) looking for .mdb, .mde, .accdb, .accde files; opening each of them (with security and password protection being a big hassle!!), looping through the Documents collection to find all the tables, and looking at each table's Connection and constructing such a table. It would be a LOT of work because there's so many ways that a database can be set up.

I wonder if the first step might be to identify the business-critical applications that get used every day and manually work on them (using the Database Documenter perhaps); then the ones used weekly, then end of month, then those databases that only get opened on the 27th of December for that critical end-of-year report. Working with the people who use the databases is going to be just as important as working with the databases themselves.

And... I know it's heretical and evil to say it... but sometimes an application will break and it really doesn't make any big difference to anyone!

I sure hope you can stick with me a little longer.  I'm not kidding i'd pay to find an example...but i can't...ok...i added all five tables...one to many for first two the rest say intermediate?  The query now only shows NOTHING....I added data to all five tables for testing purposes.  I think its because i made them both primary keys but i'm not sure how to make them foreign.

As regards the data type mismatch, the primary and foreign key columns must be of the same data type, so if the Servers table is structured like this for instance:

Servers

....ServerID (autonumber)

....ServerName (text)

the ODBC table would be structured like this:

ODBC

....ODBCID (autonumber)

....ODBCString (text)

....ServerID (number - long integer)

An autonumber is simply long integer number data type whose value is automatically assigned when a row is inserted into the table.  This pattern would be repeated down the line.

A query would join Servers to ODBC on the ServerID columns, and ODBC to the SQL table on the ODBCID columns and so on down the line.

For data entry don't try and enter everything in one form bound to such a query.  The query is only for reporting.  One approach would be to use a set of forms, each bound to one table and in addition to a text box bound to the text column have a combo box bound to the foreign key column, so in a form bund to the ODBC table you'd have a text box bound to the ODBCString column and a combo box bound to the ServerID column, set up as follows:

ControlSource:    ServerID

RowSource:     SELECT ServerID, ServerName FROM Servers ORDER BY ServerName;

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column.

You would then first enter all the servers in the servers form, then all the ODBC values in the ODBC form, selecting the server for each from the combo box, and so on down to tables.

The alternative to this top-down approach to data entry would be a bottom-up one, where you enter the tables first.  As no values would be listed in the combo box for the next level up, e.g. Access databases in the case of the tables form, you'd use the combo box's NotInList event procedure to add the database name, which would then open a form to do this.  You'd then proceed upwards through the hierarchy of forms in the same way.  You'll find an example of how to use the NotInList event procedure in this way as NotInList.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

If you wanted to do it all in one form, then you would need a form, in single form view, based on the Servers table and within this four correlated subforms, each in continuous forms view, based on the other tables.  With this set-up as you move from row to row in each subform the other subforms would be requeried to show the matching rows.  New rows could be entered in both the parent form and each subform in this way.  Subforms can be correlated by having hidden text box controls in the parent form, each referencing the primary key column of one subform and being referenced as the LinkMasterFields property of the subform one level down in the hierarchy.  Performance would, I suspect, be sluggish with so many correlations, so while this is possible I would not recommend it.  The first approach above, i.e. a top-down approach through each level of the hierarchy is the simplest solution.