Table name as a variable in sql string

[John Sh]

I am slowly coming to grips with SQL but this is bugging me.
I have resorted to using the following when using a variable in the from clause.

"FROM [" & sTable & "] as A " & _

Which seems a bit clunky.
Sometimes, but not always this seems to work

"FROM sTable as A " & _

and other times it is this

"FROM [sTable] as A " & _

The string "sTable" can be dimmed in the sub / function, it can be passed as a parameter or extracted from form.recordsource.

What is the syntactically correct way to do this?

 

[Edgar_]

At the end of the day, the only thing you should worry about is that your string is formatted correctly. In Access, when you have names with special symbols, such as a space, accented vowels, or others, you must use brackets. If your names do not contain special symbols, you do not need to add brackets.

So, if your names have special characters, do this:
"FROM [" & tableName & "] As A " & _

If your names do not have special characters, this is enough:
"FROM " & tableName & " As A " & _

The brackets are used to Evaluate the expression.

 

[The_Doc_Man]

You need to understand WHY you usually need the ...[" & sTable & "]... syntax.

Access has TWO contexts. When you are building an SQL statement using VBA and concatenation, you are in the Access Graphic User Interface (GUI) environment. VBA variables exist in this environment.

When you build that SQL string, you will eventually submit it for execution using either DoCmd.RunSQL or CurrentDB.OpenRecordset or CurrentDB.Execute, each of which takes the string and sends it to the database engine, usually ACE unless you have an SQL Server or MySQL or other external database engine. In ANY of these cases, the string is sent as such to the DB engine for execution - but this engine exists as a child process of MSACCESS.EXE and is in a separate memory segment. It DOES NOT have any visibility of VBA variables. They are still in the GUI memory space and totally invisible.

Unless you have an actual table named sTable, the second and third methods you listed should fail miserably because those literal strings do not involve string concatenation that would bring the content of the variable into the string. The rule is that generally, you must submit a completed SQL string for execution.

The APPARENT (but not actual) exception would be that using ... FROM [" & sTable & "]... in the same line as any of the SQL execution methods will FIRST run in the GUI environment where the concatenation will occur to complete the string BEFORE passing it to the SQL environment. That is to say, two things occur in sequence when the literal string is part of a line that does an SQL action once all the parsing resolution is complete.

 

[The_Doc_Man]

After seeing Edgar_'s comment, I realize there is a second way to read your question.

You use ... FROM <literal table name> WHERE ... (without [brackets] around the name) when the table name does not contain special characters or spaces. If there is anything special in the table name then you will need to bracket it. It is considered unwise to include spaces in table names because Access allows it but if you ever have to step to a larger DB engine, that engine might not allow the spaces.

Not to mention that if you keep your table names really simple so as to avoid the brackets, you do less typing.

 

[Edgar_]

In order to send square brackets to hell, it is recommended to use ONLY the following for anything you have to name in the application:
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghjiklmnopqrstuvwxyz0123456789_

 

[Edgar_]

After seeing Edgar_'s comment, I realize there is a second way to read your question.

You use ... FROM <literal table name> WHERE ... (without [brackets] around the name) when the table name does not contain special characters or spaces. If there is anything special in the table name then you will need to bracket it. It is considered unwise to include spaces in table names because Access allows it but if you ever have to step to a larger DB engine, that engine might not allow the spaces.

Not to mention that if you keep your table names really simple so as to avoid the brackets, you do less typing.

Add a third way, he might be using parameters, replace and what not.

Better wait for confirmation.

 

[John Sh]

Thank you both.
That clears things up for me. I only ever use standard characters in my table names and have removed all of the spaces.
John

 

[CJ_London]

I only ever use standard characters in my table names

Per post #5 - also applies to field names - although if the field name is a reserved word such as Date, Name, Desc, Description etc you may still need to use the square brackets to avoid confusion. One of the reasons for using meaningful names

And you don't need to specify the table name when a fieldname is unique to the query

SELECT table1.Field1 FROM Table1

can simply be

SELECT Field1 FROM Table1

A reason for ensuring field names are unique across the application, not just the table

 

[John Sh]

Per post #5 - also applies to field names - although if the field name is a reserved word such as Date, Name, Desc, Description etc you may still need to use the square brackets to avoid confusion. One of the reasons for using meaningful names

And you don't need to specify the table name when a fieldname is unique to the query

SELECT table1.Field1 FROM Table1

can simply be

SELECT Field1 FROM Table1

A reason for ensuring field names are unique across the application, not just the table

I find using the alternate "A." is the best of both worlds