Locating index key in MS SQL using ADO
-
Please help as I am converting DAO to ADO VB codes locating primary key from MS SQL database, but don't know how to convert the following function to ADO...
The DAO codes as below,
Private Function KeyFields()As IndexFields
Dim intIndex As Integer
Set KeyFields = Nothing
With mdbDatabase.TableDefs(strTable)
For intIndex = 0 to .Indexes.count -1
if .Indexes(intInded).Primary Then
Set KeyFields = .Indexes(intIndex).Fields
Exit For
End If
Next intIndex
End With
End Function
-
Ryan - I don't have the time to check the exact syntax but your editor should be able to help you along...here's something to get you started...
Private Function KeyFields(strTable As String) As String
dim strSQL As String
dim rstPK As New ADODB.Recordset
strSQL = "SELECT name FROM sysobjects " & _
"WHERE xtype = 'PK' AND parent_obj = " & _
"(SELECT id FROM sysobjects WHERE name = ' & strTable & "')"
rstPK.Open strSQL, ConnectionStringVariable etc...
If Not rstPK.EOF Then
KeyFields = rstPK!name
End If
rstPK.Close
set rstPK = Nothing
End Function
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks so much sushila. I works fine as it extracts the primary key from the SQL tables.
However, after I review the overall objective of my VB project, I found out that the previous DAO code use to find the primary key of that table and return the table column object to another function called SelectQuery. And the SelectQuery function calculate the column counts (col.counts) and do something i.e.
Private Function SelectQuery () As String
dim cols as ADOX.Columns
For intIndex = 0 to cols.count -1
if intIndex = 0 then ...
Could you please guide me how to extract primary key's and as column to be passed to another function?
-
Ryan - Pl. look under the ACCESS forum - this same topic is being addressed there -
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=184996
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply