DSN Code stopped working

[D Willett]

Hi. Just wondering if any memebers could browse over my code which has stopped working.
I've changed the headings section this morning and also the section of RS1 fields. A file is created but I get a file locked dialog from excel and the file contains no data?

Code: Select all

Private Sub cmdDSN_Click()
    On Error GoTo ErrHandler

    'Check if DSN exists
    If DSNExists("Autoflow") Then

        ' get a free file number
        FileNum = FreeFile

        'Check if csv exists
        If Dir("L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv") <> "" Then
        'If Dir("L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & Me.txtEst & ".csv") <> "" Then
            'Delete the csv
            Kill ("L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv")
            'Kill ("L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & Me.txtEst & ".csv")
        End If

        'Create a new csv
        Open "L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv" For Append As #FileNum
        'Open "L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & Me.txtEst & ".csv" For Append As #FileNum

        'create headings in the csv
        Write #FileNum, "JobID", "Reg", "Make", "Model", "Insurer", _
              "ECD", "Completed", "Estimated", _
              "Authorised", "Progress", "T_Loss", "Diary", "On_Site", "Handed_Over"

        'The name and place of the database
        ConnectSource = "C:\MM-Utilitities\AF-CSV.mdb"

        'Connect to the database
        conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                   & "Data Source=" & ConnectSource

        'SQL to state which table to use
        'SQLQuery = "SELECT * from [qryExport]"
        SQLQuery = "SELECT * from [qryExport] WHERE JobID=" & Me.txtEst
        rs1.CursorLocation = adUseClient
        rs1.CursorType = adOpenStatic
        rs1.LockType = adLockReadOnly

        'open the table
        rs1.Open SQLQuery, conn1

        'ensure it is not an empty table before trying to move to the first record
        If Not (rs1.BOF And rs1.EOF) Then rs1.MoveFirst

        Do While rs1.EOF <> True

            'here is where Num comes into play
            'Num = rs1.Fields("Number")

            'I have used Format for the Date field, otherwise you wind up with # signs in your csv

            Write #FileNum, rs1.Fields("JobID"), rs1.Fields("Reg"), rs1.Fields("Make"), rs1.Fields("Model"), _
                  rs1.Fields("Insurer"), _
                  Format(rs1.Fields("ECD"), "dd/mm/yy"), Format(rs1.Fields("Completed"), "dd/mm/yy"), _
                  Format(rs1.Fields("Estimated"), "dd/mm/yy"), Format(rs1.Fields("Authorised"), "dd/mm/yy"), _
                  Format(rs1.Fields("Progress"), "dd/mm/yy"), _
                  Format(rs1.Fields("T_Loss"), "dd/mm/yy"), Format(rs1.Fields("Diary"), "dd/mm/yy"), _
                  Format(rs1.Fields("On_Site"), "dd/mm/yy"), Format(rs1.Fields("Handed_Over"), "dd/mm/yy")

            'Write #FileNum, Num, rs1.Fields("FirstName"), rs1.Fields("Surname"), Format(rs1.Fields("Date"), "dd/mm/yy")
            rs1.MoveNext
        Loop
        rs1.Close
        conn1.Close
        Close #FileNum
        'MsgBox "All Done"
    Else
        'MsgBox "DSN Does Not Exist"
    End If
ErrHandler:
    Select Case Err.Number
        Case 71, 76
        MsgBox Err.Number
        Case Else
        MsgBox Err.Number
        
        

    End Select
    
    'Unload Me

End Sub

[HansV]

Does it make a difference if you open the file for output instead of for append?
You forgot to include Exit Sub just above the error handler.

Code: Select all

Private Sub cmdDSN_Click()
    On Error GoTo ErrHandler

    'Check if DSN exists
    If DSNExists("Autoflow") Then

        ' get a free file number
        FileNum = FreeFile

        'Create a new csv
        Open "L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv" For Output As #FileNum
        'Open "L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & Me.txtEst & ".csv" For Output As #FileNum

        'create headings in the csv
        Write #FileNum, "JobID", "Reg", "Make", "Model", "Insurer", _
              "ECD", "Completed", "Estimated", _
              "Authorised", "Progress", "T_Loss", "Diary", "On_Site", "Handed_Over"

        'The name and place of the database
        ConnectSource = "C:\MM-Utilitities\AF-CSV.mdb"

        'Connect to the database
        conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                   & "Data Source=" & ConnectSource

        'SQL to state which table to use
        SQLQuery = "SELECT * from qryExport WHERE JobID=" & Me.txtEst
        rs1.CursorLocation = adUseClient
        rs1.CursorType = adOpenStatic
        rs1.LockType = adLockReadOnly

        'open the table
        rs1.Open SQLQuery, conn1

        'ensure it is not an empty table before trying to move to the first record
        If Not (rs1.BOF And rs1.EOF) Then rs1.MoveFirst

        Do While rs1.EOF <> True
            'I have used Format for the Date field, otherwise you wind up with # signs in your csv
            Write #FileNum, rs1.Fields("JobID"), rs1.Fields("Reg"), rs1.Fields("Make"), rs1.Fields("Model"), _
                  rs1.Fields("Insurer"), _
                  Format(rs1.Fields("ECD"), "dd/mm/yy"), Format(rs1.Fields("Completed"), "dd/mm/yy"), _
                  Format(rs1.Fields("Estimated"), "dd/mm/yy"), Format(rs1.Fields("Authorised"), "dd/mm/yy"), _
                  Format(rs1.Fields("Progress"), "dd/mm/yy"), _
                  Format(rs1.Fields("T_Loss"), "dd/mm/yy"), Format(rs1.Fields("Diary"), "dd/mm/yy"), _
                  Format(rs1.Fields("On_Site"), "dd/mm/yy"), Format(rs1.Fields("Handed_Over"), "dd/mm/yy")

            rs1.MoveNext
        Loop

        rs1.Close
        conn1.Close
        Close #FileNum
        'MsgBox "All Done"
    Else
        'MsgBox "DSN Does Not Exist"
    End If
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case 71, 76
            MsgBox Err.Number
        Case Else
            MsgBox Err.Number
    End Select
    'Unload Me
End Sub

[D Willett]

No difference Hans.
The error returned is:

-2147467259

Does this mean anything to you?

[HansV]

Please comment out the line

On Error GoTo ErrHandler

temporarily. Which line is highlighted if you click Debug in the error message?

[D Willett]

The line:
ConnectSource = "C:\MM-Utilitities\AF-CSV.mdb"

Utilities spelt wrong !!!
I've gone back to the original code. I seem to remember we deleted the file for a purpose. Do I still need to add the Exit Sub?

Code: Select all

Private Sub cmdDSN_Click()
    On Error GoTo ErrHandler

    'Check if DSN exists
    If DSNExists("Autoflow") Then

        ' get a free file number
        FileNum = FreeFile

        'Check if csv exists
        If Dir("L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv") <> "" Then
            Kill ("L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv")
            
        End If

        'Create a new csv
        Open "L:\mmpdf\ConsoleFiles\" & Me.txtEst & "\" & "cdata" & ".csv" For Append As #FileNum
        'create headings in the csv
        Write #FileNum, "JobID", "Reg", "Make", "Model", "Insurer", _
              "ECD", "Completed", "Estimated", _
              "Authorised", "Progress", "T_Loss", "Diary", "On_Site", "Handed_Over"

        'The name and place of the database
        ConnectSource = "C:\MM-Utilities\AF-CSV.mdb"

        'Connect to the database
        conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                   & "Data Source=" & ConnectSource

        'SQL to state which table to use
        SQLQuery = "SELECT * from [qryExport] WHERE JobID=" & Me.txtEst
        rs1.CursorLocation = adUseClient
        rs1.CursorType = adOpenStatic
        rs1.LockType = adLockReadOnly

        'open the table
        rs1.Open SQLQuery, conn1

        'ensure it is not an empty table before trying to move to the first record
        If Not (rs1.BOF And rs1.EOF) Then rs1.MoveFirst

        Do While rs1.EOF <> True

            'here is where Num comes into play
            'Num = rs1.Fields("Number")

            'I have used Format for the Date field, otherwise you wind up with # signs in your csv

            Write #FileNum, rs1.Fields("JobID"), rs1.Fields("Reg"), rs1.Fields("Make"), rs1.Fields("Model"), _
                  rs1.Fields("Insurer"), _
                  Format(rs1.Fields("ECD"), "dd/mm/yy"), Format(rs1.Fields("Completed"), "dd/mm/yy"), _
                  Format(rs1.Fields("Estimated"), "dd/mm/yy"), Format(rs1.Fields("Authorised"), "dd/mm/yy"), _
                  Format(rs1.Fields("Progress"), "dd/mm/yy"), _
                  Format(rs1.Fields("T_Loss"), "dd/mm/yy"), Format(rs1.Fields("Diary"), "dd/mm/yy"), _
                  Format(rs1.Fields("On_Site"), "dd/mm/yy"), Format(rs1.Fields("Handed_Over"), "dd/mm/yy")

           rs1.MoveNext
        Loop
        rs1.Close
        conn1.Close
        Close #FileNum
        'MsgBox "All Done"
    Else
        'MsgBox "DSN Does Not Exist"
    End If
ErrHandler:
    Select Case Err.Number
        Case 71, 76
        MsgBox Err.Number
        Case Else
        MsgBox Err.Number

    End Select


End Sub

[HansV]

If you open the .csv file for Append, VBA will add lines to the file if it already exists. Since you want to create a new file, you have to delete the existing one first.
If you open the file for Output, it will automatically delete an already existing file with the same name, then create a new one, so there is no need to delete the existing one first.

You still need Exit Sub above ErrHandler. Otherwise, the code will continue with the ErrHandler section even if no error has occurred, and you'd get an error message showing a 0 (if no error has occurred, Err.Number will be 0).

[D Willett]

Ok, point taken, tested and working.
Thanks once again Hans.