From Access to Excel Question - VBA review

[Guest]

Hi -

I have three questions on the following code:

querystring = "SELECT * " & "FROM `" & ThisWorkbook.Path & _
"\MYDATA`.Mytable Mytable " _
& "WHERE Mytable.Dstacctunit =" & "10010"

1) Instead of referencing a Table, can I reference a Query? If yes, does the
code need to change to recognize I want to refer to a Query instead of a
Table?
2) Do the names of the column Headers (Query or Table) have to be
contiguous, without spaces (example: DSTACCTUNIT, instead of DST ACCT UNIT)?
3) Instead of referencing the true column Names, can I reference a variable
that would include the column name? If yes, Can I then have the headers with
the spaces (DST ACCT UNIT)? Can you give me a tip on how to include the
column names in a variable?

Thank you!!

 

[George Nicholson]

Comments inline

Hi -

I have three questions on the following code:

querystring = "SELECT * " & "FROM `" & ThisWorkbook.Path & _
"\MYDATA`.Mytable Mytable " _
& "WHERE Mytable.Dstacctunit =" & "10010"

1) Instead of referencing a Table, can I reference a Query? If yes, does
the
code need to change to recognize I want to refer to a Query instead of a
Table?

No change is necessary.
As long as the query returns records, it acts the same as a table. (Append,
Insert, Delete queries (aka Action querues) do not return records so you
can't Select from them anyhow.)

2) Do the names of the column Headers (Query or Table) have to be
contiguous, without spaces (example: DSTACCTUNIT, instead of DST ACCT
UNIT)?

If a field name contains spaces, enclose it in brackets: [DST ACCT UNIT]
Brackets are optional for field names without spaces, but they won't hurt
anything.

3) Instead of referencing the true column Names, can I reference a
variable
that would include the column name? If yes, Can I then have the headers
with
the spaces (DST ACCT UNIT)? Can you give me a tip on how to include the
column names in a variable?

dim strField as String
strField = "[DST ACCT UNIT]"

querystring = "SELECT * " & "FROM `" & ThisWorkbook.Path & _
"\MYDATA`.Mytable Mytable " _
& "WHERE Mytable." & strField & "=" & "10010"

or

strField = "DST ACCT UNIT"

querystring = "SELECT * " & "FROM `" & ThisWorkbook.Path & _
"\MYDATA`.Mytable Mytable " _
& "WHERE Mytable.[" & strField & "]=" & "10010"


HTH