Professional Web Applications Themes

DMO + Foreign Keys - Microsoft SQL / MS SQL Server

This should get you started... Dim oServer As New SQLDMO.SQLServer Dim oDatabase As SQLDMO.Database Dim oTable As SQLDMO.Table Dim i As Integer Dim strKeys As String With oServer .LoginSecure = True .Connect ".\dev" 'quickly create test tables Set oDatabase = .Databases("northwind") oDatabase.ExecuteImmediate "create table t1(i int primary key, j int); create table t2(i int primary key, j int)" Set oTable = oDatabase.Tables("t2") For i = 1 To oTable.Keys.Count If oTable.Keys(i).Type = SQLDMOKey_Foreign Then strKeys = strKeys & oTable.Keys(i).Name & ", " Next If InStr(1, "fk_t2_t1_i", strKeys) > 0 Then Dim oKey As New SQLDMO.Key With oKey .Type = SQLDMOKey_Foreign .KeyColumns.Add ...

  1. #1

    Default Re: DMO + Foreign Keys

    This should get you started...

    Dim oServer As New SQLDMO.SQLServer
    Dim oDatabase As SQLDMO.Database
    Dim oTable As SQLDMO.Table
    Dim i As Integer
    Dim strKeys As String

    With oServer
    .LoginSecure = True
    .Connect ".\dev"

    'quickly create test tables
    Set oDatabase = .Databases("northwind")
    oDatabase.ExecuteImmediate "create table t1(i int primary key, j int);
    create table t2(i int primary key, j int)"

    Set oTable = oDatabase.Tables("t2")
    For i = 1 To oTable.Keys.Count
    If oTable.Keys(i).Type = SQLDMOKey_Foreign Then strKeys = strKeys &
    oTable.Keys(i).Name & ", "
    Next

    If InStr(1, "fk_t2_t1_i", strKeys) > 0 Then
    Dim oKey As New SQLDMO.Key
    With oKey
    .Type = SQLDMOKey_Foreign
    .KeyColumns.Add ("j")
    .ReferencedTable = "t1"
    .ReferencedColumns.Add ("i")
    .Name = "fk_t2_t1_i"
    End With
    oTable.Keys.Add oKey
    Debug.Print "fk_t2_t1_i created"
    Else
    Debug.Print "fk_t2_t1_i already exists"
    End If

    'clean up
    oDatabase.ExecuteImmediate "drop table t2,t1"

    .DisConnect
    End With

    Set oKey = Nothing
    Set oTable = Nothing
    Set oDatabase = Nothing
    Set oServer = Nothing


    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "Steven Campbell" <stevenrcampbellyahoo.ca> wrote in message
    news:e124b691.0307071127.69d79eaposting.google.co m...
    > Is thier anyway to create an If Exists statement with Foreign Keys,
    > meaning if it already exists I don't want to attempt to add it, but if
    > it does not exist i do want to add it.

    oj Guest

  2. #2

    Default Re: DMO + Foreign Keys

    oops... this is the correct check...

    If InStr(1, strKeys, "fk_t2_t1_i") = 0 Then
    ....

    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "oj" <nospam_ojngohome.com> wrote in message
    news:%23Z6%23pLRRDHA.2148TK2MSFTNGP11.phx.gbl...
    > This should get you started...
    >
    > Dim oServer As New SQLDMO.SQLServer
    > Dim oDatabase As SQLDMO.Database
    > Dim oTable As SQLDMO.Table
    > Dim i As Integer
    > Dim strKeys As String
    >
    > With oServer
    > .LoginSecure = True
    > .Connect ".\dev"
    >
    > 'quickly create test tables
    > Set oDatabase = .Databases("northwind")
    > oDatabase.ExecuteImmediate "create table t1(i int primary key, j int);
    > create table t2(i int primary key, j int)"
    >
    > Set oTable = oDatabase.Tables("t2")
    > For i = 1 To oTable.Keys.Count
    > If oTable.Keys(i).Type = SQLDMOKey_Foreign Then strKeys = strKeys
    &
    > oTable.Keys(i).Name & ", "
    > Next
    >
    > If InStr(1, "fk_t2_t1_i", strKeys) > 0 Then
    > Dim oKey As New SQLDMO.Key
    > With oKey
    > .Type = SQLDMOKey_Foreign
    > .KeyColumns.Add ("j")
    > .ReferencedTable = "t1"
    > .ReferencedColumns.Add ("i")
    > .Name = "fk_t2_t1_i"
    > End With
    > oTable.Keys.Add oKey
    > Debug.Print "fk_t2_t1_i created"
    > Else
    > Debug.Print "fk_t2_t1_i already exists"
    > End If
    >
    > 'clean up
    > oDatabase.ExecuteImmediate "drop table t2,t1"
    >
    > .DisConnect
    > End With
    >
    > Set oKey = Nothing
    > Set oTable = Nothing
    > Set oDatabase = Nothing
    > Set oServer = Nothing
    >
    >
    > --
    > -oj
    > RAC v2.2 & QALite!
    > [url]http://www.rac4sql.net[/url]
    >
    >
    >
    > "Steven Campbell" <stevenrcampbellyahoo.ca> wrote in message
    > news:e124b691.0307071127.69d79eaposting.google.co m...
    > > Is thier anyway to create an If Exists statement with Foreign Keys,
    > > meaning if it already exists I don't want to attempt to add it, but if
    > > it does not exist i do want to add it.
    >
    >

    oj Guest

Similar Threads

  1. Foreign keys tut
    By frizzle in forum MySQL
    Replies: 2
    Last Post: July 18th, 05:45 PM
  2. Sql Import with Foreign Keys
    By ryanmhuc@yahoo.com in forum MySQL
    Replies: 0
    Last Post: May 12th, 08:49 PM
  3. foreign keys
    By Sybrand Bakker in forum Microsoft Access
    Replies: 2
    Last Post: July 25th, 10:04 PM
  4. foreign keys - updated
    By Josh in forum Microsoft Access
    Replies: 2
    Last Post: July 24th, 04:01 PM
  5. SQL DMO + Foreign Keys
    By Steven Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 09:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139