Connection from Excel to SQL

[Phil]

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

 

[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")

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

 

[Phil]

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

 

[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

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

SQL Server 2003?

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

Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"

 

[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,

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

:
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"

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

 

[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

news[email protected]...

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
:
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_Connect­­i
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®

Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connect­­i
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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 -

Is your ODBC connectivity test successful?

 

[MH]

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

Thanks

MH

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,

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

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_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®

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