$ in field name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cyberwolf - 26 Jan 2007 21:31 GMT

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

Signature

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

Reply to this Message

Douglas J. Steele - 27 Jan 2007 12:30 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I am trying to link to an ODBC source. But some fields in the table have
>the
[quoted text clipped - 7 lines]
>
> TIA

Reply to this Message

Rick Brandt - 27 Jan 2007 12:56 GMT

> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Douglas J. Steele - 27 Jan 2007 14:59 GMT

>> Have you tried putting square brackets around the field names ([Field$])?
>
[quoted text clipped - 3 lines]
> 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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message

Rick Brandt - 27 Jan 2007 15:09 GMT

>>> Have you tried putting square brackets around the field names ([Field$])?
>>
[quoted text clipped - 8 lines]
>
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Cyberwolf - 29 Jan 2007 13:31 GMT

Rick,

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

Signature

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$])?
> >>
[quoted text clipped - 16 lines]
>
> I don't see any mention of that in this thread so I might be mistaken.

Reply to this Message

Rick Brandt - 29 Jan 2007 13:50 GMT

> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Cyberwolf - 29 Jan 2007 13:58 GMT

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

Signature

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

> > Rick,
> >
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

Rick Brandt - 29 Jan 2007 14:14 GMT

> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Cyberwolf - 29 Jan 2007 13:29 GMT

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?

Signature

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$])?
>
[quoted text clipped - 9 lines]
> >
> > TIA

Reply to this Message

Rick Brandt - 29 Jan 2007 13:53 GMT

> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Cyberwolf - 29 Jan 2007 14:13 GMT

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

Signature

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

> > 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?
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

Rick Brandt - 29 Jan 2007 14:18 GMT

> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message