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-19T18:50:45+00:00

    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.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-19T18:41:24+00:00

    I see you have also posted this question in another thread.  This helps neither us nor you.  Please do not start a new thread unless you are introducing a new question.

    As far as I can see this is a straightforward linear hierarchy, so all you need is a set of one-to-many relationships from the Servers table down to the Tables table.  Apart from the Servers table each table needs, in addition to the column you have at present, a foreign key column referencing the level above.  To present the data you could for instance then join the five tables in a query and produce a report, grouping it firstly by Server, then by ODBC and so on, with each level apart from the bottommost, i.e. Tables, shown in a group header and the tables in the detail section.  Each detail section would then show the tables from one Access database.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-19T17:07:01+00:00

    Ok let me give you an example and maybe i'm going at this all wrong which is why its not working. 

    when you put your mouse over a table name it shows what server it uses, what the odbc name is,  what sql database it uses, what access database it uses and what the table name is.  that is the information we are storing.  So when we connect those databases to the new servers with new access databases everything runs smoothly.  We are converting access from 2003 to 2010.  Which i am doing beautifully(the only thing it seems) and sql is going from 2003 to 2008.

    The thought is when we get ready to redirect an access database we can have right in front of us a list of all tables in that database where they were pointed so we know we don't miss anything.  Its crucial the conversion go smoothly with no issues so no one is down.

    I hope this explains it more.  I just need those five items in a database...we have four servers 2 old 2 new..mulltiple odbc of course, multiple sql databases although i'm not sure how many...about 40 access databases and who knows how many tables.

    I would give anything for some help.  I keep having to scrap mine cause they just won't work. Seriously, my kids eating depends on me getting this done and making a good presentation and i have never used access 2010..or office 2010 for that matter ..add that to the fact that i'm nervous wreck trying to make this work.  It literally is only five fields total and yes i know its odd ...again probably why i'm not handling it right. 

    Please help it would be so appreciated.

    Was this answer helpful?

    0 comments