Error approching SQL server with DAO?

[Rody Meulman]

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(Array( _
"ODBC;DSN=Intrack;SERVER=PDC_SERVER;UID=km;APP=Microsoft®
Query;WSID=DLW140;DATABASE=Intrack;Address=\\pdc_server\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=Microsoft®
Query;WSID=DLW140;DATABASE=Intrack;Address=\\pdc_server\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

 

[keepITcool]

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 :