Solved

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

Posted on 2013-12-09
4
730 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:Murray Brown
[X]
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
  • 2
  • 2
4 Comments
 
LVL 38

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:Murray Brown
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 38

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:Murray Brown
ID: 39708639
Thank you for the last comment. It helped a lot
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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