VBA SQL Query by date

[Human_doing]

Hi all,

Can anyone please help with amending the below VBA, which at present hooks in to a SQL database and retreives all rows where frm_last_updated > 13/07/2011 00:00.

I'm looking to amend it so that it retrieves all rows where the frm_last_updated value is within the last 24 hours?

Thanks in advance for any help,

 With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Sqlserver2;Description=SQLSERVER2;UID=audituser;;APP=Microsoft Office 2003;WSID=PCID1111 _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT AuditData_AuditForm.frm_id, AuditData_AuditForm.frm_status, AuditData_AuditForm.frm_last_updated" & Chr(13) & "" & Chr(10) & "FROM SQLInfo.dbo.AuditData_AuditForm AuditData_AuditForm" & Chr(13) & "" & Chr(10) & "WHERE (AuditData_AuditForm.frm_last_u" _
        , "pdated>={ts '2011-07-13 00:00:00'})")
        .Name = "Query from Sqlserver2_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True

 

[Trevor G]

Please don't send out double threads, if you are looking to see if someone can help you then you can **bounce**

http://www.mrexcel.com/forum/showthread.php?t=563923

 

[Human_doing]

Yes, I posted the previous question on the wrong board and have asked an administrator to delete the previous one, apologies for the duplication.

 

[Richard Schollar]

Hi

you can format a date value and incorporate this within the code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Sqlserver2;Description=SQLSERVER2;UID=audituser;;APP=Microsoft Office 2003;WSID=PCID1111 _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT AuditData_AuditForm.frm_id, AuditData_AuditForm.frm_status, AuditData_AuditForm.frm_last_updated" & Chr(13) & "" & Chr(10) & "FROM SQLInfo.dbo.AuditData_AuditForm AuditData_AuditForm" & Chr(13) & "" & Chr(10) & "WHERE (AuditData_AuditForm.frm_last_u" _
        , "pdated>={ts '" & Format(Date-1,"yyyy-mm-dd") & " 00:00:00'})")
        .Name = "Query from Sqlserver2_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True

Amendment is in red.

 

[Human_doing]

Hi, thanks so much for getting back to me, the full syntax of my query is below, I've tried inserting your code addition to this but it hasn't worked could you please amend?

 With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Sqlserver2;Description=SQLSERVER2;UID=audituser;PWD=pword;APP=Microsoft Office 2003;WSID=PCID1111;DATABASE=SQLInfo" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT AuditData_AuditForm.frm_status, AuditData_AuditForm.frm_id, AuditData_AuditForm.frm_title, AuditData_AuditForm.frm_approved_date, AuditData_AuditForm.frm_name, AuditData_AuditForm.frm_health_re" _
        , _
        "cs_location, AuditData_AuditForm.frm_end_date, AuditData_AuditForm.frm_no_health_recs, AuditData_AuditForm.frm_health_recs_batches" & Chr(13) & "" & Chr(10) & "FROM SQLInfo.dbo.AuditData_AuditForm AuditData_AuditForm" & Chr(13) & "" & Chr(10) & "WHERE (Aud" _
        , "itData_AuditForm.frm_status='APPROVED')" & Chr(13) & "" & Chr(10) & "ORDER BY AuditData_AuditForm.frm_id" _
        )
        .Name = "Query from Sqlserver2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

 

[Human_doing]

Hi all,

Could anyone with a good grasp of SQL and VBA please help with the above query, Richard has been very helpful in providing additional SQL to run the query but I'm really required to insert it in to the full VBA below rather than the shortened version I posted initially, any help much appreciated. I understand the additional code relating to date needs to go before ORDER BY but not sure exactly where.

With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Sqlserver2;Description=SQLSERVER2;UID=audituser;PWD=pword;APP=Microsoft Office 2003;WSID=PCID1111;DATABASE=SQLInfo" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT AuditData_AuditForm.frm_status, AuditData_AuditForm.frm_id, AuditData_AuditForm.frm_title, AuditData_AuditForm.frm_approved_date, AuditData_AuditForm.frm_name, AuditData_AuditForm.frm_health_re" _
        , _
        "cs_location, AuditData_AuditForm.frm_end_date, AuditData_AuditForm.frm_no_health_recs, AuditData_AuditForm.frm_health_recs_batches" & Chr(13) & "" & Chr(10) & "FROM SQLInfo.dbo.AuditData_AuditForm AuditData_AuditForm" & Chr(13) & "" & Chr(10) & "WHERE (Aud" _
        , "itData_AuditForm.frm_status='APPROVED')" & Chr(13) & "" & Chr(10) & "ORDER BY AuditData_AuditForm.frm_id" _
        )
        .Name = "Query from Sqlserver2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
 
[/CODE}

 

[Richard Schollar]

Here you go - try this:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Sqlserver2;Description=SQLSERVER2;UID=audituser;PWD=pword;APP=Microsoft Office 2003;WSID=PCID1111;DATABASE=SQLInfo" _
, Destination:=Range("A1"))
  .CommandText = 
  "SELECT AuditData_AuditForm.frm_status,   AuditData_AuditForm.frm_id, AuditData_AuditForm.frm_title, AuditData_AuditForm.frm_approved_date," & _
  "AuditData_AuditForm.frm_name, AuditData_AuditForm.frm_health_recs_location, AuditData_AuditForm.frm_end_date, AuditData_AuditForm.frm_no_health_recs, " & _
   "AuditData_AuditForm.frm_health_recs_batches FROM SQLInfo.dbo.AuditData_AuditForm AuditData_AuditForm WHERE AuditData_AuditForm.frm_status='APPROVED' " & _
   "AND AuditData_AuditForm.frm_last_updated>={ts '" & Format(Date-1,"yyyy-mm-dd") & " 00:00:00'} ORDER BY AuditData_AuditForm.frm_id"
  .Name = "Query from Sqlserver2"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
   .Refresh BackgroundQuery:=False
End With

 

[Human_doing]

Thanks so much for your help that's got it,

Thanks