[RESOLVED] Access export to Excel

Currently i have a table in access which is created, data is added (both usng SQL), the table is exported to Excel (using DoCmd.TransferSpreadsheet), then the table is deleted (using SQL).

Presently the output path is hard coded as "C:\" for testing, however if i try to read the export path from an external .ini file the app hangs on the line of code which executes the "SELECT... INTO..." SQL statement.... any ideas?

Chris

Can you post the code that assigns the SQL with an example of the output location.

VB Code:

Public Function exportData(pPath As String, pDataSource As Integer)
On Error GoTo Err_exportData
 
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet
    Dim i, j, tDataSource As Integer
    Dim tPath, tTemp, tSQL As String
    Dim tResult As VbMsgBoxResult
    
    If Not pDataSource = -1 Then
        tDataSource = pDataSource
    Else
        tDataSource = 5
    End If
    
    tSQL = Switch(tDataSource = 0, "SELECT pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction, behaviour.Reason, behaviour.ExclusionLength, teacher.TeacherName, faculty.FacultyName, period.Period INTO TempTable FROM teacher INNER JOIN (Sanctions INNER JOIN (pupils INNER JOIN (period INNER JOIN (faculty INNER JOIN behaviour ON faculty.FacultyID = behaviour.FacutyID) ON period.PeriodID = behaviour.PeriodID) ON pupils.PupilID = behaviour.PupilID) ON Sanctions.SanctionID = behaviour.SanctionID) ON teacher.TeacherID = behaviour.TeacherID WHERE (((pupils.Year)=7)) ORDER BY pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction;", _
                tDataSource = 1, "SELECT pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction, behaviour.Reason, behaviour.ExclusionLength, teacher.TeacherName, faculty.FacultyName, period.Period INTO TempTable FROM teacher INNER JOIN (Sanctions INNER JOIN (pupils INNER JOIN (period INNER JOIN (faculty INNER JOIN behaviour ON faculty.FacultyID = behaviour.FacutyID) ON period.PeriodID = behaviour.PeriodID) ON pupils.PupilID = behaviour.PupilID) ON Sanctions.SanctionID = behaviour.SanctionID) ON teacher.TeacherID = behaviour.TeacherID WHERE (((pupils.Year)=8)) ORDER BY pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction;", _
                tDataSource = 2, "SELECT pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction, behaviour.Reason, behaviour.ExclusionLength, teacher.TeacherName, faculty.FacultyName, period.Period INTO TempTable FROM teacher INNER JOIN (Sanctions INNER JOIN (pupils INNER JOIN (period INNER JOIN (faculty INNER JOIN behaviour ON faculty.FacultyID = behaviour.FacutyID) ON period.PeriodID = behaviour.PeriodID) ON pupils.PupilID = behaviour.PupilID) ON Sanctions.SanctionID = behaviour.SanctionID) ON teacher.TeacherID = behaviour.TeacherID WHERE (((pupils.Year)=9)) ORDER BY pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction;", _
                tDataSource = 3, "SELECT pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction, behaviour.Reason, behaviour.ExclusionLength, teacher.TeacherName, faculty.FacultyName, period.Period INTO TempTable FROM teacher INNER JOIN (Sanctions INNER JOIN (pupils INNER JOIN (period INNER JOIN (faculty INNER JOIN behaviour ON faculty.FacultyID = behaviour.FacutyID) ON period.PeriodID = behaviour.PeriodID) ON pupils.PupilID = behaviour.PupilID) ON Sanctions.SanctionID = behaviour.SanctionID) ON teacher.TeacherID = behaviour.TeacherID WHERE (((pupils.Year)=10)) ORDER BY pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction;", _
                tDataSource = 4, "SELECT pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction, behaviour.Reason, behaviour.ExclusionLength, teacher.TeacherName, faculty.FacultyName, period.Period INTO TempTable FROM teacher INNER JOIN (Sanctions INNER JOIN (pupils INNER JOIN (period INNER JOIN (faculty INNER JOIN behaviour ON faculty.FacultyID = behaviour.FacutyID) ON period.PeriodID = behaviour.PeriodID) ON pupils.PupilID = behaviour.PupilID) ON Sanctions.SanctionID = behaviour.SanctionID) ON teacher.TeacherID = behaviour.TeacherID WHERE (((pupils.Year)=11)) ORDER BY pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction;", _
                tDataSource = 5, "SELECT pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction, behaviour.Reason, behaviour.ExclusionLength, teacher.TeacherName, faculty.FacultyName, period.Period INTO TempTable FROM teacher INNER JOIN (Sanctions INNER JOIN (pupils INNER JOIN (period INNER JOIN (faculty INNER JOIN behaviour ON faculty.FacultyID = behaviour.FacutyID) ON period.PeriodID = behaviour.PeriodID) ON pupils.PupilID = behaviour.PupilID) ON Sanctions.SanctionID = behaviour.SanctionID) ON teacher.TeacherID = behaviour.TeacherID ORDER BY pupils.Surname, pupils.Forename, behaviour.Date, Sanctions.Sanction;")
    
    tPath = pPath
    tTemp = Right(tPath, 1)
    
    If Not tTemp = "\" Then
        If Not tTemp = "/" Then
            tPath = tPath & "\"
        End If
    End If
    
    tTemp = Date
    tPath = tPath & Replace(tTemp, "/", "_")
    
    If Len(Dir(tPath & ".xls")) > 0 Then
        '   We know that the file exists.
        tResult = MsgBox("File Already Exists, would you like to overwrite the existing file?", vbYesNo, "PK:Sanctions")
        If tResult = vbNo Then
            MsgBox "No file created", vbOKOnly
            Exit Function
        Else
            Dim tDeleteFile As String
            tDeleteFile = tPath & ".xls"
            SetAttr tDeleteFile, vbNormal
            Kill tDeleteFile
        End If
    End If
 
    Set oApp = New Excel.Application
    Set oWB = oApp.Workbooks.Add
    
    DoCmd.RunSQL ("CREATE TABLE TempTable (Surname Text(50), Forename Text(50), [Date] DateTime, Sanction Text(50), Reason Text(255), ExclusionLength Long, TeacherName Text(50), FacultyName Text(50), Period Text(50));")
    DoCmd.RunSQL (tSQL)
        
    oWB.Sheets("Sheet1").Cells(1, 1) = ""
    oWB.Close SaveChanges:=True, Filename:=tPath
        
    Set oWB = Nothing
    oApp.Quit
    Set oApp = Nothing
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "TempTable", tPath & ".xls"
    DoCmd.RunSQL ("DROP TABLE TempTable;")

It hangs on the line:

VB Code:

DoCmd.RunSQL ("CREATE TABLE TempTable (Surname Text(50), Forename Text(50), [Date] DateTime, Sanction Text(50), Reason Text(255), ExclusionLength Long, TeacherName Text(50), FacultyName Text(50), Period Text(50));")

First Off the tSQL that you are writing will create the table for you so there is no need for the CREATE TABLE SQL.

There is also no real need for the large switch function, only one of the SQL's in this switch is different from the others (excluding the where clause) so do a simple if statement the write the end of the SQL..

VB Code:

Dim tSQL As String
  tsql = "SELECT c.Surname, c.Forename, f.Date, b.Sanction, f.Reason, " & _
    "f.ExclusionLength, a.TeacherName, e.FacultyName, d.Period INTO " & _
    "TempTable FROM teacher a INNER JOIN (Sanctions b INNER JOIN " & _
    "(pupils c INNER JOIN (period d INNER JOIN (faculty e INNER JOIN " & _
    "behaviour f ON e.FacultyID = f.FacutyID) ON d.PeriodID = f.PeriodID) " & _
    "ON c.PupilID = f.PupilID) ON b.SanctionID = f.SanctionID) ON a." & _
    "TeacherID = f.TeacherID "
  If tDataSource = 5 Then
    tSQL = tSQL & "ORDER BY c.Surname, c.Forename, f.Date, b.Sanction;"
  Else
    tSQL = tSQL & "WHERE c.Year=" & tDataSource + 7 & " ORDER BY " & _
      "c.Surname, c.Forename, f.Date, b.Sanction;"
  End If

This will generate the same query but without the switch function.

Also I'm not sure what is populated in the ppath variable, but the following would probably be better.. and the same result should be obtained

VB Code:

If Right(pPath,1) <> "\" Then 
    tPath = pPath & "\" & Replace(CStr(Date), "/", "_")
  Else
    tpath = pPath & Replace(CStr(Date), "/", "_")
  End If

The error was occuring because the table already existed, on a previous run the program had exited befor the drop statement, so it was hanging on the create sql statement.

Is there any way to check if a table exist aand if so delete it?
I was thinking this might do it:

VB Code:

If CurrentDb.TableDefs(TempTable).Name > "" Then
     DoCmd.RunSQL "DROP TABLE TempTable;"
End If

but that hangs!

Use a simple On Error Resume Next around the drop statement, so if the table doesn't exist it will just skip past..

VB Code:

On Error Resume Next
  Currentdb.Execute "DROP TABLE TempTable"
  On Error Goto 0