Solved

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

Posted on 2013-12-09
4
701 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 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