Question Delay in executing Create Table SQL statement

[bifteki]

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 fso

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)"

DoCmd.RunSQL sql_str

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 " &
txt_sql_conditions.Value

'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


DoCmd.RunSQL sql_str

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
(Now)), (CStr(Day(Now)))))
filePath = "P:\test_folder\" & fileName & "_eortazontes"

Set fso = CreateObject("Scripting.FileSystemObject")

fileNumber = 0

Do
fileNumber = fileNumber + 1
tmpFilePath = filePath & "_" & fileNumber & ".xls"

If Not fso.FileExists(tmpFilePath) Then
fileNotExists = True
End If
Loop While fileNotExists = False

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo.
tmptbl_export", tmpFilePath, True


DoCmd.RunSQL "DROP TABLE tmptbl_export"
End Sub


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 itself.

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?

 

[wiklendt]

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)"

DoCmd.RunSQL sql_str

might just be me, but it seems that you are commanding access to find records from the temp table BEFORE you create it.

then you created a breakpoint BEFORE the temporary table was deleted but AFTER it was created, so when you refresh (F5), it has no problems running because the table form the first attempt is still there and not deleted.

 

[wiklendt]

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)"

DoCmd.RunSQL sql_str

might just be me, but it seems that you are commanding access to find records from the temp table BEFORE you create it.

then you created a breakpoint BEFORE the temporary table was deleted but AFTER it was created, so when you refresh (F5), it has no problems running because the table form the first attempt is still there and not deleted.