Connection from Excel to SQL

G

Guest

Hi,

Hope someone can help, I am trying to pull data into Excel from SQL Server
2003 and my script keeps failing, I think it might have something to do with
the way that I am construting my access string and specificaly the database
part as I am not sure if it needs to be surrounded by either brackets or ""
tried both ways but not getting anywhere.

Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Microsoft®
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")
Debug.Print .CommandText
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

Again, hope someone can help

Thanks PD
 
M

MH

SQL Server 2003?
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Microsoft®
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connection=Yes"
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")
Click to expand...

The WITH block in the above snippet should read:

With ActiveSheet
.QueryTables...

I suspect that is why your code is failing but without any error messages to
go on it's hard to guess!

MH
 
G

Guest

Hi MH,

Yes it is with 2003, I tried your suggestion and get the same problem, I
hace the following error messages :

Connection failed:
SQLState: '01S00'
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been denied

Sorry about that, hope that this is a little more informative

Thanks PD
 
N

NickHK

Phil,
You can create/test connection string with this method:
- Create a new text file somewhere suitable.
- Rename to Whatever.udl
- Double click and follow the wizard, filling in info
- Click OK.
- Open the file in a text editor and copy the connection string.

MZ-Tools uses this method in its add:
http://mztools.com/index.htm

NickHK

Phil said:
Hi MH,

Yes it is with 2003, I tried your suggestion and get the same problem, I
hace the following error messages :

Connection failed:
SQLState: '01S00'
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been denied

Sorry about that, hope that this is a little more informative

Thanks PD

MH said:
SQL Server 2003?
Click to expand...
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"
The WITH block in the above snippet should read:

With ActiveSheet
.QueryTables...

I suspect that is why your code is failing but without any error messages to
go on it's hard to guess!

MH
Click to expand...
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"
Click to expand...
 
D

Don

Phil,
You can create/test connection string with this method:
- Create a new text file somewhere suitable.
- Rename to Whatever.udl
- Double click and follow the wizard, filling in info
- Click OK.
- Open the file in a text editor and copy the connection string.

MZ-Tools uses this method in its add:http://mztools.com/index.htm

NickHK




Hi MH,
Click to expand...
Yes it is with 2003, I tried your suggestion and get the same problem, I
hace the following error messages :
Click to expand...
Connection failed:
SQLState: '01S00'
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been denied

Sorry about that, hope that this is a little more informative
Click to expand...
Thanks PD
Click to expand...
:
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connect­i
on=Yes"



Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connect­i
on=Yes"
Click to expand...

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...
I typically record a macro for determining the proper connection
string. Data.. Import External data.. New database query.

Following is the current method that I use for connecting to a SQL
database and the prior method I used :

' Execute Microsoft query
' 03-16-2007 query using generic ID
With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=DatabaseName;Description=DatabaseName;UID=id;PWD=pw;APP=Microsoft
Office 2003;WSID=" & NameOfComputer & ";Network=networkname" _
, Destination:=Range("A10"))
'
' 03-16-2007 prior query using own ID/username
' With ActiveSheet.QueryTables.Add(Connection:= _
' "ODBC;DSN=DatabaseName;Description=DatabaseName;UID=" &
UserName & ";APP=Microsoft Office 2003;WSID=" & NameOfComputer &
";Network=networkname;Trusted_Connection=Yes" _
' , Destination:=Range("A10"))
.CommandText = Array( _
"SELECT ----sql statements-----)
.Name = "Query from DatabaseName"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
' .BackgroundQuery = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
' .SavePassword = True
.SavePassword = False
' .SaveData = True
.SaveData = False
' .AdjustColumnWidth = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Don
 
D

Don

Phil,
You can create/test connection string with this method:
- Create a new text file somewhere suitable.
- Rename to Whatever.udl
- Double click and follow the wizard, filling in info
- Click OK.
- Open the file in a text editor and copy the connection string.
Click to expand...
MZ-Tools uses this method in its add:http://mztools.com/index.htm
NickHK
Click to expand...

news:D[email protected]...
Click to expand...
Hi MH,
Yes it is with 2003, I tried your suggestion and get the same problem, I
hace the following error messages :
Connection failed:
SQLState: '01S00'
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been denied
Click to expand...
Sorry about that, hope that this is a little more informative
Thanks PD
:
SQL Server 2003?
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Microsoft®
Click to expand...
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connect­­i
on=Yes"
Click to expand...
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")
The WITH block in the above snippet should read:
With ActiveSheet
.QueryTables...
I suspect that is why your code is failing but without any error messages to
go on it's hard to guess!
MH
Hi,
Hope someone can help, I am trying to pull data into Excel from SQL Server
2003 and my script keeps failing, I think it might have something to do
with
the way that I am construting my access string and specificaly the
database
part as I am not sure if it needs to be surrounded by either brackets or
""
tried both ways but not getting anywhere.
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Microsoft®
Click to expand...
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connect­­i
on=Yes"
Click to expand...
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")
Debug.Print .CommandText
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
Again, hope someone can help
Thanks PD- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I typically record a macro for determining the proper connection
string. Data.. Import External data.. New database query.

Following is the current method that I use for connecting to a SQL
database and the prior method I used :

' Execute Microsoft query
' 03-16-2007 query using generic ID
With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=DatabaseName;Description=DatabaseName;UID=id;PWD=pw;APP=Microsoft
Office 2003;WSID=" & NameOfComputer & ";Network=networkname" _
, Destination:=Range("A10"))
'
' 03-16-2007 prior query using own ID/username
' With ActiveSheet.QueryTables.Add(Connection:= _
' "ODBC;DSN=DatabaseName;Description=DatabaseName;UID=" &
UserName & ";APP=Microsoft Office 2003;WSID=" & NameOfComputer &
";Network=networkname;Trusted_Connection=Yes" _
' , Destination:=Range("A10"))
.CommandText = Array( _
"SELECT ----sql statements-----)
.Name = "Query from DatabaseName"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
' .BackgroundQuery = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
' .SavePassword = True
.SavePassword = False
' .SaveData = True
.SaveData = False
' .AdjustColumnWidth = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Don- Hide quoted text -

- Show quoted text -
Click to expand...

Is your ODBC connectivity test successful?
 
M

MH

Nick, that is a great tip, I have not come accross that one before.

Thanks

MH

NickHK said:
Phil,
You can create/test connection string with this method:
- Create a new text file somewhere suitable.
- Rename to Whatever.udl
- Double click and follow the wizard, filling in info
- Click OK.
- Open the file in a text editor and copy the connection string.

MZ-Tools uses this method in its add:
http://mztools.com/index.htm

NickHK

Phil said:
Hi MH,

Yes it is with 2003, I tried your suggestion and get the same problem, I
hace the following error messages :

Connection failed:
SQLState: '01S00'
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been denied

Sorry about that, hope that this is a little more informative

Thanks PD

MH said:
SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Microsoft®
Click to expand...
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")

The WITH block in the above snippet should read:

With ActiveSheet
.QueryTables...

I suspect that is why your code is failing but without any error messages to
go on it's hard to guess!

MH

Hi,

Hope someone can help, I am trying to pull data into Excel from SQL Server
2003 and my script keeps failing, I think it might have something to do
with
the way that I am construting my access string and specificaly the
database
part as I am not sure if it needs to be surrounded by either brackets or
""
tried both ways but not getting anywhere.

Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Microsoft®
Click to expand...
Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")
Debug.Print .CommandText
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

Again, hope someone can help

Thanks PD
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

access selective data...using query 5
Import Data from SQL server to excel 0
Database query. 6
Connection to MySQL using VBA 1
Web query - unable to extract data 3
How do you importing unicode from web 2
Help! Error 1004 2
Problem with varaible dates in SQL query 2

Top