PDA

View Full Version : sql server VBA help



QuietRiot
11-15-2007, 04:27 PM
I'm using this code below to import data into a spreadsheet. The problem is it doesn't include the headers/titles of the columns. How do I include field names? I know if I record I see something similiar to .fieldnames = true

here is the code I would like to work with instead..



Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "servername"
Database_Name = "dbname"
User_ID = "******"
Password = "****"

SQLStr = "select * from dbo.TransactionInformation where fundgroupnumber = '090' and postingdate =" & QueryDate

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic


With Worksheets("Query").Range("A2")
.ClearContents
.CopyFromRecordset rs
End With

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing


any help,
thanks

QuietRiot
11-15-2007, 04:41 PM
I was thinking something like this. if someone can confirm it looking right. I'll give it a shot tomorrow at work



With rs
.Open SQLStr, Cn, adOpenStatic
For i = 0 To objRecordset.Fields.Count - 1
Cells(1, i + 4).Value = objRecordset.Fields(i).Name
Next i
Worksheets("query").Range("A2").CopyFromRecordset rs
End With

Bob Phillips
11-15-2007, 04:47 PM
That is the general idea.

XLGibbs
11-15-2007, 05:42 PM
I was thinking something like this. if someone can confirm it looking right. I'll give it a shot tomorrow at work



With rs
.Open SQLStr, Cn, adOpenStatic
For i = 0 To objRecordset.Fields.Count - 1
Cells(1, i + 4).Value = objRecordset.Fields(i).Name
Next i
Worksheets("query").Range("A2").CopyFromRecordset rs
End With


I have used that precise logic to do what you describe..