Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-12-09
4
Medium Priority
?
742 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 39

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 39

Accepted Solution

by:
PatHartman earned 2000 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

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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