How do I retrieve Column Names from SQL Server using ADO

[SBendBuckeye]

How can I pull the Column Names out of SQL Server so I can incorporate them into my Excel VBA code when using ADO? I am not having any problems populating a recordset with the data but I need the column names also.

Thanks in advance for any ideas, suggestions, tips, tricks, working code examples ;-), links, tutorials or whatever!


Have a great day!

j2consulting@yahoo.com

 

[DrJavaJoe]

You would want to use ADOX if you do a search here you should find lots of examples or just make a reference to Microsoft ADO Ext 2.7 for DDL and Security and start messing with it.

"Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'."

 

[zemp]

Take a look at the ADO .OpenSchema method. Good examples in the help files and see Thread709-501345.

zemp

 

[DrJavaJoe]

Here's an example:
Dim Catalog As New ADOX.Catalog
Set Catalog.ActiveConnection = adoConn

Dim Table As ADOX.Table, Column As ADOX.Column
For Each Table In Catalog.Tables
For Each Column In Table.Columns
Debug.Print Table.Name & ", " & Column.Name
Next
Next




"Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'."

 

[NetGiant]

Uh. If you're getting a recordset then you already have the column names. Check the YourRecordset.Fields(0).Name property. Try this snippet.

Dim objField as ADODB.Field

For Each objField In YourRecordset.Fields
With objField
Debug.Print .Name
Debug.Print .Type
Debug.Print .DefinedSize
End With
Next objField

Sorry if I miss something as I wrote this right in the response box and didn't test it in VB.


Net_Giant

What fun is a technology if you can't crash the OS?

 

[SBendBuckeye]

Thanks all. You are correct, NetGiant, had a brain cramp there. Learned some new things so thanks again!

Have a great day!

j2consulting@yahoo.com