Access Queries not running properly in VB6

[EddiRae]

I am adding queries that I created in Access to run in an application.
I have given you the meat of the subroutine.
The tabledefs.delete is working. It is the rest of the process that is not working.

If you could let me know what I have wrong, I would greatly appreciate it.

The code is below.

    Dim db As Database
    Dim ws As Workspace
    Dim qry As QueryDef


    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(sStatsPath)

    For i = 0 To imt
        db.TableDefs.Delete ("Stp01-DailyDetail")
        db.TableDefs.Delete ("Stp02-ReschedEst")
        db.TableDefs.Delete ("Stp03-Manhours")
        
        'Remove data for date
        Set qry = db.QueryDefs("DELETE")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
        
        Set qry = db.QueryDefs("ACCESS_Raw Data - D")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
        
        Set qry = db.QueryDefs("ACCESS_Res-Est-S")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
        
        Set qry = db.QueryDefs("ACCESS_Manhour-S")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
    
        Set qry = db.QueryDefs("Answers-S")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
    
        calcdate = DateAdd("d", 1, calcdate)
    Next i

Eddi Rae Melton

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

Care to give us a hint as to what "not working" means in this case? (eg: is there an error? if so what was it, and which line was it on? ...).

[EddiRae]

The "ACCESS_Raw Data - D" query is suppose to make the table with a specific date. When I run this in Access, I get data. When I run in VB, I am not getting any data.

Sorry for the confusion.
Eddi Rae

[si_the_geek]

OK, well that's a step closer.

What is the data type of the calc_date parameter?

What is the data type and value of the calcdate variable?

[EddiRae]

What is the data type of the calc_date parameter?
In the Access database, the calc_date is a date. I don't have a parameter actually set up. I just have the query being filtered by the [calc_date].

What is the data type and value of the calcdate variable?
In the VB application the calcdate date is a date. The value being passed is 11/21/2008. There are no quotes or anything.

[EddiRae]

I figured it out!! You helped me walk through what I couldn't see.

I had the dates being create as follows:

'Set dates to run until now
    dFromDate = #1/1/2007#
    dToDate = Format(Now(), "mm/dd/yyyy")
          
    On Error Resume Next        ' Need just in case object does not exist
    imt = DateDiff("d", dFromDate, dToDate)
    
    calcdate = dFromDate

The dFromDate is a date variable. Once I changed the #1/1/2007# to be "1/1/2007", it worked!!

Thank you for all your help!!!
Eddi Rae

[EddiRae]

I take it back ... it is still not working ...

Here is the entire subroutine ....

Public Sub BuildStats_ACCESS()
On Error GoTo Err_BuildStats_ACCESS
    Dim db As Database
    Dim ws As Workspace
    Dim qry As QueryDef
    Dim strsql As String
    Dim frm As Form
    Dim calcdate As Date
    Dim strDate As String
    Dim i As Integer
    Dim imt As Integer
    Dim dToDate As Date
    Dim dFromDate As Date
    On Error GoTo Err_BuildStats_ACCESS

    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(sStatsPath)
    
    'Set dates to run until now
    dFromDate = "1/1/2007"
    dToDate = Format(Now(), "mm/dd/yyyy")
             
    On Error Resume Next        ' Need just in case object does not exist
    imt = DateDiff("d", dFromDate, dToDate)
        
    calcdate = dFromDate

    For i = 0 To imt
        db.TableDefs.Delete ("Stp01-DailyDetail")
        db.TableDefs.Delete ("Stp02-ReschedEst")
        db.TableDefs.Delete ("Stp03-Manhours")
        
        'Remove data for date
        Set qry = db.QueryDefs("DELETE")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
        
        Set qry = db.QueryDefs("ACCESS_Raw Data - D")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
        
        Set qry = db.QueryDefs("ACCESS_Res-Est-S")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
        
        Set qry = db.QueryDefs("ACCESS_Manhour-S")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
    
        Set qry = db.QueryDefs("Answers-S")
        qry.Parameters("calc_date") = calcdate
        qry.Execute
        qry.Close
    
        calcdate = DateAdd("d", 1, calcdate)

        If calcdate = "11/21/2008" Then
            MsgBox "Get Ready"
        End If
    Next i
    db.Close
    ws.Close
    MsgBox "Build Complete"
Exit_BuildStats_ACCESS:
    Exit Sub

Err_BuildStats_ACCESS:
    MsgBox Err.Description
    Resume Exit_BuildStats_ACCESS
End Sub

let me know if you see anything. I cannot figure this out.

[si_the_geek]

You should never use values inside "" (a String) or the result of Format (which returns a String) to put a value into a Date variable - as there is an implicit type conversion which is likely to give the wrong value. You should always use values which have a data type of Date (such as those returned by ## and Now and DateAdd).

For more information, see the article Why are my dates not working properly? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)


In addition to that, you should avoid using "On Error Resume Next". It certainly isn't needed in this case.


That section of your code should be like this:

    'Set dates to run until now
    dFromDate = "1/1/2007"
    dToDate = Date   '(Now also includes the time, which you don't want)
             
    imt = DateDiff("d", dFromDate, dToDate)
        
    calcdate = dFromDate

I haven't used DAO in years, and never used QueryDef's, so I'm not sure what the issue(s) might be.

At the moment all I can suggest is reducing the amount of things you are doing - run just one of the QueryDef's to see if it is doing what you want, as another one may be altering the results.

[EddiRae]

As you can see from my first post, I was using the "#" around the beginning date. That was not working.

I will remove the on error that you referenced.

[EddiRae]

I found it!!! It was so simple that I didn't see it. Since I have spaces in the query name, you have to place brackets around the query name!!!

This statement ...

Set qry = db.QueryDefs("ACCESS_Raw Data - D")

Should be ...

Set qry = db.QueryDefs("[ACCESS_Raw Data - D]")

Thanks for all the help!!!
Eddi Rae

[si_the_geek]

Well you're not alone, I missed that too - and it's the kind of thing I normally look out for!

I'm a bit surprised you need the square brackets in that particular situation, as normally functions add them automatically - but I guess this is one of those that doesn't.


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".