Extract Data From Sql Server To Excel2007 using Windows Authentication 1

[BxWill]

What modifications are necessary to the vba code below to enable the extraction of Sql Server data to Excel 2007.

The vba code resides in the Excel module.

Note, I am using Windows Authentication to access the data via Sql Server Query Analyzer.


Did review the References and all appear to be included.

Public Sub ExtractData()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim adofld As ADODB.Field

Dim server As String
Dim dbname As String
Dim usernm As String
Dim tblname As String
Dim pword As String
Dim sqlstr As String
Dim whrclse As String
Dim x As Integer
Dim y As Integer

Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Set xlws = Sheets("Sheet1")

pword = InputBox("Please enter your password.", "Password Prompt")

server = xlws.Range("B3").Value
dbname = xlws.Range("B4").Value
usernm = xlws.Range("B5").Value
tblname = xlws.Range("B6").Value

Set adoconn = New ADODB.Connection
Set adors = New ADODB.Recordset

adoconn.ConnectionString = "Provider=sqloledb;Datasource=" & server & _
";Database=" & dbname & ";uid=" & usernm & _
";pwd=" & pword

adoconn.Open

sqlstr = "Select " & tblname & ".* from " & tblname

x = 12
y = 0

While xlws.Cells(x, 1).Value <> ""
y = y + 1
whrclse = whrclse & " AND " & xlws.Cells(x, 1).Value & _
" " & xlws.Cells(x, 2).Value
x = x + 1
Wend

If y <> 0 Then
whrclse = " WHERE 1 = 1 " & whrclse
sqlstr = sqlstr & whrclse
End If

adors.Open sqlstr, adoconn, adOpenStatic
Debug.Print sqlstr
Set xlws = Sheets("Sheet2")
xlws.Activate

x = 1

For Each fld In adors.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld

Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset adors
xlws.Columns.AutoFit


adors.Close
adoconn.Close
Set fld = Nothing
Set adors = Nothing
Set adoconn = Nothing
Set xlrng = Nothing
Set xlws = Nothing

End Sub

 

[xlbo]

Get rid of the input box for password and amend connection string to this kind of format

adoconn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Catalog=DatabaseName;Data Source=ServerName"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BxWill]

Error received is "incorrect syntax near 2468"

Note, 2468 is the customer code that I entered into the Excel cell.

For example, I am entering just one parameter into the Excel cell like as follows;

Parameters
Field--------Criteria
CustCode-----2468

Any additional thoughts as to a resolution for this error?

 

[SkipVought]

What is the value of sqlstr in the Immediate window?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BxWill]

Input a space between "Data" and "Source."

Now, the error message is "Invalid object name 'DIM_RECORD'."

Note, DIM_RECORD is the table name that is typed into Excel on Sheet 1.

The following line is highlighted in the code module - "adors.Open sqlstr, adoconn, adopenstatic"

The value of sqlstr is "Select DIM_RECORD.* from DIM_RECORD WHERE 1=1 AND CustCode =1234"

Any idea as to a resolution?

 

[xlbo]

does the database have schemas?

Sounhds like the connection isn't recognising DIM_RECORD as an object in teh database - possibly because the default schema may be different now you are using integrated security?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BxWill]

Still not able to get this to work...

It is a Sql Server database.

How do I determine if the database has schemas?

Some of the tables are prefixed with "dbo."

Maybe, I will try prefixing "DIM_RECORD" with the full name of the database (REPORT_FINANCIAL). In other words, I will revise to "REPORT_FINANCIAL.DIM_RECORD."

Any further insight is greatly appreciated.

 

[xlbo]

Take the string output of the query from vba and run or against the database in either mgmt studio or query analyser (depending on your version of SQL server) that should tell you If the query is syntactically correct

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question