Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error approching SQL server with DAO?
Hi,
At work the old Server was about to be raplaced when it crashed. Now there is a new server with a new name. With the following code it was always easy to retreve thye information needed. (recorded macro to get data based on hard criteria, replaced with variable): With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=Intrack;SERVER=PDC_SERVER;UID=km;APP=Mic rosoft® Query;WSID=DLW140;DATABASE=Intrack;Address=\\pdc_s erver\pipe\local\query;Qu" _ ), Array("eryLogFile=Yes")), Destination:=Range("HA1")) .sql = Array( _ "SELECT apv_Kaasplanning.Jaar, apv_Kaasplanning.Week, apv_Kaasplanning.Charge, apv_Kaasplanning.Volgnummer, apv_Kaasplanning.Kaassoort, apv_Kaasplanning.Bakken" & Chr(13) & "" & Chr(10) & "FROM Intrack.dbo.apv_Kaasplanning apv_Ka" _ , _ "asplanning" & Chr(13) & "" & Chr(10) & "WHERE (apv_Kaasplanning.Jaar='" & jaar & "') AND (apv_Kaasplanning.Week='" & weeknr & "')" & Chr(13) & "" & Chr(10) & "ORDER BY apv_Kaasplanning.Charge, apv_Kaasplanning.Volgnummer" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With After i changed the server name in this code a login pop up came true. This was easaly. No login name and No password, voila. It works. Now the problem: Some scripting to get data from the database is not a modifyd recorded macro, it was made by someone else.(DAO?) I cant get it to work. Set wrkspace = CreateWorkspace("IntrackDB", "Intrack", "", dbUseODBC) Set con = wrkspace.OpenConnection("IntrackDB", dbDriverNoPrompt, True, "ODBC;DSN=Intrack;SERVER=PDC_SERVER;UID=km;APP=Mic rosoft® Query;WSID=DLW140;DATABASE=Intrack;Address=\\pdc_s erver\pipe\local\query;QueryLogFile=Yes") I tried removing the UID as i did in the recorded macro. No effects. And i did change the server name. Any idea what going wrong? I forgot to write down the error number returned. it was something 3000 TIA Rody |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error approching SQL server with DAO?
Rody
I find DAO with DSN is an awkward way to connect to a database, technology has moved on since :) Also I'd never pump a query to column HA??? (waste of memory) Also WHY are you logging the query calls, but since you are.. have you checked the log? For testing.. change dbDriverNoPrompt to dbDriverPrompt else give me a call. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Rody Meulman wrote : Hi, At work the old Server was about to be raplaced when it crashed. Now there is a new server with a new name. With the following code it was always easy to retreve thye information needed. (recorded macro to get data based on hard criteria, replaced with variable): With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=Intrack;SERVER=PDC_SERVER;UID=km;APP=Mic rosoft® Query;WSID=DLW140;DATABASE=Intrack;Address=\\pdc_s erver\pipe\local\que ry;Qu" _ ), Array("eryLogFile=Yes")), Destination:=Range("HA1")) .sql = Array( _ "SELECT apv_Kaasplanning.Jaar, apv_Kaasplanning.Week, apv_Kaasplanning.Charge, apv_Kaasplanning.Volgnummer, apv_Kaasplanning.Kaassoort, apv_Kaasplanning.Bakken" & Chr(13) & "" & Chr(10) & "FROM Intrack.dbo.apv_Kaasplanning apv_Ka" _ , _ "asplanning" & Chr(13) & "" & Chr(10) & "WHERE (apv_Kaasplanning.Jaar='" & jaar & "') AND (apv_Kaasplanning.Week='" & weeknr & "')" & Chr(13) & "" & Chr(10) & "ORDER BY apv_Kaasplanning.Charge, apv_Kaasplanning.Volgnummer" _ ) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With After i changed the server name in this code a login pop up came true. This was easaly. No login name and No password, voila. It works. Now the problem: Some scripting to get data from the database is not a modifyd recorded macro, it was made by someone else.(DAO?) I cant get it to work. Set wrkspace = CreateWorkspace("IntrackDB", "Intrack", "", dbUseODBC) Set con = wrkspace.OpenConnection("IntrackDB", dbDriverNoPrompt, True, "ODBC;DSN=Intrack;SERVER=PDC_SERVER;UID=km;APP=Mic rosoft® Query;WSID=DLW140;DATABASE=Intrack;Address=\\pdc_s erver\pipe\local\que ry;QueryLogFile=Yes") I tried removing the UID as i did in the recorded macro. No effects. And i did change the server name. Any idea what going wrong? I forgot to write down the error number returned. it was something 3000 TIA Rody |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL Server Error message when starting XP | Excel Discussion (Misc queries) | |||
Excel 2003 Error Cannot Locate the Internet Server or Proxy Server | Excel Discussion (Misc queries) | |||
SQL Server ODBC Error | Excel Discussion (Misc queries) | |||
Submit to SQL Server error | Excel Discussion (Misc queries) | |||
error saving a new file on server | Excel Discussion (Misc queries) |