how to export odbc information from access 2010

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.

I was asking two different questions and thought I needed a new thread sorry for that misunderstanding on my part about the forum.

I understand what you are saying but how would i then add new records.  I have a form and managed to select but i need to add then save and my thoughts are just all jumbled on this.

Also my query is only returning one row.

You don't know of any examples anywhere do you.  I sure can't find one. I'd pay money at this point as i'm running out of time but i just can't find anything.