how to export odbc information from access 2010

Anonymous
2012-02-17T17:33:20+00:00

Is there a way to export linked information in access 2010.  For instance the odbc, server& database names associated with each table?  I found the documenter but that is only for one table and is a mess in excel.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2012-02-20T05:56:32+00:00

Please post the SQL view of the recordsource of the form and the rowsources of the four combo boxes.

The relationship DOESN'T ACTUALLY MATTER at this point. You do need the relationships, but they won't affect how the form works!

My idea was that you would have tblTablenames with fields (I don't remember and can't scroll back to see them) for AccessDatabaseID, ODBCID, SQLDatabaseID and ServerID, as the Recordsource for your form; the form would not have ANY other tables involved at all. It would have a textbox for the table name and four combo boxes, based on the appropriate tables (AccessDatabases, ODBC, SQLDatabases, Servers) and bound to those four ID fields. You would type in the tablename, and then select the appropriate choices from the four combo boxes. Then you'ld move to the next tablename, and so on.

I haven't any trace of a clue what you mean by "it doesn't work... it shows four records at the bottom".

Again: If you're basing the Form on this multitable query, DON'T. That's not how relationships work, that's not how forms work!!!!

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2012-02-20T04:33:26+00:00

Add fields AccessdbID, ODBCID, SQLdbID and ServerID to tblTableName.

You can then use a Form with combo boxes bound to those fields, based on your database, odbc and SQLdb and Server tables.

It's not quite normalized but a) I think it will meet your current needs and it will be quick and b) it can be used to populate normalized tables if you need to move there.

Was this answer helpful?

0 comments

70 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. ScottGem 68,840 Reputation points Volunteer Moderator
    2012-02-21T12:58:59+00:00

    I'm sorry I'm late to getting back to this. I've gone through all the posts and a couple of things strike me.

    First, I agree with Ken, you should have ONE table that lists each all the info about each database table. The other tables are just lookup tables to standardize your input.

    Second, an ODBC table is defined by the following:

    Data Set Name (DSN) and table name

    Each DSN is further defined by the Database it is connecting to. How this is defined depends on the database. It could be a server name and database name or it could just a database.

    So the way I see it is main table should be structured like this:

    tblTables

    TableID (PK Autonumber)

    DatabaseConnection (This would be the name of the Access file for a native Access table and the name of the ODBC DSN for an ODBC table)

    DatabaseLocation (This would be Location of the Access file or Server and Database name of the source of the DSN.

    I think that is all you really need. Then have lookup tables for the ODBC DSNs and Access native files.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-21T16:46:52+00:00

    You were all so helpful.  Thank you again.   I appreciate it more than I can ever express.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-21T18:07:19+00:00

    So the way I see it is main table should be structured like this:

    tblTables

    TableID (PK Autonumber)

    DatabaseConnection (This would be the name of the Access file for a native Access table and the name of the ODBC DSN for an ODBC table)

    DatabaseLocation (This would be Location of the Access file or Server and Database name of the source of the DSN.

    All of which could be parsed out from the linked tabledef's Connect property I think, so by generating these with the little application I posted on SkyDrive, modifying the CREATE TABLE statement slightly to add the additional columns to the table, and then executing a few update queries using a suitable parsing function one would be home and dry with no manual data input whatsoever.

    Was this answer helpful?

    0 comments