Passing table names to query/code 1

[GLENEE]

I have a number of linked tables in my database. The user needs to be able to delete the contents of any of these tables when required. Is it possible to pass the name of a selected table to SQL? I've tried the following but it does not work:

Table name is selected from a list box and stored as strPeriod

ex:
[ strPeriod = "April 2006" 'name of table selected from list

strSQL = "Delete strPeriod.* FROM strPeriod"
DoCmd.RunSQL (strSQL) ]

Thanks

 

[RoyVidar]

You need to feed it the value of the variable, not the name, and also ensure it might handle special characters (including space)

[tt]strSQL = "Delete FROM [" & strPeriod & "]"
'DoCmd.RunSQL (strSQL)
CurrentDB.Execute strSQL, dbFailOnError[/tt]

Roy-Vidar

 

[Remou]

It would be:

[tt]strSQL = "Delete * FROM [" & strPeriod & "]"[/tt]

 

[jrbarnett]

If your table has a space in its name, then you will need to enclose the table name in square brackets, thus:

strPeriod = "[April 2006]" 'name

Additionally, to delete the contents of a table, just use

DELETE FROM tablename

so use:

strSQL = "DELETE FROM " & strPeriod
DoCmd.RunSQL strSQL

There is no harm in putting square brackets around table names without a space in their name, but if possible, removing the space from the table name is worth considering because it can cause other issues elsewhere.

John

 

[Remou]

Small typo, I think, RoyVidar, you seem to have missed the *.

 

[Remou]

I see I am wrong, no * necessary. There is always something to learn here.

 

[GLENEE]

Thanks, that's solved it.

 

[RoyVidar]

Glad it worked!

Lots of ways to skin a cat. Using "DELETE * FROM..." is what the wizard gives us, it's what's found in the Jet SQL help file (JETSQL40.chm somewhere on a harddisk near you) and is valid, documented and working SQL. Alternatively "DELETE tablename.* FROM ...".

Other documentations/tutorials, as for instance

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

uses a syntax without *, that's perhaps also more common on other platforms?

Roy-Vidar

 

[PHV]

that's perhaps also more common on other platforms?
JetSQL is the only one I'm aware off that use the DELETE * syntax ...