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