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

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