Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VB.net/VSTO - Get Table relationships in Access database

Posted on 2013-12-09
4
706 Views
Last Modified: 2013-12-10
Hi

I am using the following code to open an Access database from my Excel
Add-in project. I want to find the table relationships using code similar to the
VBA code that I found between the lines in the middle.
How would I do this?


    Sub Import_CSV_to_Access_table()
        Try

            Dim arrSplit As Object = Split(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text, "=")
            Dim Access_File_Path As String = Mid(arrSplit(2), 1, Len(arrSplit(2)) - 1)

            Dim appAccess As New Microsoft.Office.Interop.Access.Application
            appAccess.OpenCurrentDatabase(Access_File_Path)
            appAccess.Visible = True

            appAccess.DoCmd.SetWarnings(False)
            Dim oAccessDB As Microsoft.Office.Interop.Access.curr


            '----------------------------------------------------------------------
            For Each rel In db.Relations
                Debug.Print(rel.Name, rel.Table, rel.ForeignTable, rel.Attributes)
                For Each fld In rel.Fields
                    Debug.Print, fld.Name, fld.ForeignName
                Next
            Next
            '----------------------------------------------------------------------

            appAccess.DoCmd.SetWarnings(True)

            appAccess.CloseCurrentDatabase()
            appAccess.Quit()
            appAccess = Nothing

        Catch ex As Exception
            MsgBox(ex.Message & " poph11")
        End Try
    End Sub
0
Comment
Question by:murbro
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 39706360
I gave you an answer in your other post.

Please don't post multiple versions of the same question or if you do, at least tell us and paste a link.
0
 

Author Comment

by:murbro
ID: 39707967
Hi

But this is via VB.net. Please could you post the rest of the VB.net code required to connect to the database and run your query SELECT MSysRelationships.*
FROM MSysRelationships;
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39708581
This is an Access forum.  If you need code for VB.net, you would be better off posting your request there.  The query I suggested is like any other query you would run from vb.net against Jet/ACE tables.  The only difference is that it queries a system table.  If you open the database and don't see the MSys tables, you can show them by right-clicking on the header of the navigation pane and choosing "Navigation Options".  Then click the box that says "Show System Objects".  The tables don't need to be visible for the query to work.
0
 

Author Closing Comment

by:murbro
ID: 39708639
Thank you for the last comment. It helped a lot
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question