Deleting Temp Tables (1 Viewer)

M

Mari111

Registered User.
Local time
Today, 14:10
Joined
Jan 31, 2018
Messages
37
Hello,

I have an import data button which creates a temp table in MS Access to import new data into and then runs an append query to add the data into a linked SQL Server table (the back end).

I then need some code to delete the temp table, because it is re-created each time a user clicks the import button. I'm getting a run time error 7874 though.

It's recognising the DoCmd.Close command but not recognising the DoCmd.DeleteObject command line. It's throwing a run time error 7874 MS Access can't find object 'TblTemp_Table' even though the table definitely still exists, I've just closed it. My code is:

Code: 
Private Sub cmdImportFilterResults_Click()
Dim sExcelFile As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant

 
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections in dialog box
.AllowMultiSelect = False
'Set the title of the dialog box
.Title = "Please select a file"
'Show the dialog box. If the .Show method returns True, the user picked at least one file.
If .Show = True Then
sExcelFile = .SelectedItems(1)

'' import the file to the linked sql server table
    
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Table_FilterResult", sExcelFile, True
DoCmd.OpenQuery ("qryAppendFilterResult")
DoCmd.Close acTable, "Table_FilterResult"
DoCmd.DeleteObject , "Table_FilterResult"
End If
End With
End Sub

Why is it throwing this run-time error when the temp table still exists? It is definitely named correctly.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:10
Joined
May 7, 2009
Messages
19,242
What is qryAppendFilterResult does it uses the temp table coz you got it open while deleting the table.
 
M

Mari111

Registered User.
Local time
Today, 14:10
Joined
Jan 31, 2018
Messages
37
Oh ok, it uses the temp table and appends the data in it to the linked sql server table....how do I close the qry in vba code? DoCmd.CloseQuery?
 
Minty

Minty

AWF VIP
Local time
Today, 22:10
Joined
Jul 26, 2013
Messages
10,371
I'm not sure why you are closing the table, it isn't opened by anything as far as I can see?

The append qry wouldn't be open if it really is an append query, it would have completed it's action by then.

And an Append query wouldn't create a new table, it only adds records to it?

So something in your description / process flow is wrong somewhere, based on what you have told us?
 
M

Mari111

Registered User.
Local time
Today, 14:10
Joined
Jan 31, 2018
Messages
37
Hi,

Found the code for closing the query and it works ok. Thanks arnelgp. Question solved.

Thank you all.
 
M

Mark_

Longboard on the internet
Local time
Today, 14:10
Joined
Sep 12, 2017
Messages
2,111
If I am understanding correctly,
Code: 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Table_FilterResult", sExcelFile, True
moves data from your spreadsheet to "Table_FilterResult", so this opens and closes the table as needed.

Code: 
DoCmd.OpenQuery ("qryAppendFilterResult")
opens and closes "Table_FilterResult" as it itself needs while adding all records from your temp table to the SQL table.

Code: 
DoCmd.Close acTable, "Table_FilterResult"
is not needed as you have not opened the table yourself.

Code: 
DoCmd.DeleteObject , "Table_FilterResult"
will try to DELETE the TABLE.

My guess is that you do not want to delete the table itself, just all records in the table. You can do this by running a DELETE QUERY instead.

Your coding does beg the question, if you are not going to validate the data coming in from excel, why add it to a temp table instead of adding it to the SQL table itself? Something like
Code: 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MySQLTable", sExcelFile, True
would seem to be much easier. Personally I'd still review and validate the data prior to adding it. It can be un-fun when you add the wrong values because you didn't check first.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:10
Joined
Feb 19, 2002
Messages
43,275
1. Creating temp tables causes bloat.
2. In this case, you have no need for temp tables. Simply link to the text file and use an append query to take it from the ilinked text file or spreadsheet directly to the SQL Server table. Saving it in Access is not necessary.
 
You must log in or register to reply here.

Similar threads

P
Solved Access vba Manage an Attachment field with vba
Replies
8
Views
441
Pat Hartman
P
N
Solved Mass Importing Txt Files
Replies
14
Views
397
ebs17
E
S
Solved Import .CSV VBA Run time Error 3011
Replies
4
Views
339
sathsaj
S
G
Solved VBA COMPILATION ERROR
Replies
9
Views
915
georg0307
G
H
Random Table Data Deletion
2
Replies
36
Views
1,225
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

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