SQL Query in VBA

[Guest]

Hi

I have been given a code to modify. It picks data from Access database by selcting different checkboxes in Excel. I have encountered this SQL Query in tha
"SELECT DISTINCT " + CStr(field) + "" & Chr(13) & "" & Chr(10) & "FROM `" + CStr(datab) + "`." + CStr(table_name) + " " + CStr(table_name) + "
Now I am confused why all these Chr() have been used.I read in VB help that Chr(13) and CHr(10) convert to linefeed/carriage return in Windows, so why are they being used

Secondly why is table_name is twice in the quer
Wouldn't this Query be fine
"SELECT DISTINCT " + CStr(field) + "FROM `" + CStr(datab) + "`." + CStr(table_name)

Here datab is the databse name, field is in the form table name.field name . I guess this makes even Cstr(table_name) redundant, not too sure though

Please help
thnks

 

[Tom Ogilvy]

Select Distinct Name from `something`.`datatable` A
Where A.Qty > 3

so you create an alias for the datatable. In this case, they are using the
table_name as the alias. Since they are using variables, it appears they
have gone too literal in reproducing a query produced in the query wizard I
would think. The carriage return linefeed represents the same mentality.
It is in the copied query, so they reproduced it, but I don't believe they
are necessary.

--
Regards,
Tom Ogilvy

Hi,

I have been given a code to modify. It picks data from Access database by

selcting different checkboxes in Excel. I have encountered this SQL Query in
that

"SELECT DISTINCT " + CStr(field) + "" & Chr(13) & "" & Chr(10) & "FROM `"

+ CStr(datab) + "`." + CStr(table_name) + " " + CStr(table_name) + ""

Now I am confused why all these Chr() have been used.I read in VB help

that Chr(13) and CHr(10) convert to linefeed/carriage return in Windows, so
why are they being used?

Secondly why is table_name is twice in the query
Wouldn't this Query be fine?
"SELECT DISTINCT " + CStr(field) + "FROM `" + CStr(datab) + "`." + CStr(table_name)

Here datab is the databse name, field is in the form table name.field name

.. I guess this makes even Cstr(table_name) redundant, not too sure though.

 

[Guest]

I find the piecemeal constructing of an SQL statement quite unreadable especially when the need for doubling embedded double quotes arises. I prefer an approach such as this

SQL = "SELECT * FROM # WHERE DOBIRTH>'@' " ' The whole SQL is readable as one strin
SQL = Replace(SQL,"#",CSTR(table_name)) ' Run time substitutions are made one at a time
SQL = Replate(SQL,'@',date) ' There is no need to wrestle with embedded single quotes eithe

The use of carriage return/linefeed is sound practice--keeps the SQL meaningful to the human eye--but these characters are usually ignored by the query engine