General ODBC Error

[t16turbo]

hi guys and gals,

I'v written a fairly simple bit of VBA that runs some SQL on a datasource.

when I run it, howver, it returns a General ODBC Error and highlights the '.Refresh BackgroundQuery:=False' line
I'm passing a Date into the Sub called 'datepicked'
this comes in in the form #01/09/2005#

can anyone suggest why this might be throwing up an error?

Sub AutoTHAI(datepicked)

    Dim sSQL As String
    Dim sConn As String
    
     sConn = "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=nikulive.dstintl.com;"
     
sSQL = " SELECT x.dsti_true_client3,c2.company_name,wipunion.transtype, NEXT_DAY(wipunion.transdate, 'Friday'), NEXT_DAY(TRUNC(wipunion.entrydate), 'Friday'), sum(NVL(wipunion.quantity,0)) AS quantity,x.dsti_proj_ref,wipunion.project_code,wipunion.resource_code,c.company_name,d.name project_name,e.external_id,e.first_name,e.last_name "
sSQL = sSQL & " FROM(select x.charge_code,x.TRANSNO,x.TRANSTYPE,x.TRANSDATE,x.ENTRYDATE,x.QUANTITY,x.PROJECT_CODE,x.RESOURCE_CODE,x.COMPANY_CODE,x.STATUS,x.TASK_ID,x.emplyhomedepart FROM niku.ppa_wip x Union ALL SELECT y.charge_code,y.TRANSNO,y.TRANSTYPE,y.TRANSDATE,y.ENTRYDATE,y.QUANTITY,y.PROJECT_CODE,y.RESOURCE_CODE,y.COMPANY_CODE,0,y.TASK_ID,y.emplyhomedepart FROM niku.ppa_transcontrol y) wipunion, niku.srm_companies c, niku.srm_companies c2, niku.srm_projects d, niku.srm_resources e, niku.odf_ca_project x, niku.nbi_project_current_facts nbi, niku.prchargecode chg "
sSQL = sSQL & " WHERE c2.company_id (+) = x.dsti_true_client3 AND chg.prexternalid = wipunion.charge_code AND nbi.project_code = d.unique_name AND wipunion.company_code = c.company_id (+) AND wipunion.project_code = UPPER( d.unique_name ) AND wipunion.resource_code = e.unique_name AND wipunion.status <> 2 And x.ID = d.ID AND wipunion.company_code = 'DST01UKT' AND wipunion.entrydate >= " & datepicked
sSQL = sSQL & " Group BY wipunion.transtype, NEXT_DAY(wipunion.transdate, 'Friday'), NEXT_DAY(TRUNC(wipunion.entrydate), 'Friday'), x.dsti_proj_ref, wipunion.project_code, wipunion.resource_code, c.company_name, d.name, e.external_id, e.first_name, e.last_name, wipunion.task_id, x.dsti_true_client3, c2.company_name "



 With Sheets("RAW").QueryTables(1)
            .Destination = Sheets("RAW").Cells(1, 1)
            .Connection = sConn
            .CommandText = sSQL
            .Refresh BackgroundQuery:=False
            End With
            
            End Sub

 

[xlbo]

almost certainly to do with date syntax and whether it is being passed a true date or not

try 'Date'

or try checking whether the function is accepting a real date or a string date - this should tell you:

Sub AutoTHAI(datepicked as Date)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

thanks Geoff,

it WAS the date. all sorted now!

regards,
Matt

 

[comaboy]

Geoff,

You *need* to know this, apologies if you already do, but...

Just in case you don't know - SQL uses a different parameter set up that Access... so if you have a LIKE clause, you have to change the *'s to %'s so that eg

...WHERE Surname LIKE "*mith*"
becomes
...WHERE Surname LIKE "%mith%"

Same is true of "?", #date# becomes 'date' and more!

Reason I'm posting is that MS never really publicised this back in '97 and the first time I used (Access) OBDC layers/remote SQL Servers I banged my head on *many* walls!

If you didn't know this, Google up Access SQL Parameters to get more on the subject!

(Just my 2p)

 

[xlbo]

yup - well aware - I query Access / SQL Server / Oracle and any number of random databases - all of which use subtly different syntax

always worth mentioning though...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question