October 26, 2005 at 8:13 am
I have created a form that allows people to select fields from a view, creates a temporty table containing all of the records from that view with only the fields they select, and then exports all of the data from that table into an Excel spreadsheet.
It works fine about 20% of the time, but most of the time it gives an error saying: "can't find the object tblTempExport"
Does anybody see anything I should do differently in this code?
I would greatly apprciate any help anyone could offer.
Thanks!
Private Sub exportExcelButton_Click()
' set up a connection
Dim conn As New adoDb.Connection
Dim rst As adoDb.Recordset
conn.Open CurrentProject.Connection
' Drop the table if it exists
Dim dropSQL As String
dropSQL = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME ='tblTempExport' AND TABLE_SCHEMA = 'dbo'"
dropSQL = dropSQL & ") DROP TABLE tblTempExport"
conn.Execute (dropSQL)
' create a new table with the appropriate fields.
Dim createTableSQL As String
Dim selectSQL As String
Dim insertSQL As String
createTableSQL = "CREATE TABLE tblTempExport ( "
insertSQL = "INSERT INTO tblTempExport ( "
selectSQL = "SELECT "
Dim tempString As String
For Index = 0 To Me.ExportList.ListCount - 1
If Index > 0 Then
createTableSQL = createTableSQL & ", "
selectSQL = selectSQL & ", "
insertSQL = insertSQL & ", "
End If
createTableSQL = createTableSQL & Me.ExportList.ItemData(Index) & " varchar(" & 255 & ")"
selectSQL = selectSQL & "RTRIM(" & Me.ExportList.ItemData(Index) & ")"
insertSQL = insertSQL & Me.ExportList.ItemData(Index)
Next Index
createTableSQL = createTableSQL & ")"
selectSQL = selectSQL & " FROM dbo.ChaplainAll()"
conn.Execute (createTableSQL)
'Finally insert the needed records into the table
If Not (IsNull(Me.OpenArgs)) Then
selectSQL = selectSQL & Me.OpenArgs
End If
insertSQL = insertSQL & ") "
insertSQL = insertSQL & selectSQL
conn.Execute (insertSQL)
Dim filename As String
filename = "c:\" & Me.filename & ".xls"
'We need to delete the file before we export to it if it already exists.
Dim fileTest As String
fileTest = Dir(filename)
If fileTest <> "" Then
' the file exists, delete it
Kill filename
End If
'Refresh the database window so that the application can see the newly created table.
RefreshDatabaseWindow
'exportToExcel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTempExport", filename, False
DoCmd.OpenForm "frmMessage", acNormal, , , acFormReadOnly, acWindowNormal, "The data has been exported to" & Chr$(13) & Chr$(10) & "C:\" & Me.filename & ".xls."
End Sub
The Redneck DBA
October 26, 2005 at 9:08 am
Why not just eliminate the temporary table, and export from a query?
Private Sub exportExcelButton_Click()
Dim selectSQL As String
selectSQL = "SELECT "
Dim tempString As String
For Index = 0 To Me.ExportList.ListCount - 1
If Index > 0 Then selectSQL = selectSQL & ", "
selectSQL = selectSQL & "RTRIM(" & Me.ExportList.ItemData(Index) & ")"
Next Index
selectSQL = selectSQL & " FROM dbo.ChaplainAll()"
Dim filename As String filename = "c:\" & Me.filename & ".xls"
'We need to delete the file before we export to it if it already exists. Dim fileTest As String fileTest = Dir(filename) If fileTest <> "" Then ' the file exists, delete it Kill filename End If
'exportToExcel DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, selectSQL, filename
DoCmd.OpenForm "frmMessage", acNormal, , , acFormReadOnly, acWindowNormal, "The data has been exported to" & Chr$(13) & Chr$(10) & "C:\" & Me.filename & ".xls."
End Sub
Mark
October 26, 2005 at 9:15 am
Thanks Mark,
That makes sense to me also, but when I tried that before I got an error that said "The table name you entered doesn't follow Chaplaincy object-naming rules".
I also tried creating a storedProcedure that just selected everything from the temp table and using that instead of selectSQL or tblTempExport but it still misbehaves. I've also played arround with sticking dbo and various other stuff in front of the table/stored procedure names to try to help it find them, but it doesn't seem to matter.
I probably should have mentioned that this is an .adp not an .mdb. That may make a difference, I don't know.
The Redneck DBA
October 27, 2005 at 11:57 am
I'm not sure what the difference is, but I switched the line:
DoCmd.TransferSpreadsheet acExport, SpreadsheetTypeExcel9, "dbo.tblTempExport", filename
to:
DoCmd.OutputTo acOutputTable, "dbo.tblTempExport", acFormatXLS, filename, False
and it works now.
Don't know why, but it works so I'm going to leave it alone.
The Redneck DBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply