Get data for previous dates and a database oddity

[RichardMGreen]

Hi all

I suppose this is two questions in one.
Firstly, I have a spreadsheet with a load of query names which are run via some code (I'll post if if anyone needs it). Most of the queries require an input which is supplied via cells on the spreadsheet and work OK. Two queries, however, don't need any inputs and are not returning any data to the spreadsheet even though they have records when run in the database.

Anyone any ideas on that one?


Secondly, within the database I need to be able to get data for a month 6 and 12 months ago from a supplied date.
E.g. If I supplied todays date, then I would want all data for January this year and July last year.
All the supplied dates are pre-built in the spreadheet (see above).
Anyone any ideas on this one?

I'm not sure if that is going to be clear enough, so feel free to ask for extra info (don't know what else anyone will need).

Any help would be greatly appreciated.

 

[RichardMGreen]

OK, managed to sort the second bit by working out the parameters and passing them in a similar manner to the other queries.

Anyone any ideas as to why a query run in the database returns results but doesn't return anything when run through Excel VBA?
If it helps, I'm using an ADODB connection to access the Access database.

 

[RichardMGreen]

Anyone, please?

 

[RichardMGreen]

Here's the code I'm using:-

    Dim conn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
    Dim wsData As Worksheet, wsDst As Worksheet, ws As Worksheet, wb As Workbook
    Dim rngData As Range, rngDst As Range, qt As QueryTable, response As String, savename As String
    Dim strQry As String, strSQL As String, file As String, strConn As String, outputlocation As String
    Dim maxquery As Integer, currentquery As Integer, reportingdate As String, vsion As Integer
    Dim param1, param2
'----- Get database password or cancel if required -----
    UserForm2.Show
    If cancel_button = True Then
        Exit Sub
    End If
'----- Set up database name and location -----
    Set wsData = Worksheets("Query_List")
    file = wsData.Range("H2")
'----- Clear out old data and set up userform -----
    maxquery = Sheets("Query_List").Range("A65535").End(xlUp).Row - 1
    Sheets("Section_1").Rows("3:10000").ClearContents
    Sheets("Section_2").Rows("3:10000").ClearContents
    Sheets("GP_reporting_data").Rows("3:10000").ClearContents
    Sheets("6_Month_data").Rows("3:10000").ClearContents
    Sheets("12_Month_data").Rows("3:10000").ClearContents
    currentquery = 0
'----- Turn off calculations and screen flicker -----
'    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
'----- Get all data -----
    On Error GoTo db_pass_error
    Set conn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Jet OLEDB:Database Password=" & db_pass & ";"
    conn.ConnectionString = strConn
    conn.Open
    UserForm1.Label1.Caption = "Refreshing Database Queries..."
    UserForm1.ProgressBar1.Value = 0
    UserForm1.Show
    Set rngData = wsData.Range("A2")
    Set cmd = New ADODB.Command
    On Error GoTo 0
    While rngData.Value <> ""
        UserForm1.Label1.Caption = "Refreshing " & rngData.Value
        UserForm1.Repaint
        strQry = "[" & rngData.Value & "]"
        param1 = rngData.Offset(, 3).Value
        param2 = rngData.Offset(, 4).Value
        strSQL = "SELECT * FROM " & strQry
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL
        cmd.ActiveConnection = conn
'----- Pass parameters if needed/available -----
        If Not (IsNull(param1)) Then cmd.Parameters(0) = param1
        If Not (IsNull(param2)) Then cmd.Parameters(1) = param2
'----- Pick up information on where data is to go -----
        Set wsDst = Worksheets(rngData.Offset(, 1).Value)
        Set rngDst = wsDst.Range(rngData.Offset(, 2).Value)
'----- Retrieve data from database and insert into correct cells -----
        Set rs = cmd.Execute
        If Not IsNull(rngDst) Then rngDst.CopyFromRecordset rs
        currentquery = currentquery + 1
'----- Make sure no data left to write and set up details for next query -----
        Set rs = Nothing
        Set rngData = rngData.Offset(1)
        UserForm1.ProgressBar1.Value = (currentquery / (maxquery + 1)) * 100
        UserForm1.Repaint
    Wend
    Set conn = Nothing

And here's the SQL for the query in question:-

SELECT ext_Assessments.ClientId, ext_Clients.firstname, ext_Clients.lastname, ext_Clients.Tel, ext_Clients.Mobile, ext_Assessments.Signoffdate, ext_Assessments.SignOffReason, ext_Assessments.SignoffStatus, ext_Assessments.PrimaryIssue, ext_Clients.ageBand, ext_Clients.gender
FROM ext_Clients INNER JOIN ext_Assessments ON ext_Clients.ClientId = ext_Assessments.ClientId
GROUP BY ext_Assessments.ClientId, ext_Clients.firstname, ext_Clients.lastname, ext_Clients.Tel, ext_Clients.Mobile, ext_Assessments.Signoffdate, ext_Assessments.SignOffReason, ext_Assessments.SignoffStatus, ext_Assessments.PrimaryIssue, ext_Clients.ageBand, ext_Clients.gender, ext_Assessments.DoneSignoff, ext_Assessments.DoneMaintenance, Month(ext_Assessments!Signoffdate), Year(ext_Assessments!Signoffdate)
HAVING (((ext_Assessments.SignOffReason) Like "PHP*") AND ((ext_Assessments.DoneSignoff)="true") AND ((ext_Assessments.DoneMaintenance)="false") AND ((Month([ext_Assessments]![Signoffdate]))=[Month Number]) AND ((Year([ext_Assessments]![Signoffdate]))=[Year]));

Userform1 is a progress bar to show where things are at any given time while the macro is running.
Userform2 is used as an input box to get the password for the database (better than it being hard-coded in the VBA).

All the other queries are run by this macro and return results fine, it's just this one that doesn't want to return anything.

 

[Norie]

Have you tried removing all the grouping and changing the HAVING clause to a WHERE clause?

I don't know if that'll make a difference but I'm also not sure why you are grouping, you don't seem to be summing or counting anything which is usually why you group.

 

[RichardMGreen]

I haven't tried that. I just built the query using the designer in Access but I couldn't paste that here.

I'll give that a go tomorrow when the systems running slightly faster that an lazy snail.

 

[RichardMGreen]

Hi Norie

Removed all the grouping and I'm still hitting the same problem.
This is the new SQL:

SELECT ext_Assessments.ClientId, ext_Clients.firstname, ext_Clients.lastname, ext_Clients.Tel, ext_Clients.Mobile, ext_Assessments.Signoffdate, ext_Assessments.SignOffReason, ext_Assessments.SignoffStatus, ext_Assessments.PrimaryIssue, ext_Clients.ageBand, ext_Clients.gender
FROM ext_Clients INNER JOIN ext_Assessments ON ext_Clients.ClientId=ext_Assessments.ClientId
WHERE (((ext_Assessments.SignOffReason) Like "PHP*") And ((ext_Assessments.DoneSignoff)="true") And ((ext_Assessments.DoneMaintenance)="false") And ((Month(ext_Assessments!Signoffdate))=[Month Number]) And ((Year(ext_Assessments!Signoffdate))=[Year]));

 

[james_lankford]

when you use like in a where clause you need to change * to %
so
firstname like '*james*'
would become
firstname like '%james%'

 

[RichardMGreen]

But the query itself runs fine in the database and returns the expected results (this is Access we're talking about, not "proper" SQL).

 

[james_lankford]

But the query itself runs fine in the database and returns the expected results (this is Access we're talking about, not "proper" SQL).

but its not Access, its ADODB

...
Anyone any ideas as to why a query run in the database returns results but doesn't return anything when run through Excel VBA?
If it helps, I'm using an ADODB connection to access the Access database.