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

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" <(E-Mail Removed)> wrote in message
news:57A5941C-F0F9-4C0F-95C0-(E-Mail Removed)...
> 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

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=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" <(E-Mail Removed)> wrote in message
> news:57A5941C-F0F9-4C0F-95C0-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
newsE6EC5FF-7BD1-44D1-A8FB-(E-Mail Removed)...
> 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=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
> >
> > "Phil" <(E-Mail Removed)> wrote in message
> > news:57A5941C-F0F9-4C0F-95C0-(E-Mail Removed)...
> > > 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
> >
> >
> >

On Apr 23, 4:08 am, "NickHK" <TungChe...@Invalid.com> 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" <P...@discussions.microsoft.com> wrote in message
>
> newsE6EC5FF-7BD1-44D1-A8FB-(E-Mail Removed)...
>
>
>
> > 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=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
>
> > > "Phil" <P...@discussions.microsoft.com> wrote in message
> > >news:57A5941C-F0F9-4C0F-95C0-(E-Mail Removed)...
> > > > 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

On Apr 23, 8:21 am, Don <d...@att.com> wrote:
> On Apr 23, 4:08 am, "NickHK" <TungChe...@Invalid.com> 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" <P...@discussions.microsoft.com> wrote in message
>
> >newsE6EC5FF-7BD1-44D1-A8FB-(E-Mail Removed)...
>
> > > 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=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
>
> > > > "Phil" <P...@discussions.microsoft.com> wrote in message
> > > >news:57A5941C-F0F9-4C0F-95C0-(E-Mail Removed)...
> > > > > 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?

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

Thanks

MH

"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> newsE6EC5FF-7BD1-44D1-A8FB-(E-Mail Removed)...
>> 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=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
>> >
>> > "Phil" <(E-Mail Removed)> wrote in message
>> > news:57A5941C-F0F9-4C0F-95C0-(E-Mail Removed)...
>> > > 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
>> >
>> >
>> >
>
>