$ in field name

[Cyberwolf]

I am trying to link to an ODBC source. But some fields in the table have the
$ in their name so I get an error. Is there anyway around this. I just want
to create a pass-through query using some of the fields which 2 of the fields
contain the $.

Any help would be appreciated

TIA

 

[Douglas J. Steele]

Have you tried putting square brackets around the field names ([Field$])?

 

[Douglas J. Steele]

Have you tried putting square brackets around the field names ([Field$])?

The OP's question doesn't really make sense in the context of a
passthrough query. The SQL is parsed by the server and in the server's
eyes those are perfectly legal field names. All he should have to do is
alias them in the passthrough query using names without the dollar sign so
that the field names in the data set are acceptable to Access.

I wasn't sure about that (and don't have access to SQL Server at the moment,
so couldn't check), but I figured it was worth a try.

Your solution, though, makes more sense of course.

 

[Rick Brandt]

Have you tried putting square brackets around the field names ([Field$])?

The OP's question doesn't really make sense in the context of a passthrough
query. The SQL is parsed by the server and in the server's eyes those are
perfectly legal field names. All he should have to do is alias them in the
passthrough query using names without the dollar sign so that the field names
in the data set are acceptable to Access.

I wasn't sure about that (and don't have access to SQL Server at the moment,
so couldn't check), but I figured it was worth a try.

Your solution, though, makes more sense of course.

Actually your suggestion was right on if he was using SQL Server because like
Access it allows "illegal" field names if you put square brackets around them.
I thought that in another group the OP indicated that the passthrough was to an
AS400 though which does not have such flexibility. The field name is either
legal or it is not.

I don't see any mention of that in this thread so I might be mistaken.

 

[Cyberwolf]

I think I confused you. I can't even create a linked table. I belive that
is needed to create a pass-through query, correct?
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

Have you tried putting square brackets around the field names ([Field$])?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to link to an ODBC source. But some fields in the table have
the
$ in their name so I get an error. Is there anyway around this. I just
want
to create a pass-through query using some of the fields which 2 of the
fields
contain the $.

Any help would be appreciated

TIA
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

 

[Cyberwolf]

Rick,

This is to an AS400 table. So, basically you are saying I can't do anything
to get to this data?
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

Have you tried putting square brackets around the field names ([Field$])?

The OP's question doesn't really make sense in the context of a passthrough
query. The SQL is parsed by the server and in the server's eyes those are
perfectly legal field names. All he should have to do is alias them in the
passthrough query using names without the dollar sign so that the field names
in the data set are acceptable to Access.

I wasn't sure about that (and don't have access to SQL Server at the moment,
so couldn't check), but I figured it was worth a try.

Your solution, though, makes more sense of course.

Actually your suggestion was right on if he was using SQL Server because like
Access it allows "illegal" field names if you put square brackets around them.
I thought that in another group the OP indicated that the passthrough was to an
AS400 though which does not have such flexibility. The field name is either
legal or it is not.

I don't see any mention of that in this thread so I might be mistaken.

 

[Rick Brandt]

Rick,

This is to an AS400 table. So, basically you are saying I can't do
anything to get to this data?

I don't believe I suggested that at all. What I said was that a passthrough
query should not care about the $ in the field name because that is
perfectly acceptable to the AS400. However; to avoid problems when you use
the result-set in your Access app you should provide an alias for that field
in your SQL that does not include the $ in the name.

 

[Rick Brandt]

I think I confused you. I can't even create a linked table. I
belive that is needed to create a pass-through query, correct?

What ODBC driver are you using? I have no problem creating links to tables
on our AS400 that have $ in the field names. We use the IBM driver.

The only time I have found that a field name interfered with linking was
when I encountered a SQL Server table that had a dot in the name of a field.

 

[Cyberwolf]

My mistake. But, don't I need to have the table linked in order to run the
passthrough query?

 

[Cyberwolf]

Ok, I am using the IBM driver. The error I get is "to many indexes on
table". The error that I saw before was on someone elses PC. (I should have
tried it on mine to begin with). Is there a way around the "to many indexes
error" If not, is there a way ot use VBA to get araound it?

Thanks

 

[Rick Brandt]

My mistake. But, don't I need to have the table linked in order to
run the passthrough query?

No. (sort of the point of a passthrough query). You only need a link to
create a standard query that goes against the link.

 

[Rick Brandt]

Ok, I am using the IBM driver. The error I get is "to many indexes
on table". The error that I saw before was on someone elses PC. (I
should have tried it on mine to begin with). Is there a way around
the "to many indexes error" If not, is there a way ot use VBA to get
araound it?

Okay THAT error I have seen. When you link to an AS400 table every logical
file on the AS400 using that table will be treated as an index by Access and
if there are more than 32 then you cannot link to that table. However; if
you link to a logical file Access does not "see" all of the other logical
files so if you have a logical file that contains all of the data that the
physical file you are trying to link to then you should be able to link to
that.

If you only need read access then the passthrough query is another way
around the problem because a passthrough query does not care how many
indexes are on the table being selected from.