I am trying to access a table in a mdb using VBA though a brain dead
program. The problem is it's modifying the the text inside a sql string by
replacing everything bracketed by [ ] with an empty string. I don't know why
and support is?? If I enter the following line in it's editor.

strSql = "SELECT tblParts.[PART#] FROM tblParts WHERE (((tblParts.[PART#]) =
'" & FileName & "'));"

VBA sees the following

strSql = "SELECT tblParts. FROM tblParts WHERE (((tblParts.) = '" & FileName
& "'));"

Is there someway I can reference these fields in SQL by position instead of
name?
If this is not the place, can someone point me to a good SQL group?

Ron Paii wrote:
Huh?
I think you are actually saying that you are trying to write VBA code in a
brain dead text editor. You need to identify the editor. Why not use the VBA
IDE in Access?

Ughhh! Get rid of those unnecessary parentheses! And the semicolon as well -
not needed in JetSQL despite the brainwashing by the Access Query Builder.
tblParts.[PART#] = '" & FileName & "'"
You're saying that the editor you are using is doing this? What editor is
that? I've never seen that happen. Perhaps you need to escape those brackets
so the editor you are using won't process them. The problem is, without
knowing what editor you are using, it's impossible to advise you how to
escape them. It's not VBA doing this so I suspect the VBA escape method
(doubling the characters that you want to be treated literally) would work.
It can't hurt to try:
.... tblParts.[[PART#]] ...

I suppose it can't hurt to try the java escape method:
.... tblParts.\[PART#\] ...

No.

If by "SQL", you mean "SQL Server", then there are a couple in usenet that
are easily found. If you are talking about JetSQL,. then this group is as
good as any. This is not really a "SQL" question, anyways - it's a question
about the text editor you are using.

On Jan 18, 10:51*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
I know that the #, useful for date fields, can make things to appear
as a hyperlink field. I'd check to see if the field name Part#, if
changed to PartNo or PartNum, makes a difference.

Ron Paii wrote:
No, but if you create this as a saved parameterized query, you can greatly
simplify your code. Here is a post I made about this method:
http://groups.google.com/group/micro...eb98f331?pli=1

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

No it is not Access, it's a brain dead program;(
It has a feature called advanced scripting which is VBA but it only supplies
a text editor for writing code. It then exports that code into VBA after
apply it's version of macro replacement for any text within []. By adding
"Debug.Assert False" to the code, the VBA editor opened and I could see the
code. The fix was to use chr(91) and chr(93).

legal SQL syntax.


Thank you for your help.

Ron Paii wrote:
You talk like VBA is a program that one can export code into (it isn't). Do
you mean that it's inserting the code into an Access VBA module? if so, I
still don't understand why you're not doing this in Access.

Is your editor creating an executable program? If so, it's not exporting the
code "into VBA", it's compiling an executable program that probably uses the
VB Runtime dll to execute - in which case, it's not VBA, it's VB.

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

I am referencing a table in a MDB file from a CAD program to get information
for use with that program. That program's maco / script editor is a simple
text editor. The code is looks identical to VBA. When I added the
"Debug.Assert False" and ran the code. A VBA style editor opened, showing
the modified code. I can only guess the program is making the changes in the
background, then opening VBA then importing and running the code.

Ron Paii wrote:
That's not conclusive. VBA looks an awful lot like VB ... the main
difference is
there are extra application-specific functions in VBA - such as Nz(). Can
you
use Nz in code written by this editor?

Again, you cannot "open" VBA. VBA is not a program that can be opened. It is
an execution environment suppled by a dll. Office programs supply an
Integrated Development Environment (IDE) that is used to edit and debug
code. The IDE is not executing the code: it compiles the text and passes the
compilation to the VBA dll which does the execution. I suspect your CAD
program is doing something similar.

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

interface. I know VB is not on my computer.

On Wed, 18 Jan 2012 10:32:30 -0600, "Ron Paii" <None (AT) none (DOT) com> wrote:

If you change PART# to PARTNUM and omit all the square brackets (which
are only required if the field name is not a single word) then maybe
that will work.
Also some of the round backets and the semicolon are also not required
as already pointed out but they don't seem to be doing any harm.
HTH