SQL VBA problem

[robertokalos]

Hello,

I have a macro that doesnt work. The problem is at the 2nd date restriction that I have ((""CR-Ticket"".""Request Date""<={ ts '" & Format(Range("A1").Value, "yyyy-mm-dd 00:00:00") & "'})

Do you know, what I am doing wrong?

[SIZE=3][FONT=Times New Roman]With Selection.QueryTable<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    .Connection = _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    "ODBC;DRIVER={AR System ODBC Driver};ARServer=HAYDN;UID=kyster;PWD=password;ARAuthentication=;SERVER=NotTheServer"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    .CommandText = Array( _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    "SELECT ""CR-Ticket"".Application, ""CR-Ticket"".""Business Priority"", ""CR-Ticket"".Item, ""CR-Ticket"".""Request Date"", ""CR-Ticket"".Department, ""CR-Ticket"".""Requested by"", ""CR-Ticket"".""Service Type"", ""CR-Ticke" _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    , _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    "t"".""CR ID"", ""CR-Ticket"".Status, ""CR-Ticket"".""Assigned To"", ""CR-Ticket"".""Closed Date"", ""CR-Ticket"".Priority, ""CR-Ticket"".Phase, ""CR-Ticket"".""Target Release"", ""CR-Ticket"".Module, ""CR-Ticket"".Entity" & Chr(13) & "" & Chr(10) & "FRO" _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    , _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    "M ""CR-Ticket"" ""CR-Ticket""" & Chr(13) & "" & Chr(10) & "WHERE (""CR-Ticket"".Application='CLM') AND (""CR-Ticket"".Status<>'Rejected' And ""CR-Ticket"".Status<>'Closed') AND (""CR-Ticket"".Entity='Finance+Risk') AND (""CR-Ticket"".""Request" _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    , _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    " Date""<={ts '" & Format(Range("A1").Value, "yyyy-mm-dd 00:00:00") & "'}) OR (""CR-Ticket"".Status<>'Rejected' And ""CR-Ticket"".Status<>'Closed') AND (""CR-Ticket"".Entity='FKL Project') AND (""CR-Ticket"".""Request Date""<={ ts '" & Format(Range("A1").Value, "yyyy-mm-dd 00:00:00") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY ""CR-Ticket"".""Service Type"" DESC, ""CR-Ticket"".Status, ""CR-Ticket"".""Assigned To"", ""CR-Ticket"".""CR ID""" _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    )<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    .Refresh BackgroundQuery:=False<o:p></o:p>[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End With[/SIZE][/FONT]

 

[sclem1016]

Hello.


This code previously worked and suddenly it will not refresh the back ground query anymore:

Sub LoadFromDb(sDb As String, sSql As String, sTab As String, sPath As String, sRange As String)
With Worksheets(sTab).QueryTables.Add( _
Connection:=Array(Array( _
"ODBC;DRIVER={Microsoft Access Driver(*.mdb, *.accdb)};DBQ=" & sPath & sDb & ";DefaultDir=" & sPath & ";" _
), Array( _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Worksheets(sTab).Range(sRange))
.CommandText = sSql
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
'DeleteRangeNames
End Sub

It runs fine until I get to the .Refresh BackgroundQuery:=False after it tries to execute the code I get a run time error 1004 General ODBC error. I am passing SQL code to run in the database, and I have tested the SQL code in Access and the query runs fine. I am using Office 2007 and Windows 7 64 bit. I am stumped. Can someone please offer some advice?