Run-time error "3125" invalid punctuation because fieldname contains

I try to transfer PO data from SQL Server into my MS Access
Application.

The following SQL works fine:
SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
Server;SERVER=MYSERVER;APP=2007 Microsoft Office
system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO]

As soon as I add a where clause an error appears
SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
Server;SERVER=MYSERVER;APP=2007 Microsoft Office
system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] = '999'

Unfortunately the field Job No. can not be changed in my SQL Server
database

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

On Wed, 2 Apr 2008 06:40:12 -0700 (PDT), HH
wrote:

I've never understood why some people insist on funny column names.
And then leave it up to others to deal with the mess.

Perhaps you could link the table using your ODBC connection, and then
run a query in Access.
Alternatives include:
* use a different driver such as OLEDB. See www.connectionstrings.com.
* Use ADO to execute a stored procedure that takes JobNo as an
argument

-Tom.



>I try to transfer PO data from SQL Server into my MS Access
>Application.
>
>The following SQL works fine:
>SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO]
>
>As soon as I add a where clause an error appears
>SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] = '999'
>
>Unfortunately the field Job No. can not be changed in my SQL Server
>database

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

"HH" wrote in message
news:7c61471a-b24c-4c15-a374-6f003b025d34@i7g2000prf.googleg roups.com...

> WHERE [Job No.] = '999'

Perhaps you should remove the single quotes around 999, if Job No. is
numeric.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

HH wrote:
> I try to transfer PO data from SQL Server into my MS Access
> Application.
>
> The following SQL works fine:
> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO]
>
> As soon as I add a where clause an error appears
> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] = '999'
>
> Unfortunately the field Job No. can not be changed in my SQL Server
> database

You can filter the data before or after you retrieve it. You can create a
VIEW in SQL Server that renames the Job No. column (and optionally filters
on Job 999), or you can delete rows after the data is added to
purchase_headers. Or you can create a linked table and see if that works.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

On Wed, 2 Apr 2008 09:48:49 -0500, "DFS" wrote:

I like that idea: create views representing the table(s), but using
more sensible column names.

-Tom.


>HH wrote:
>> I try to transfer PO data from SQL Server into my MS Access
>> Application.
>>
>> The following SQL works fine:
>> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO]
>>
>> As soon as I add a where clause an error appears
>> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] = '999'
>>
>> Unfortunately the field Job No. can not be changed in my SQL Server
>> database
>
>You can filter the data before or after you retrieve it. You can create a
>VIEW in SQL Server that renames the Job No. column (and optionally filters
>on Job 999), or you can delete rows after the data is added to
>purchase_headers. Or you can create a linked table and see if that works.
>
>

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

HH wrote in
news:7c61471a-b24c-4c15-a374-6f003b025d34@i7g2000prf.googleg roups.com
:

> As soon as I add a where clause an error appears
> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] =
> '999'
>
> Unfortunately the field Job No. can not be changed in my SQL
> Server database

Isn't it possible to refer to fields by their ordinal position in
some instances? I don't know if ODBC supports this or not, but it
would mean you wouldn't need to use the field name. Of course it
would also mean that you'd have to hope that nobody changed the
order of the fields in the underlying table/view that you're using.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

Tom van Stiphout wrote:
> On Wed, 2 Apr 2008 09:48:49 -0500, "DFS" wrote:
>
> I like that idea: create views representing the table(s), but using
> more sensible column names.

And you can approximate VIEWs and their usage in Access this way:

1) create the VIEW query (call it View_Data) in the source database
2) create a query in your external db such as

SELECT *
FROM View_Data
IN 'path to source db'

Except for being able to move the source db without relinking all the tables
(of dubious benefit, and you still have to update the path in the query), I
can't think of any advantages to this versus linking the source tables and
writing the query in the external db. Can you?





> -Tom.
>
>
>> HH wrote:
>>> I try to transfer PO data from SQL Server into my MS Access
>>> Application.
>>>
>>> The following SQL works fine:
>>> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>>> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>>> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO]
>>>
>>> As soon as I add a where clause an error appears
>>> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>>> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>>> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] =
>>> '999'
>>>
>>> Unfortunately the field Job No. can not be changed in my SQL Server
>>> database
>>
>> You can filter the data before or after you retrieve it. You can
>> create a VIEW in SQL Server that renames the Job No. column (and
>> optionally filters on Job 999), or you can delete rows after the
>> data is added to purchase_headers. Or you can create a linked table
>> and see if that works.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
wrote:

Not in queries, but in recordsets (where query results often end up)
you can.
-Tom.


>HH wrote in
>news:7c61471a-b24c-4c15-a374-6f003b025d34@i7g2000prf.google groups.com
>:
>
>> As soon as I add a where clause an error appears
>> SELECT * INTO purchase_headers FROM [ODBC;DRIVER=SQL
>> Server;SERVER=MYSERVER;APP=2007 Microsoft Office
>> system;DATABASE=MYDB;UID=myuid;PWD=xxxxx].[PO] WHERE [Job No.] =
>> '999'
>>
>> Unfortunately the field Job No. can not be changed in my SQL
>> Server database
>
>Isn't it possible to refer to fields by their ordinal position in
>some instances? I don't know if ODBC supports this or not, but it
>would mean you wouldn't need to use the field name. Of course it
>would also mean that you'd have to hope that nobody changed the
>order of the fields in the underlying table/view that you're using.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

Tom van Stiphout wrote in
news:v6n9v3pr19onqttv4phvm944g18acj6jk1@4ax.com:

> On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
> wrote:
>>
>>Isn't it possible to refer to fields by their ordinal position in
>>some instances? I don't know if ODBC supports this or not, but it
>>would mean you wouldn't need to use the field name. Of course it
>>would also mean that you'd have to hope that nobody changed the
>>order of the fields in the underlying table/view that you're
>>using.
>
> Not in queries, but in recordsets (where query results often end
> up) you can.

Are you absolutely certain about that? I seem to recall we had a
discussion about this recently, and someone demonstrated that it
worked. Maybe it was in UNION queries only? I can't get it to work
by trying it, and maybe it required a non-Jet back end or something,
but I marked it in my mind as something worth remembering.

The memory must have gotten distorted somewhere along the line.
Maybe that's the source of my weird dreams.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

On 3 Apr 2008 23:26:31 GMT, "David W. Fenton"
wrote:

Yes, I am quite sure... that your memory must have gotten distorted
:-)
Seriously, the SQL language does not allow selecting by ordinal
positions.

-Tom.




>Tom van Stiphout wrote in
>news:v6n9v3pr19onqttv4phvm944g18acj6jk1@4ax.com:
>
>> On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
>> wrote:
>>>
>>>Isn't it possible to refer to fields by their ordinal position in
>>>some instances? I don't know if ODBC supports this or not, but it
>>>would mean you wouldn't need to use the field name. Of course it
>>>would also mean that you'd have to hope that nobody changed the
>>>order of the fields in the underlying table/view that you're
>>>using.
>>
>> Not in queries, but in recordsets (where query results often end
>> up) you can.
>
>Are you absolutely certain about that? I seem to recall we had a
>discussion about this recently, and someone demonstrated that it
>worked. Maybe it was in UNION queries only? I can't get it to work
>by trying it, and maybe it required a non-Jet back end or something,
>but I marked it in my mind as something worth remembering.
>
>The memory must have gotten distorted somewhere along the line.
>Maybe that's the source of my weird dreams.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

But you can write SQL using field numbers in the Order By::

Select ... Order By 1,2

where 1,2 refer to the first two displayed fields.

- Steve


The
"Tom van Stiphout" wrote in message
news:bt6bv3ls44ul1e9ph2vpjnlovalgjqp266@4ax.com...
> On 3 Apr 2008 23:26:31 GMT, "David W. Fenton"
> wrote:
>
> Yes, I am quite sure... that your memory must have gotten distorted
> :-)
> Seriously, the SQL language does not allow selecting by ordinal
> positions.
>
> -Tom.
>
>
>
>
>>Tom van Stiphout wrote in
>>news:v6n9v3pr19onqttv4phvm944g18acj6jk1@4ax.com:
>>
>>> On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
>>> wrote:
>>>>
>>>>Isn't it possible to refer to fields by their ordinal position in
>>>>some instances? I don't know if ODBC supports this or not, but it
>>>>would mean you wouldn't need to use the field name. Of course it
>>>>would also mean that you'd have to hope that nobody changed the
>>>>order of the fields in the underlying table/view that you're
>>>>using.
>>>
>>> Not in queries, but in recordsets (where query results often end
>>> up) you can.
>>
>>Are you absolutely certain about that? I seem to recall we had a
>>discussion about this recently, and someone demonstrated that it
>>worked. Maybe it was in UNION queries only? I can't get it to work
>>by trying it, and maybe it required a non-Jet back end or something,
>>but I marked it in my mind as something worth remembering.
>>
>>The memory must have gotten distorted somewhere along the line.
>>Maybe that's the source of my weird dreams.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

"Sky" wrote in
news:UlxJj.98$_I1.9@trnddc02:

> But you can write SQL using field numbers in the Order By::
>
> Select ... Order By 1,2
>
> where 1,2 refer to the first two displayed fields.

That's it! And that works particularly well in UNION queries, where
the actual names of the fields could be something completely
different in each SELECT.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

On Fri, 04 Apr 2008 21:47:32 GMT, "Sky" com> wrote:

Excellent! I stand corrected.

SELECT dbo_Employees.*
FROM dbo_Employees
order by 2,1

This worked on an ODBC-linked table.

-Tom.



>But you can write SQL using field numbers in the Order By::
>
> Select ... Order By 1,2
>
>where 1,2 refer to the first two displayed fields.
>
>- Steve
>
>
>The
>"Tom van Stiphout" wrote in message
>news:bt6bv3ls44ul1e9ph2vpjnlovalgjqp266@4ax.com...
>> On 3 Apr 2008 23:26:31 GMT, "David W. Fenton"
>> wrote:
>>
>> Yes, I am quite sure... that your memory must have gotten distorted
>> :-)
>> Seriously, the SQL language does not allow selecting by ordinal
>> positions.
>>
>> -Tom.
>>
>>
>>
>>
>>>Tom van Stiphout wrote in
>>>news:v6n9v3pr19onqttv4phvm944g18acj6jk1@4ax.com:
>>>
>>>> On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
>>>> wrote:
>>>>>
>>>>>Isn't it possible to refer to fields by their ordinal position in
>>>>>some instances? I don't know if ODBC supports this or not, but it
>>>>>would mean you wouldn't need to use the field name. Of course it
>>>>>would also mean that you'd have to hope that nobody changed the
>>>>>order of the fields in the underlying table/view that you're
>>>>>using.
>>>>
>>>> Not in queries, but in recordsets (where query results often end
>>>> up) you can.
>>>
>>>Are you absolutely certain about that? I seem to recall we had a
>>>discussion about this recently, and someone demonstrated that it
>>>worked. Maybe it was in UNION queries only? I can't get it to work
>>>by trying it, and maybe it required a non-Jet back end or something,
>>>but I marked it in my mind as something worth remembering.
>>>
>>>The memory must have gotten distorted somewhere along the line.
>>>Maybe that's the source of my weird dreams.
>

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

Sky wrote:
> But you can write SQL using field numbers in the Order By::
>
> Select ... Order By 1,2
>
> where 1,2 refer to the first two displayed fields.

Doesn't work for me (tested with LongInt, Text, Text columns)



> - Steve
>
>
> The
> "Tom van Stiphout" wrote in message
> news:bt6bv3ls44ul1e9ph2vpjnlovalgjqp266@4ax.com...
>> On 3 Apr 2008 23:26:31 GMT, "David W. Fenton"
>> wrote:
>>
>> Yes, I am quite sure... that your memory must have gotten distorted
>> :-)
>> Seriously, the SQL language does not allow selecting by ordinal
>> positions.
>>
>> -Tom.
>>
>>
>>
>>
>>> Tom van Stiphout wrote in
>>> news:v6n9v3pr19onqttv4phvm944g18acj6jk1@4ax.com:
>>>
>>>> On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
>>>> wrote:
>>>>>
>>>>> Isn't it possible to refer to fields by their ordinal position in
>>>>> some instances? I don't know if ODBC supports this or not, but it
>>>>> would mean you wouldn't need to use the field name. Of course it
>>>>> would also mean that you'd have to hope that nobody changed the
>>>>> order of the fields in the underlying table/view that you're
>>>>> using.
>>>>
>>>> Not in queries, but in recordsets (where query results often end
>>>> up) you can.
>>>
>>> Are you absolutely certain about that? I seem to recall we had a
>>> discussion about this recently, and someone demonstrated that it
>>> worked. Maybe it was in UNION queries only? I can't get it to work
>>> by trying it, and maybe it required a non-Jet back end or something,
>>> but I marked it in my mind as something worth remembering.
>>>
>>> The memory must have gotten distorted somewhere along the line.
>>> Maybe that's the source of my weird dreams.

Re: Run-time error "3125" invalid punctuation because fieldname contains a dot

Tom van Stiphout wrote:
> On Fri, 04 Apr 2008 21:47:32 GMT, "Sky" > com> wrote:
>
> Excellent! I stand corrected.
>
> SELECT dbo_Employees.*
> FROM dbo_Employees
> order by 2,1
>
> This worked on an ODBC-linked table.

hmmmm... I thought it didn't work for me (local Access2003 table) when I
specified the columns:

SELECT Field1, Field2, Field3
FROM Table
ORDER BY 2,1

But now it seems to be working, and it definitely works with:

SELECT *
FROM Table
ORDER BY 2,1



> -Tom.
>
>
>
>> But you can write SQL using field numbers in the Order By::
>>
>> Select ... Order By 1,2
>>
>> where 1,2 refer to the first two displayed fields.
>>
>> - Steve
>>
>>
>> The
>> "Tom van Stiphout" wrote in message
>> news:bt6bv3ls44ul1e9ph2vpjnlovalgjqp266@4ax.com...
>>> On 3 Apr 2008 23:26:31 GMT, "David W. Fenton"
>>> wrote:
>>>
>>> Yes, I am quite sure... that your memory must have gotten distorted
>>> :-)
>>> Seriously, the SQL language does not allow selecting by ordinal
>>> positions.
>>>
>>> -Tom.
>>>
>>>
>>>
>>>
>>>> Tom van Stiphout wrote in
>>>> news:v6n9v3pr19onqttv4phvm944g18acj6jk1@4ax.com:
>>>>
>>>>> On 3 Apr 2008 04:25:11 GMT, "David W. Fenton"
>>>>> wrote:
>>>>>>
>>>>>> Isn't it possible to refer to fields by their ordinal position in
>>>>>> some instances? I don't know if ODBC supports this or not, but it
>>>>>> would mean you wouldn't need to use the field name. Of course it
>>>>>> would also mean that you'd have to hope that nobody changed the
>>>>>> order of the fields in the underlying table/view that you're
>>>>>> using.
>>>>>
>>>>> Not in queries, but in recordsets (where query results often end
>>>>> up) you can.
>>>>
>>>> Are you absolutely certain about that? I seem to recall we had a
>>>> discussion about this recently, and someone demonstrated that it
>>>> worked. Maybe it was in UNION queries only? I can't get it to work
>>>> by trying it, and maybe it required a non-Jet back end or
>>>> something, but I marked it in my mind as something worth
>>>> remembering.
>>>>
>>>> The memory must have gotten distorted somewhere along the line.
>>>> Maybe that's the source of my weird dreams.