help with probable no brainer :(

[rmanchu]

using access 2003 VBA.

how to open an adodb recordset with no records when only the table name
is given?

table in mssql server 2000

essentially, i'm trying to get the name of the PK field which is the
first field in the table (this is a given). and i'm doing this for a
whole set of tables so i want to write a general procedure to do this
from just the table name.

thanx
riyaz

 

[Dirk Goldgar]

using access 2003 VBA.

how to open an adodb recordset with no records when only the table
name is given?

table in mssql server 2000

essentially, i'm trying to get the name of the PK field which is the
first field in the table (this is a given). and i'm doing this for a
whole set of tables so i want to write a general procedure to do this
from just the table name.

thanx
riyaz

You could open a recordset on a SQL SELECT statement that applies a
criterion that always evaluates to False. For example,

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnect As String
Dim strTableName As String

strTableName = <your table name>
strConnect = <your connect string>

Set conn = New ADODB.Connection
conn.Open strConnect

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM " & strTableName & " WHERE 1=0", conn

Debug.Print rs.Fields(0).Name

rs.Close
conn.Close

Or you could use ADOX Catalog, Table, and Columns objects, but I'm not
as familar with ADOX.

 

[Douglas J Steele]

For what you're trying to do, it probably makes more sense to use ADOX:

Function FirstColumn(TableName As String) As String

Dim cnnCurr As ADODB.Connection
Dim catCurr As ADOX.Catalog

Set cnnCurr = New ADODB.Connection
cnnCurr.Open <your connection string here>

Set carCurr = New ADOX.Catalog
Set catCurr.ActiveConnection = cnnCurr

FirstColumn = catCurr.Tables(TableName).Columns(0).Name

End Function

Of course, I'd be reluctant to assume that the first column is always the
primary key. How do you handle resolution tables for many-to-many
relationships? The correct way is to loop through the Indexes collection
until you find the Primary Key Index, then report what columns are in it:

Function PrimaryKey(TableName As String) As String

Dim cnnCurr As ADODB.Connection
Dim catCurr As ADOX.Catalog
Dim idxCurr As ADOX.Index
Dim colCurr As ADOX.Column
Dim strOutput As String

Set cnnCurr = New ADODB.Connection

cnnCurr.Open <your connection string here>

Set catCurr = New ADOX.Catalog
Set catCurr.ActiveConnection = cnnCurr

For Each idxCurr In catCurr.Tables(TableName).Indexes
If idxCurr.PrimaryKey = True Then
strOutput = vbNullString
For Each colCurr In idxCurr.Columns
strOutput = strOutput & colCurr.Name & ";"
Next colCurr
If Len(strOutput) > 0 Then
strOutput = Left(strOutput, Len(strOutput) - 1)
End If
PrimaryKey = strOutput
Exit For
End If
Next idxCurr

End Function