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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-02-20T03:15:14+00:00

    I had saved a copy when i only had five tables

    tblaccessname

    accessdbid primary key

    accessname text

    SqlDatabaseid1 Foreign Key

    tblodbc

    odbcid primary key

    odbcname text

    serverid foreign key

    tblservername

    serverid autonum primary key

    servername text

    tblsqldb

    sqldatabaseid auto primary

    sqldbname text

    odbcid foreign key

    tbltablename

    tableid auto primary key

    tablename text

    accessdbid1 foreign key

    my query's never worked right to put in a form and have it all work together...this was hours and hours ago.

    I looked at that example he mentione and they have no queires just the junction table...it only has two tables and one junction

    I can't even fathom how to do that certainly not in the next few hours

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-20T02:51:48+00:00

    Your still here OMG Thank God....I am by the way..and YYYYYYUUUUUUUUUUMMMMMMMMMMMM....Indian Foood is my favorite. 

    Ok I created all the junctions...and i started with table and access name...they work but i can't enter new access names..ughh...i hate to start over and i'm at a loss as to what to do..

    So if we do like you are saying I know a couple of the Sql Databases I have put those in and I have the SQL servers Listed.  Do I get rid of the junctions?  Do i just re link those primary keys.  Jeez I wish i could just send this to you to look at as i hate not giving you the information you need.  Thank you for sticking with me.  Its getting really late here and I feel like i've gotten no where and i have this darn deadline.

    I have same tables only i added the tables for the junctions ..so there is four of them all referencing primary keys for two tables.  ie: accesstablejunction references tableid and accessdbid

    I think i'm in overkill on all this and i need this done .

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-20T02:33:02+00:00

    Wouldn't it be Server - 1:m - SQL Database - 1:m - ODBC - m:m - Access database - 1:m - Table?

    I agree with Ken that at this point your best bet may involve a simple one-table form for Access databases, with a subform for Tables. You could just manually enter the few Servers directly into the table; I have no way to know if you're dealing with four or forty SQL databases but you could enter them as well, either in the table or in a simple one-table form.

    That would let you just put combo boxes on the Access Database form for the ODBC and Database fields. Nested subforms may well be overkill.

    If you could post your current tables, with primary and foreign keys, it would help. I'm cooking supper (lentil dhal, cardamom basmati rice and spicy mustard greens <g>) but I'll be checking back over the evening.

    Was this answer helpful?

    0 comments