Unskilled VB coding = createQueryDef doesn't work.

[oh4real]

Howdy all. I have pretty much NO VB/Access coding experience, but am forced to come up with a generic script/process for users with varying database designs and varying levels of expertise to use and integrate into their Dbs.

Following is my script and is launched On_Open event. The script determines someone's desired upload class (test, archive or recent), executes a specific query based on that info, then writes the query results to a text file, tab-delimited. I am trying to use createQueryDef so that the query can be built on the fly with a temporary query - no need to make several permanent queries. As you might notice, the main problem is having to insert TOP 300 in the SQL query, the WHERE changes are quite manageable, the TOP 300 gets me.

Anyway, my diagnostic MsgBox right before the createQueryDef works, but I get stuck in some kinda error/hold after that, that requires ctrl+break to get out of.

Thanks for any insight!!! I am using Access 2000 right now, to ensure as much universal compatibility as possible, but that may be a mistake!

Option Compare Database
 
Private Sub Report_Open(Cancel As Integer)
 
    Dim uploadClass As Integer
    Dim countDates As Integer
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM date_tracking")
 
    If rst.RecordCount = 0 Then
        response = MsgBox("Is this for a Test Upload?", vbYesNoCancel, "Upload Type")
        If response = vbYes Then
            uploadClass = 0
            bp (uploadClass)   'run query and data export
        ElseIf response = vbNo Then
            response = MsgBox("Access will now generate an Archive upload.", vbOKCancel, "Upload Type")
            If response = vbOK Then
                uploadClass = 2
                bp (uploadClass)
            End If
        End If
    Else: response = MsgBox("Access will now generate an upload of new records since last.", vbOKCancel, "Upload Type")
        If response = vbOK Then
            uploadClass = 1
            bp (uploadClass)
        End If
    End If
    Set rst = Nothing
End Sub
 
Sub bp(uploadClass As Integer)
    On Error GoTo bp_Err
 
        Dim date_update As Integer
        Dim fileName As String
        Dim strTop As String
        Dim strWhere As String
        Dim strFields As String
        Dim qryName As String
        Dim strSQL As String
 
        date_update = 1
 
        '********   COPY AND PASTE FROM SQL VIEW OF Query *********
        '----------------------------------------------------------------------------------------------------
 
        strFields = " { copy & paste complex JOIN/ON/FROM query } "
 
        '----------------------------------------------------------------------------------------------------
        '*******************************************************************
 
        If (uploadClass = 0) Then
            strTop = "TOP 300 "
            strWhere = " WHERE table1.date > Date()-1095 "
            date_update = 2
        ElseIf (uploadClass = 1) Then
            strTop = ""
            strWhere = " WHERE (((table1.date)<(SELECT Max([UploadDate]) AS maxDate FROM date_tracking;))) "
        Else
            strTop = ""
            strWhere = " WHERE table1.date > Date()-1095 "
        End If
 
        strSQL = "SELECT " & strTop & strFields & strWhere
        qryName = "bpQuery"
        Set dbs = CurrentDb
        response = MsgBox("I'm about to CreateQueryDef", vbOKOnly, "") ' for diagnostic purposes only
        Set qdf = dbs.CreateQueryDef(qryName, strSQL)
        response = MsgBox("I'm just went through CreateQueryDef", vbOKOnly, "") ' for diagnostic purposes only
        Set qdf = Nothing
 
        fileName = "C:/bp/" & DatePart("m", Date) & DatePart("d", Date) & DatePart("yyyy", Date) & ".txt"
       DoCmd.OutputTo acOutputQuery, qryName, acFormatTXT, fileName, False, ""
'    OR    DoCmd.TransferText acExportDelim, "", qryName, fileName, False, ""       
 
       DoCmd.QueryDefs(qryName).delete
 
bp_Exit:
        If date_update = 1 Then
            Set rst = dbs.OpenRecordset("date_tracking")
                rst.AddNew
                rst![UploadDate] = Date - 1
                rst.Update
                rst.Close
        End If
 
bp_Err:
        date_update = 2
        Resume bp_Exit
End Sub
 

Regards,
oh4real

[oh4real]

BTW, line 65-70 is where the createQueryDef is.

oh4real

[oh4real]

Nevermind. Problems were at least 2-fold:

1) Tools -> References - were not all ticked, as per forum helpers! Don't remember which ones I ticked, so can't test if this was the original problem, but I think #2 is the real reason.

2) The QueryDefs.delet e qryName line had 'db' for database, while it should been 'dbs'. Therefore the query was never deleted - this caused a primary failure of the CreateQueryDef (qryName, strSql) since it couldn't overwrite.

3) The _Err & _Exit had not Exit Sub in them, so got unending loops - once did .addnew 245,000 times ;)

Clear head, great night's sleep and refill on Adderall go a long way...

Sorry to bother,
oh4real