VBA function for returning Column names, type as a dictionary
I needed the column names of a sql server 2008 table.
So I wrote a function who would return a dictionary with the column names as the key and the type as the item.
‘Set Reference to Microsoft Scripting Runtime
‘Set Reference to Microsoft ActiveX Data Objects 2.7 Library
Function ColumnNames(ByVal sConnection As String, ByVal sTable As String) As Dictionary
Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim sSQL As String
Dim dColumnNames As Dictionary
sSQL = sSQL & “SELECT sc.name AS column_name, st.name AS datatype ”
sSQL = sSQL & “FROM sysobjects so ”
sSQL = sSQL & “JOIN syscolumns sc ON so.id = sc.id ”
sSQL = sSQL & “JOIN systypes st ON sc.xtype = st.xtype ”
sSQL = sSQL & “WHERE so.xtype = ‘U’ AND st.name <> ’sysname’ AND so.name = ‘” & sTable & “‘”
Set dColumnNames = New Dictionary
Set cConnection = New ADODB.Connection
cConnection.Open sConnection
Set rsRecordset = New ADODB.Recordset
Set rsRecordset.ActiveConnection = cConnection
rsRecordset.Open sSQL
Do While Not rsRecordset.EOF And Not rsRecordset.BOF
dColumnNames.Add rsRecordset.Fields(0).Value, rsRecordset.Fields(1).Value
rsRecordset.MoveNext
Loop
Set rsRecordset = Nothing
cConnection.Close
Set cConnection = Nothing
Set ColumnNames = dColumnNames
End Function
March 1st, 2010 at 10:26 am
I use a more primitive technique…. Just Say
SELECT*
FROM TABLE
WHERE “SOME CONDITION THAT WOULD RETURN ZERO RECORDS” Eg. Name = “ZZZZZZ”
Then read the column headings in an Array and use as required
March 2nd, 2010 at 4:36 pm
I need the type of the column to build a insert query.
That was the reason for the more complex routine.
June 25th, 2010 at 2:15 pm
PillSpot.org. Canadian Health&Care.Special Internet Prices(up to 40% off average US price).No prescription online pharmacy.Pillspot.org. Herbal-supplements@buy.online” rel=”nofollow”>.< /a …
Categories: Stop SmokingAntidepressants.Skin Care.Mental Health/Epilepsy.Antiviral.Anxiety/Sleep Aid.Mens Health.Antidiabetic.Pain Relief.General Health.Vitamins/Herbal Supplements.Eye Care.Stomach.Blood Pressure/Heart.Anti-allergic/Asthma.Cholest…
July 21st, 2010 at 4:06 pm
Buy:Cialis Professional.Cialis Super Active+.Levitra.Super Active ED Pack.Cialis.Maxaman.Viagra Soft Tabs.Soma.Tramadol.Propecia.Viagra Super Active+.Zithromax.Viagra Professional.Viagra.Viagra Super Force.Cialis Soft Tabs.VPXL….
August 29th, 2010 at 9:19 am
uk MacBook Apple/ http://AWESOMEBABYCLOTHES.INFO/tag/r\x3dh : uk MacBook Apple/…
uk MacBook Apple/…
August 29th, 2010 at 10:40 am
r\x3dh http://AWESOMEBABYCLOTHES.INFO/tag/r\x3dh : uk MacBook Apple/…
r\x3dh…
August 29th, 2010 at 12:12 pm
pilot http://acontinentalv5r0agu.04FORDPARTS.US/tag/King Air Emergency Landing pilot Pilot/ : pilot…
pilot…
August 29th, 2010 at 12:33 pm
Suzuki http://kdaisywcktao.BEDROOMPROPERTY.INFO/tag/Suzuki+shed+Motorcycle/ : Suzuki…
Motorcycle…
August 29th, 2010 at 12:57 pm
and http://lambicoxcdo.BESTPARTSPLUS.INFO/tag/Reviews+and+Maker/ : Reviews…
Maker…
August 30th, 2010 at 12:04 am
Theater http://winductionpqtus.APTAUTOPARTS.INFO/tag/Theater+Receivers+speakers/ : Theater…
speakers…
August 30th, 2010 at 5:46 am
Boxes http://ainstallingyrcyp.01DODGEPARTS.US/tag/Boxes+Storage+storage/ : Boxes…
storage…