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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2012-02-19T22:40:23+00:00

    Thank YOU... and sorry I felt that I had to "yell" at you to get here.

    There are a couple of problems that I see. For one thing, the foreign key of a relationship must always be in the "many" side table, linked to the primary key of the "one" side table. So since you could have many different ODBC links to a particular SQL database, it's not correct to have the ODBCID2 field in tblSQLDB. If you do that, then you would be able to set up one, and only one, such link. You need many!

    Since - in the real world - one ODBC link must point to one and only one Database, you need to put the database ID in the ODBC table, not the other way around.

    I'm also not sure that it's appropriate to have a one (SQL/Server database) to many (Access database) relationship. It may be appropriate in your shop, if in practice each Access database is linked to only one SQL instance; but the technology allows for the possibility that you could have one Access frontend linked to two or more SQL backends (and of course you already model the fact that each SQL backend could be linked to multiple Access frontends). Similarly, you could have several different ODBC connections in one Access database, as well as several different Access databases for a given ODBC connection. Do you? 

    Don't change the structure unless you need to - no point in spending a lot of time solving a nonexistant problem! - but I did want to bring up the concern.

    Now a bigger issue. You seem to be making a very common mistake, creating a multitable query (tblODBC joined to tblSQLdb) and using that joined query as the Recordsource for a form. You don't need to do this, and it can and will cause trouble.

    Instead, if - as it appears - you have a linear heirarchy, use Forms and Subforms: a Form for the "one" side table of a relationship, with a Subform for the "many". I think you may be able to use a structure like:

    frmSQLDatabases

       <recordsource tblSQLDB>

       <you'ld just have a combo box on this form to select the SQL Server for this database>

    sbfrmODBC

      <a Subform showing all the ODBC connections to the currently selected tblSQLDB record>

    sbfrmAccessDB

      <a sub-subform showing all of the Access databases that use that ODBC connection>

    sbfrmTables

     <a sub-sub-subform showing all of the tables in that Access database>

    You should certainly consider having a Text field in the table of Tables in which you could store the CONNECT string for that table, available from the Tabledef collection's properties in VBA. It contains the information needed to recover the name of the ODBC connection, the server, the parent table (which might be different than the name of the linked table), etc.

    Good luck and keep posting back... and do try to talk openly to your coworkers and boss about realistic understanding on both sides about what is needed, what is practical, and how much time it may take!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-19T22:19:01+00:00

    OK I solved the problem of the SQL query.  If i make all five like this great.  Let me ask you this how do i make a form with all the data so you can select a table and ODBC then add the rest you see what i'm saying we have four sql servers....then some ODBC connections i am not sure as of yet how many.  Then the sql databases and I do not know how many of those.  Then fourty databases and lots of tables. 

    Like i said i've never used 2010.  I've looked at the Microsoft examples but this is just such a different database idea. 

    I apologize I don't know exact amount of each but i've only been there five days.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-19T21:57:50+00:00

    Table Structures

    tblserverName

    ServerID AutoNumber Primary Key

    ServerName Text

    tblODBC

    ODBCID AutoNumber Primary Key

    ODBCName Text

    ServerID Foreign Key

    tblSQLDB

    SQLDatabaseID AutoNumber Primary Key

    SQLDBName Text

    ODBCID2 Number Foreign Key

    tblAccessName

    AccessDBID AutoNumber Primary Key

    AccessName Text

    SQLDatabaseID1 Number Foreign Key

    tblTableName

    TableID AutoNumber Primary Key

    TableName Text

    AccessDB1 Number Foreign Key

    Relationships set up one to many from serverid to serverid...odbcid to odbcid2...sqldatabaseid to sqldatabaseid1....accessdbid to accessdbid1

    qry odbc

    tblodbc and tblserverName connected with serverid

    qrySQLDB

    tblodbc and tblsqlDB connected with odbcid to odbcid2

    form

    odbc

    has record source qry odbc now this one seems to be working good i have it saving and a command button to start new record.

    when i tried to add SQL form is when it messed up and the odbc field didn't show up.  I can try again.  I stepped away walked around outside because you were right i was getting to emotional.   I have very few hours left. 

    Thank you again from my whole heart.

    Was this answer helpful?

    0 comments