Question Delay in executing Create Table SQL statement

B

bifteki

Registered User.
Local time
Today, 23:29
Joined
Oct 24, 2008
Messages
23
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?
 
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_companay_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?
 
Posting 3 times gets you nowhere here.
 
I didn't read all of your post, but you can use TransferSpreadsheet to export queries - I have it working in my Database. Just place the query name where you would place the table name.
 
I am sorry for the multiple posts but we had a problem with the internet connection yesterday and I didn't even think I had managed to post one.

TransferSpreadSheet works for Access queries. I have a connection with an SQL Server DB and I have views and stored procedures. This means that TransferSpreadSheet doesn't even see them.
 
Ok, perhaps you could use the Sleep function to create a delay inbetween creating the table and appending the data to allow the table to be created before attempting to insert the data?
 
I tried putting it to sleep for 60 seconds but it still didn't work.
I added the following lines
DoCmd.SelectObject acTable, , True
Application.RefreshDatabaseWindow
after the creation of the table, but still no good.

The table is always created right after I end the code, or break it through a breakpoint.

Any other ideas on this?
 
Thought the Sleep might have worked, oh well.

How about we don't create a new table each time. Rather than deleting the table and creating a new one every time, how about we append the data to the existing table and then delete the data at the end of the code?

I know this means you have a table hanging around that pretty much has no use most of the time but its an idea?

Other than that, I'm fresh out for now.
 
DoEvents doesn't work either.

Anyway I decided to choose a conciliatory solution: I'll create a new table
whenever the form is opened and drop it whenever it's closed.

Thank you both for your help. :)
 
Good idea. Would have been nicer to create it on the click of the button, but at least the table only exists for as long as the user is on the form.

One thing, would there ever be an instance where multiple users could be in the form at the same time, i.e. User1 opens the form and table is created. User2 then opens the form (User1 is still in the form) - wouldn't this return an error for User2 when it attempts to create the table that already exists?
 
The truth is that many users may access the database at once.
But I've already thought about it.
I perform a check before I create the table, using the following "IF NOT EXISTS...." SQL statement:

sql_str = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmptbl_export]') AND type in (N'U')) " & _
"CREATE TABLE [dbo].[tmptbl_export] (" & _
"[fld_company_name] [nvarchar](50) NULL, " & _
"[fld_person_name] [varchar](50) NULL, " & _
"[fld_person_surname] [varchar](50) NULL)"

CurrentProject.Connection.Execute sql_str

Right after the table is used it is truncated and when the form closes it is dropped.
The only problem will be if two users open the form, one of them closes it and the other one then tries to do an export. The table will already have been dropped. But anyway, this project is for use by my company and I am actually the one who will be using this feature the most, so... *shrugs*
 
You must log in or register to reply here.

Similar threads

Z
SQL table linked in MSAccess, insert yes, update/delete no
2
Replies
24
Views
2,703
Zakraket
Z
R
filter a certain type only
Replies
9
Views
260
Pat Hartman
P
B
How to run the output of Access vba SQL statement to a temp table
Replies
5
Views
514
Pat Hartman
P
T
acImport creates link?
Replies
5
Views
369
GPGeorge
GPGeorge
D
Solved Problem with dates in sql
Replies
7
Views
402
disgracept
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom