Connection from Excel to SQL
 

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=Micr osoft®
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

Connection from Excel to SQL
 

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®
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" wrote in message
...
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=Micr osoft®
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

Connection from Excel to SQL
 

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

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®
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" wrote in message
...
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=Micr osoft®
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

Connection from Excel to SQL
 

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" wrote in message
...
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" wrote:

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®

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

"Phil" wrote in message
...
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=Micr osoft®

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

Connection from Excel to SQL
 

On Apr 23, 4:08 am, "NickHK" wrote:
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" wrote in message

...



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

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®

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

"Phil" wrote in message
...
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=Micr osoft®

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;UI D=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;UI D=" &
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

Connection from Excel to SQL
 

On Apr 23, 8:21 am, Don wrote:
On Apr 23, 4:08 am, "NickHK" wrote:



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" wrote in message

...

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

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®

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

"Phil" wrote in message
...
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=Micr osoft®

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;UI D=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;UI D=" &
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?

Connection from Excel to SQL
 

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

Thanks

MH

"NickHK" wrote in message
...
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" wrote in message
...
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" wrote:

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®

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

"Phil" wrote in message
...
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=Micr osoft®

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