• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Using/Getting MS Access Relation information in application

Hi, I'm planning on developing a system for building websites automatically based on the layout of an MS Access database thus creating forms for every table in the database and automatically creating drop-down boxes with items from another table on these forms whenever a field is linked to another field from another table in the database.

To do this I have to check the fields relations in the Access DB - is this possible from within Delphi?

Thanks in advance

Claus
0
145cfc
Asked:
145cfc
  • 3
  • 2
1 Solution
 
Pierre CorneliusCommented:
Simply call a query to MSysRelationships
 i.e. SELECT * FROM MSysRelationships

Here's a demo. Simply change the connection string accordingly. I used the Northwind.mdb sample database (copied to temp dir);

PAS File:
=================
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, Grids, DBGrids, DB, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.


DFM File:
=================
object Form1: TForm1
  Left = 192
  Top = 114
  Width = 696
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  WindowState = wsMaximized
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 0
    Top = 0
    Width = 688
    Height = 446
    Align = alClient
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
    Columns = <
      item
        Expanded = False
        FieldName = 'ccolumn'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'grbit'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'icolumn'
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'szColumn'
        Width = 150
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'szObject'
        Width = 150
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'szReferencedColumn'
        Width = 150
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'szReferencedObject'
        Width = 150
        Visible = True
      end
      item
        Expanded = False
        FieldName = 'szRelationship'
        Width = 150
        Visible = True
      end>
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\Northwind.m' +
      'db;Persist Security Info=False'
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 40
    Top = 56
  end
  object ADOQuery1: TADOQuery
    Active = True
    Connection = ADOConnection1
    CursorType = ctStatic
    Parameters = <>
    SQL.Strings = (
      'SELECT * '
      'FROM MSysRelationships')
    Left = 88
    Top = 56
  end
  object DataSource1: TDataSource
    DataSet = ADOQuery1
    Left = 128
    Top = 56
  end
end


Regards
Pierre
0
 
145cfcAuthor Commented:
Excellent - but then I face a new problem; apparently I need to set something regarding security in the DB 'cause when I try your code on Northwind demo DB everything works fine but whenever I try on any other Access DB I get an error stating missing read permission as the cause for not returning the records.

Any hints on that?
0
 
Pierre CorneliusCommented:
That's actually another question. How are you logging in, as an admin user. If you have sufficient rights, you could grant yourself access to the MSysRelationships system table.

e.g. GRANT SELECT ON MSysRelationships TO Username
0
 
145cfcAuthor Commented:
Excellent and quick reply again. Fits my needs 100%. Thank you.
0
 
Pierre CorneliusCommented:
Glad to help
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now