UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Variable In Delete Statement, Access 2016    
 
   
LagoDavid
post Dec 27 2017, 11:42 PM
Post#1



Posts: 446
Joined: 12-October 03
From: Texas


I have a temporary table I am importing into from a spreadsheet. Each time I execute the import steps, one of the early steps is to delete any existing records from the temporary table. My statement is:
CurrentDb.Execute "DELETE * FROM tmptblLIMSImport;", dbFailOnError

This works fine. However, I would like to use a variable in the DELETE statement instead of hard-coding the temporary table name. When I try to use a variable name instead of hard coding, Access tells me it cannot find the table or query to delete from. My variable name is strTemporaryTableName and it has been properly declared. And as well, I added a Debug.Print statement to see what was held in the strTemporaryTableName, and that variable does in fact hold the correct table name.

Is there some special syntax I need to use in order to use a variable in the DELETE statement? The statement that is failing is:
CurrentDb.Execute "DELETE * FROM strTemporaryTableName;", dbFailOnError



Go to the top of the page
 
RJD
post Dec 28 2017, 05:13 AM
Post#2


UtterAccess VIP
Posts: 8,925
Joined: 25-October 10
From: Gulf South USA


QUOTE
Is there some special syntax I need to use in order to use a variable in the DELETE statement? The statement that is failing is:
CurrentDb.Execute "DELETE * FROM strTemporaryTableName;", dbFailOnError

When you embed the variable within the quotes, Access just looks literally for the table named strTemporaryTableName, not for the table named in the variable. You have to put the variable outside the quotes, something like ...

CurrentDb.Execute "DELETE * FROM " & strTemporaryTableName & ";", dbFailOnError

Or build the query command string first and then call the string in the Execute, something like ...

Dim strSQL as String
strSQL = "DELETE * FROM " & strTemporaryTableName & ";"
CurrentDb.Execute strSQL, dbFailOnError

HTH
Joe
Go to the top of the page
 
LagoDavid
post Dec 28 2017, 10:20 AM
Post#3



Posts: 446
Joined: 12-October 03
From: Texas


thank you so much; that makes complete sense
Go to the top of the page
 
RJD
post Dec 28 2017, 10:34 AM
Post#4


UtterAccess VIP
Posts: 8,925
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Hope that is working for you now.

Good luck with your project.

Regards,
Joe
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 11:01 AM