I have an Access ADP project connected to an SQL Server 2005 DB.
In VBA, I want to export a recordset given from a previous query to Excel
using TransferSpreadsheet. As this command can only accept a table as its
parameter, I first create a new table -to use as a temporary one-, then I
insert the specific rows in it, export it and, finally, delete it from the DB.
The process is triggered through a command button.
However, this doesn't work. I get the message that it can't find the table
'tmptbl_export'. The reason for this is a delay in creating the table. If I
press the button while I have the 'All Access objects' pane open, I can see
that I get the message and -after I press OK- then the table is created!
I give the code below:
Private Sub cmd_export_to_excel_Click()
Dim sql_str As String
Dim rst As Recordset
Dim tableExists As Boolean
Dim errNum As Integer
Dim fileName As String
Dim filePath As String
Dim fileNotExists As Boolean
Dim fileNumber As Integer
Dim tmpFilePath As String
DoCmd.RunSQL "IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[tmptbl_export]') AND type in (N'U')) " & _
"DROP TABLE [dbo].[tmptbl_export]"
sql_str = "CREATE TABLE [dbo].[tmptbl_export] (" & _
"[fld_company_name] [nvarchar](50) NULL, " & _
"[fld_person_name] [varchar](50) NULL, " & _
"[fld_person_surname] [varchar](50) NULL)"
sql_str = "INSERT INTO dbo.[tmptbl_export] (fld_company_name, fld_person_name,
fld_person_surname) SELECT dbo.tbl_Companies.fld_company_name, dbo.
tbl_Persons.fld_person_name, dbo.tbl_Persons.fld_person_surname " &
'sql_str = "SELECT dbo.tbl_Companies.fld_company_name, dbo.tbl_Persons.
fld_person_name, dbo.tbl_Persons.fld_person_surname " & _
"INTO dbo.[tmptbl_export] " & txt_sql_conditions.Value
fileName = CStr(Year(Now)) & (IIf(Len(CStr(Month(Now))) = 1, "0" & CStr(Month
(Now)), (CStr(Month(Now))))) & (IIf(Len(CStr(Day(Now))) = 1, "0" & CStr(Day
filePath = "P:test_folder" & fileName & "_eortazontes"
Set fso = CreateObject("Scripting.FileSystemObject")
fileNumber = 0
fileNumber = fileNumber + 1
tmpFilePath = filePath & "_" & fileNumber & ".xls"
If Not fso.FileExists(tmpFilePath) Then
fileNotExists = True
Loop While fileNotExists = False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo.
tmptbl_export", tmpFilePath, True
DoCmd.RunSQL "DROP TABLE tmptbl_export"
If I insert a breakpoint right after the insertion of the rows the table gets created. If then I press F5 it runs fine and I
get the file with the expected results. That's how I figured it's a matter of this delay and not a matter of the statement
I have also tried doing the creation and insertion in the table with SELECT
INTO (the commented line) but it didn't work either. I also tried to do this
same process with docmd.outputTo but I don't get the expected result, as
docmd.outputTo doesn't support Unicode encoding and I -mostly- have Unicode
in my results.
Does anyone know of any way to overcome this?