Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
Socket Connection via Excel | Excel Programming | |||
Excel VBA via ADO connection | Excel Programming | |||
Database Connection from Excel | Excel Programming | |||
no connection to excel | Excel Programming |