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-19T16:41:45+00:00

    It would help if you could describe just what the database is modelling, what each of the five tables represents and how they are 'connected'.  Five single column tables is an unusual structure.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-19T16:36:11+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.

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2012-02-19T11:47:34+00:00

    In a many-to-many relationship, you need a table with (at least) two fields. Let's say you want to store people and their hobbies.

    The Persons table lists the individual persons:

      PersonID (AutoNumber, Primary Key)

      LastName (Text)

      FirstName (Text)

      etc.

    The Hobbies table lists all possible hobbies:

      HobbyID (AutoNumber, Primary Key)

      Hobby (Text)

    The PersonsHobbies table lists all existing combinations of a person and a hobby.

      PersonID (Number)

      HobbyID (Number)

    If a person has 3 hobbies, there will be three records for that person in the PersonsHobbies table, with the same PersonID and different HobbyIDs.

    If there are 12 persons with a specific hobby, there will be 12 records with the same HobbyID and different PersonIDs.

    See http://www.eileenslounge.com/viewtopic.php?f=29&t=477#p3581 for a simple example.

    Was this answer helpful?

    0 comments