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. Anonymous
    2012-02-20T18:11:18+00:00

    artstlvr   @

    sbcglobal   dot

    net

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-02-20T20:12:08+00:00

    As you seem to be making heavy weather of creating a set of related tables, if what you need is a way to see the source to which a table was linked when you come to configure your new system, why not just create a single table with columns for the database name, table name and its Connect string.  That will give you the information you need, and it would need no data entry at all as the rows can easily be inserted for each Access database with a little bit of fairly simple code.  In fact I've quickly put together a little file containing .accdb and .mdb which does this, as TablelinksInfo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

    The file uses the following module to create an d populate the table:

    Option Compare Database

    Option Explicit

    Public Function FillTablelinks(strDatabase As String)

        On Error GoTo Err_Handler

        Dim dbsExtCurrent As DAO.Database

        Dim dbsExt As DAO.Database

        Dim tdf As DAO.TableDef

        Dim strSQL As String

        Set dbsExtCurrent = CurrentDb

        Set dbsExt = OpenDatabase(strDatabase)

        For Each tdf In dbsExt.TableDefs

            If tdf.Connect <> "" Then

                strSQL = _

                "INSERT INTO TableLinks(DatabaseName,TableName,ConnectString) " & _

                "VALUES(""" & dbsExt.Name & """,""" & tdf.Name & """,""" & tdf.Connect & """)"

                dbsExtCurrent.Execute strSQL, dbFailOnError

            End If

        Next tdf

        MsgBox "Operation Completed.", vbInformation, "Confirmation"

    Exit_Here:

        Exit Function

    Err_Handler:

        MsgBox Err.Description, vbExclamation, "Error"

        Resume Exit_Here

    End Function

    Public Function CreateTable() As Boolean

        Const TABLEEXISTS = 3010

        Dim tdf As DAO.TableDef

        Dim strSQL As String

        strSQL = _

            "CREATE TABLE TableLinks (" & _

            "DatabaseName TEXT(255)," & _

            "TableName TEXT(255)," & _

            "ConnectString TEXT(255)," & _

            "CONSTRAINT PrimaryKey PRIMARY KEY (DatabaseName, TableName))"

        On Error Resume Next

        CurrentDb.Execute strSQL, dbFailOnError

        Select Case Err.Number

            Case 0

            ' no error

            CreateTable = True

            Application.RefreshDatabaseWindow

            Case TABLEEXISTS

            ' anticipated error

            CreateTable = True

            Case Else

            ' unknown error

            CreateTable = False

            MsgBox Err.Description, vbExclamation, "Error"

        End Select

    End Function

    All you need to do is select the Access file by clicking the top button on the opening form to open the common dialogue, then click the Insert Rows etc. button.  The other buttons open a form and report to view the inserted data, the form also including a combo box to find a table by name.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-02-20T20:18:09+00:00

    I tried to look at it but none of the buttons work.

    Was this answer helpful?

    0 comments