Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13603 > unrolled thread

OLEDB syntax problems

Back to article view | Back to comp.databases.ms-access

Contents

  OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-02 09:56 -0400
    Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-02 11:15 -0400
      Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-02 17:38 -0400
    Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-02 12:33 -0700
      Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-02 17:45 -0400
        Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-03 05:25 -0700
          Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-03 08:48 -0400
            Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-03 20:51 -0400
              Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-04 07:05 -0400
                Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-04 15:43 -0400
                Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-04 15:56 -0400
                  Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-04 18:57 -0400
                  Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-06 13:57 -0400
                    Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-06 14:46 -0400
                      Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-07 08:17 -0400
                        Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-07 06:32 -0700
                          Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-07 10:49 -0400
                            Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-07 07:54 -0700
                              Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-07 11:17 -0400
                                Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-07 08:46 -0700
                                  Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-07 12:18 -0400
                                    Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-07 10:28 -0700
                        Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-07 16:34 -0400
                          Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-07 17:16 -0400
                            Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-08 12:58 -0400
                              Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-08 13:20 -0400
                                Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-08 14:24 -0400
                                  Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-08 15:34 -0400
                                  Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-09 15:36 -0400
                        Re: OLEDB syntax problems Ron Weiner <rw@domain.com> - 2019-05-07 16:49 -0400
                          Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-07 17:18 -0400
                    Re: OLEDB syntax problems Ron Paii <ron81pai@gmail.com> - 2019-05-06 12:33 -0700
                      Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-06 15:52 -0400
    Re: OLEDB syntax problems FreeToGolfAndSki <genoagroup@gmail.com> - 2019-05-08 06:41 -0700
      Re: OLEDB syntax problems Neil <neil@myplaceofwork.com> - 2019-05-08 13:26 -0400

Page 1 of 2  [1] 2  Next page →

#13603 — OLEDB syntax problems

We recently completed updating our Access database to .accdb format and 
using the ACE.OLEDB driver. Last week, one of our tracking functions 
began to fail with the message:

Microsoft Access Database Engine error '80040e14'
Syntax error in INSERT INTO statement

After testing, including checking the Microsoft documentation (the error 
number is useless), the on-line SQL syntax verification that shows the 
syntax to be 100% correct, and other sources, we are still getting the 
error. The current syntax is:

INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" 
& WhoAdm & "', '" & DidWhat & "');

Everything is simple plain text except LogVal, which is a date & time 
compilation that is converted to text. Our own syntax checker that uses 
the same content as the live version returns this result which appears 
to be valid:

INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John 
Smith', 'Upload of email attachment failed at 11:30:00 AM: ');

Any help and insights are appreciated.
-- 
best regards,

Neil

[toc] | [next] | [standalone]

#13604

Neil explained :
> We recently completed updating our Access database to .accdb format and using 
> the ACE.OLEDB driver. Last week, one of our tracking functions began to fail 
> with the message:
>
> Microsoft Access Database Engine error '80040e14'
> Syntax error in INSERT INTO statement
>
> After testing, including checking the Microsoft documentation (the error 
> number is useless), the on-line SQL syntax verification that shows the syntax 
> to be 100% correct, and other sources, we are still getting the error. The 
> current syntax is:
>
> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" & 
> WhoAdm & "', '" & DidWhat & "');
>
> Everything is simple plain text except LogVal, which is a date & time 
> compilation that is converted to text. Our own syntax checker that uses the 
> same content as the live version returns this result which appears to be 
> valid:
>
> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John 
> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>
> Any help and insights are appreciated.

 Hmmmm... '2019/5/1/11' does not appear to be a valid date to me.  
There is a lot of forward slashes in there.  What was the real date 
that LogVal was derived from.

rdub

---
This email has been checked for viruses by AVG.
https://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13606

On 5/2/2019 11:15 AM, Ron Weiner wrote:
> Neil explained :
>> We recently completed updating our Access database to .accdb format 
>> and using the ACE.OLEDB driver. Last week, one of our tracking 
>> functions began to fail with the message:
>>
>> Microsoft Access Database Engine error '80040e14'
>> Syntax error in INSERT INTO statement
>>
>> After testing, including checking the Microsoft documentation (the 
>> error number is useless), the on-line SQL syntax verification that 
>> shows the syntax to be 100% correct, and other sources, we are still 
>> getting the error. The current syntax is:
>>
>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', 
>> '" & WhoAdm & "', '" & DidWhat & "');
>>
>> Everything is simple plain text except LogVal, which is a date & time 
>> compilation that is converted to text. Our own syntax checker that 
>> uses the same content as the live version returns this result which 
>> appears to be valid:
>>
>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 
>> 'John Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>
>> Any help and insights are appreciated.
> 
> Hmmmm... '2019/5/1/11' does not appear to be a valid date to me. There 
> is a lot of forward slashes in there.  What was the real date that 
> LogVal was derived from.
> 
> rdub
> 
As I wrote, the LogVal is a date/time compilation organized for a 
particular purpose. It isn't a simple date statement. So, the forward 
slashes are inserted as part of the compilation. At any rate, that isn't 
what is causing the syntax failure messages.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13605

On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
> We recently completed updating our Access database to .accdb format and 
> using the ACE.OLEDB driver. Last week, one of our tracking functions 
> began to fail with the message:
> 
> Microsoft Access Database Engine error '80040e14'
> Syntax error in INSERT INTO statement
> 
> After testing, including checking the Microsoft documentation (the error 
> number is useless), the on-line SQL syntax verification that shows the 
> syntax to be 100% correct, and other sources, we are still getting the 
> error. The current syntax is:
> 
> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" 
> & WhoAdm & "', '" & DidWhat & "');
> 
> Everything is simple plain text except LogVal, which is a date & time 
> compilation that is converted to text. Our own syntax checker that uses 
> the same content as the live version returns this result which appears 
> to be valid:
> 
> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John 
> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
> 
> Any help and insights are appreciated.
> -- 
> best regards,
> 
> Neil

is LogIn expecting a date?

?isdate('2019/5/11') fails with Expected: expression
?isdate("2019/5/11") returns true
?isdate(#2019/5/11#) returns true

Newer version of Access are pickier on data type.

PS:
Have your considered using named and typed parameters? The tend to move the data errors into your code where they are easier to trap.

[toc] | [prev] | [next] | [standalone]

#13607

On 5/2/2019 3:33 PM, Ron Paii wrote:
> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>> We recently completed updating our Access database to .accdb format and
>> using the ACE.OLEDB driver. Last week, one of our tracking functions
>> began to fail with the message:
>>
>> Microsoft Access Database Engine error '80040e14'
>> Syntax error in INSERT INTO statement
>>
>> After testing, including checking the Microsoft documentation (the error
>> number is useless), the on-line SQL syntax verification that shows the
>> syntax to be 100% correct, and other sources, we are still getting the
>> error. The current syntax is:
>>
>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '"
>> & WhoAdm & "', '" & DidWhat & "');
>>
>> Everything is simple plain text except LogVal, which is a date & time
>> compilation that is converted to text. Our own syntax checker that uses
>> the same content as the live version returns this result which appears
>> to be valid:
>>
>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John
>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>
>> Any help and insights are appreciated.
>> -- 
>> best regards,
>>
>> Neil
> 
> is LogIn expecting a date?
> 
No.

> ?isdate('2019/5/11') fails with Expected: expression
> ?isdate("2019/5/11") returns true
> ?isdate(#2019/5/11#) returns true
> 
> Newer version of Access are pickier on data type.
> 
> PS:
> Have your considered using named and typed parameters? The tend to move the data errors into your code where they are easier to trap.
> 

All of the values entered into the database are parameterized. I'm 
primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs. 
the older Access drivers.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13608

On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
> On 5/2/2019 3:33 PM, Ron Paii wrote:
> > On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
> >> We recently completed updating our Access database to .accdb format and
> >> using the ACE.OLEDB driver. Last week, one of our tracking functions
> >> began to fail with the message:
> >>
> >> Microsoft Access Database Engine error '80040e14'
> >> Syntax error in INSERT INTO statement
> >>
> >> After testing, including checking the Microsoft documentation (the error
> >> number is useless), the on-line SQL syntax verification that shows the
> >> syntax to be 100% correct, and other sources, we are still getting the
> >> error. The current syntax is:
> >>
> >> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '"
> >> & WhoAdm & "', '" & DidWhat & "');
> >>
> >> Everything is simple plain text except LogVal, which is a date & time
> >> compilation that is converted to text. Our own syntax checker that uses
> >> the same content as the live version returns this result which appears
> >> to be valid:
> >>
> >> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John
> >> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
> >>
> >> Any help and insights are appreciated.
> >> -- 
> >> best regards,
> >>
> >> Neil
> > 
> > is LogIn expecting a date?
> > 
> No.
> 
> > ?isdate('2019/5/11') fails with Expected: expression
> > ?isdate("2019/5/11") returns true
> > ?isdate(#2019/5/11#) returns true
> > 
> > Newer version of Access are pickier on data type.
> > 
> > PS:
> > Have your considered using named and typed parameters? The tend to move the data errors into your code where they are easier to trap.
> > 
> 
> All of the values entered into the database are parameterized. I'm 
> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs. 
> the older Access drivers.
> 
> -- 
> best regards,
> 
> Neil

The query you posted is not parameterized.
The following code will execute your query with the parameters values properly typed and sized before the execute. You could also save this query for faster execution.

dim addLogQuery as DAO.Querydef

set addLogQuery = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS loginDate DateTime, adminName Text ( 25 ), actionText Text ( 255 );
INSERT INTO AdTrack (LogIn, Admin, Action) 
VALUES ([loginDate], [adminName], [actionText]);")

addLogQuery.Paramiters(0) = #2019/5/1/11#
addLogQuery.Paramiters(1) = "John Smith"
addLogQuery.Paramiters(2) = "Upload of email attachment failed at 11:30:00 AM:"

addLogQuery.Execute
set addLogQuery = nothing

[toc] | [prev] | [next] | [standalone]

#13609

On 5/3/2019 8:25 AM, Ron Paii wrote:
> On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
>> On 5/2/2019 3:33 PM, Ron Paii wrote:
>>> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>>>> We recently completed updating our Access database to .accdb format and
>>>> using the ACE.OLEDB driver. Last week, one of our tracking functions
>>>> began to fail with the message:
>>>>
>>>> Microsoft Access Database Engine error '80040e14'
>>>> Syntax error in INSERT INTO statement
>>>>
>>>> After testing, including checking the Microsoft documentation (the error
>>>> number is useless), the on-line SQL syntax verification that shows the
>>>> syntax to be 100% correct, and other sources, we are still getting the
>>>> error. The current syntax is:
>>>>
>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '"
>>>> & WhoAdm & "', '" & DidWhat & "');
>>>>
>>>> Everything is simple plain text except LogVal, which is a date & time
>>>> compilation that is converted to text. Our own syntax checker that uses
>>>> the same content as the live version returns this result which appears
>>>> to be valid:
>>>>
>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John
>>>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>>>
>>>> Any help and insights are appreciated.
>>>> -- 
>>>> best regards,
>>>>
>>>> Neil
>>>
>>> is LogIn expecting a date?
>>>
>> No.
>>
>>> ?isdate('2019/5/11') fails with Expected: expression
>>> ?isdate("2019/5/11") returns true
>>> ?isdate(#2019/5/11#) returns true
>>>
>>> Newer version of Access are pickier on data type.
>>>
>>> PS:
>>> Have your considered using named and typed parameters? The tend to move the data errors into your code where they are easier to trap.
>>>
>>
>> All of the values entered into the database are parameterized. I'm
>> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs.
>> the older Access drivers.
>>
>> -- 
>> best regards,
>>
>> Neil
> 
> The query you posted is not parameterized.
> The following code will execute your query with the parameters values properly typed and sized before the execute. You could also save this query for faster execution.
> 
> dim addLogQuery as DAO.Querydef
> 
> set addLogQuery = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS loginDate DateTime, adminName Text ( 25 ), actionText Text ( 255 );
> INSERT INTO AdTrack (LogIn, Admin, Action)
> VALUES ([loginDate], [adminName], [actionText]);")
> 
> addLogQuery.Paramiters(0) = #2019/5/1/11#
> addLogQuery.Paramiters(1) = "John Smith"
> addLogQuery.Paramiters(2) = "Upload of email attachment failed at 11:30:00 AM:"
> 
> addLogQuery.Execute
> set addLogQuery = nothing
> 
Thanks for your thoughts, Ron. However, I think the main point is being 
missed. What I mean by "parameterized" is that all values that are 
actually entered into the database by the program are specifically 
formatted code sequences, and the process will not accept generic 
inserted data for input. It is not possible to inject something into the 
database externally.

The data fields in use are 3 TEXT fields, no number or date fields. In 
my tests, I determined whether it was the format of any of the content 
that triggered the error. It was not.

My above SQL sequence can be inserted into on-line SQL checker, such as:
<https://www.eversql.com/sql-syntax-check-validator/>
and the result is that it is 100% correct.

And, without making any changes in the syntax, after reverting to the 
"old" MS Access driver the process worked flawlessly as it had for over 
20 years.

The basic question I asked here is, what is it about the ACE.OLEDB 
driver that is so quirky with MS-Access?

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13610

Neil pretended :
> On 5/3/2019 8:25 AM, Ron Paii wrote:
>> On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
>>> On 5/2/2019 3:33 PM, Ron Paii wrote:
>>>> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>>>>> We recently completed updating our Access database to .accdb format and
>>>>> using the ACE.OLEDB driver. Last week, one of our tracking functions
>>>>> began to fail with the message:
>>>>>
>>>>> Microsoft Access Database Engine error '80040e14'
>>>>> Syntax error in INSERT INTO statement
>>>>>
>>>>> After testing, including checking the Microsoft documentation (the error
>>>>> number is useless), the on-line SQL syntax verification that shows the
>>>>> syntax to be 100% correct, and other sources, we are still getting the
>>>>> error. The current syntax is:
>>>>>
>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '"
>>>>> & WhoAdm & "', '" & DidWhat & "');
>>>>>
>>>>> Everything is simple plain text except LogVal, which is a date & time
>>>>> compilation that is converted to text. Our own syntax checker that uses
>>>>> the same content as the live version returns this result which appears
>>>>> to be valid:
>>>>>
>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John
>>>>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>>>>
>>>>> Any help and insights are appreciated.
>>>>> -- best regards,
>>>>>
>>>>> Neil
>>>>
>>>> is LogIn expecting a date?
>>>>
>>> No.
>>>
>>>> ?isdate('2019/5/11') fails with Expected: expression
>>>> ?isdate("2019/5/11") returns true
>>>> ?isdate(#2019/5/11#) returns true
>>>>
>>>> Newer version of Access are pickier on data type.
>>>>
>>>> PS:
>>>> Have your considered using named and typed parameters? The tend to move 
>>>> the data errors into your code where they are easier to trap.
>>>>
>>>
>>> All of the values entered into the database are parameterized. I'm
>>> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs.
>>> the older Access drivers.
>>>
>>> -- best regards,
>>>
>>> Neil
>> 
>> The query you posted is not parameterized.
>> The following code will execute your query with the parameters values 
>> properly typed and sized before the execute. You could also save this query 
>> for faster execution.
>> 
>> dim addLogQuery as DAO.Querydef
>> 
>> set addLogQuery = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS 
>> loginDate DateTime, adminName Text ( 25 ), actionText Text ( 255 );
>> INSERT INTO AdTrack (LogIn, Admin, Action)
>> VALUES ([loginDate], [adminName], [actionText]);")
>> 
>> addLogQuery.Paramiters(0) = #2019/5/1/11#
>> addLogQuery.Paramiters(1) = "John Smith"
>> addLogQuery.Paramiters(2) = "Upload of email attachment failed at 11:30:00 
>> AM:"
>> 
>> addLogQuery.Execute
>> set addLogQuery = nothing
>> 
> Thanks for your thoughts, Ron. However, I think the main point is being 
> missed. What I mean by "parameterized" is that all values that are actually 
> entered into the database by the program are specifically formatted code 
> sequences, and the process will not accept generic inserted data for input. 
> It is not possible to inject something into the database externally.
>
> The data fields in use are 3 TEXT fields, no number or date fields. In my 
> tests, I determined whether it was the format of any of the content that 
> triggered the error. It was not.
>
> My above SQL sequence can be inserted into on-line SQL checker, such as:
> <https://www.eversql.com/sql-syntax-check-validator/>
> and the result is that it is 100% correct.
>
> And, without making any changes in the syntax, after reverting to the "old" 
> MS Access driver the process worked flawlessly as it had for over 20 years.
>
> The basic question I asked here is, what is it about the ACE.OLEDB driver 
> that is so quirky with MS-Access?

Grasping at straws here, but are the values you are showing in your 
example the actual values that are in the failing query?  Can you 
supply DDL for the table AdTrack so we can try for a repro?  i am just 
not seeing the problem.  Sorry :-(

Rdub

---
This email has been checked for viruses by AVG.
https://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13611

On 5/3/2019 8:51 PM, Ron Weiner wrote:
> Neil pretended :
>> On 5/3/2019 8:25 AM, Ron Paii wrote:
>>> On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
>>>> On 5/2/2019 3:33 PM, Ron Paii wrote:
>>>>> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>>>>>> We recently completed updating our Access database to .accdb 
>>>>>> format and
>>>>>> using the ACE.OLEDB driver. Last week, one of our tracking functions
>>>>>> began to fail with the message:
>>>>>>
>>>>>> Microsoft Access Database Engine error '80040e14'
>>>>>> Syntax error in INSERT INTO statement
>>>>>>
>>>>>> After testing, including checking the Microsoft documentation (the 
>>>>>> error
>>>>>> number is useless), the on-line SQL syntax verification that shows 
>>>>>> the
>>>>>> syntax to be 100% correct, and other sources, we are still getting 
>>>>>> the
>>>>>> error. The current syntax is:
>>>>>>
>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & 
>>>>>> "', '"
>>>>>> & WhoAdm & "', '" & DidWhat & "');
>>>>>>
>>>>>> Everything is simple plain text except LogVal, which is a date & time
>>>>>> compilation that is converted to text. Our own syntax checker that 
>>>>>> uses
>>>>>> the same content as the live version returns this result which 
>>>>>> appears
>>>>>> to be valid:
>>>>>>
>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 
>>>>>> 'John
>>>>>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>>>>>
>>>>>> Any help and insights are appreciated.
>>>>>> -- best regards,
>>>>>>
>>>>>> Neil
>>>>>
>>>>> is LogIn expecting a date?
>>>>>
>>>> No.
>>>>
>>>>> ?isdate('2019/5/11') fails with Expected: expression
>>>>> ?isdate("2019/5/11") returns true
>>>>> ?isdate(#2019/5/11#) returns true
>>>>>
>>>>> Newer version of Access are pickier on data type.
>>>>>
>>>>> PS:
>>>>> Have your considered using named and typed parameters? The tend to 
>>>>> move the data errors into your code where they are easier to trap.
>>>>>
>>>>
>>>> All of the values entered into the database are parameterized. I'm
>>>> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs.
>>>> the older Access drivers.
>>>>
>>>> -- best regards,
>>>>
>>>> Neil
>>>
>>> The query you posted is not parameterized.
>>> The following code will execute your query with the parameters values 
>>> properly typed and sized before the execute. You could also save this 
>>> query for faster execution.
>>>
>>> dim addLogQuery as DAO.Querydef
>>>
>>> set addLogQuery = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS 
>>> loginDate DateTime, adminName Text ( 25 ), actionText Text ( 255 );
>>> INSERT INTO AdTrack (LogIn, Admin, Action)
>>> VALUES ([loginDate], [adminName], [actionText]);")
>>>
>>> addLogQuery.Paramiters(0) = #2019/5/1/11#
>>> addLogQuery.Paramiters(1) = "John Smith"
>>> addLogQuery.Paramiters(2) = "Upload of email attachment failed at 
>>> 11:30:00 AM:"
>>>
>>> addLogQuery.Execute
>>> set addLogQuery = nothing
>>>
>> Thanks for your thoughts, Ron. However, I think the main point is 
>> being missed. What I mean by "parameterized" is that all values that 
>> are actually entered into the database by the program are specifically 
>> formatted code sequences, and the process will not accept generic 
>> inserted data for input. It is not possible to inject something into 
>> the database externally.
>>
>> The data fields in use are 3 TEXT fields, no number or date fields. In 
>> my tests, I determined whether it was the format of any of the content 
>> that triggered the error. It was not.
>>
>> My above SQL sequence can be inserted into on-line SQL checker, such as:
>> <https://www.eversql.com/sql-syntax-check-validator/>
>> and the result is that it is 100% correct.
>>
>> And, without making any changes in the syntax, after reverting to the 
>> "old" MS Access driver the process worked flawlessly as it had for 
>> over 20 years.
>>
>> The basic question I asked here is, what is it about the ACE.OLEDB 
>> driver that is so quirky with MS-Access?
> 
> Grasping at straws here, but are the values you are showing in your 
> example the actual values that are in the failing query?  Can you supply 
> DDL for the table AdTrack so we can try for a repro?  i am just not 
> seeing the problem.  Sorry :-(
> 
> Rdub
> 
No need to apologize...the problem is that there isn't a problem with 
the SQL structure itself, it's in how the ACE.OLEDB driver handles it. ;-)

In my tests, I've proven that the actual values entered into the field 
do not change the outcome. It's an issue with how the driver wants the 
SQL statement structured, but I have not found one source of information 
that suggests that the way it is currently structured is incorrect.

Another bizarre aspect, which is OT for this thread but not irrelevant, 
is that the UPDATE and even complex SELECT commands work just fine with 
the ACE.OLEDB driver, yet every instance of INSERT fails, sometimes with 
an error message, but most of the time without one.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13612

After serious thinking Neil wrote :
> On 5/3/2019 8:51 PM, Ron Weiner wrote:
>> Neil pretended :
>>> On 5/3/2019 8:25 AM, Ron Paii wrote:
>>>> On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
>>>>> On 5/2/2019 3:33 PM, Ron Paii wrote:
>>>>>> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>>>>>>> We recently completed updating our Access database to .accdb format 
>>>>>>> and
>>>>>>> using the ACE.OLEDB driver. Last week, one of our tracking functions
>>>>>>> began to fail with the message:
>>>>>>>
>>>>>>> Microsoft Access Database Engine error '80040e14'
>>>>>>> Syntax error in INSERT INTO statement
>>>>>>>
>>>>>>> After testing, including checking the Microsoft documentation (the 
>>>>>>> error
>>>>>>> number is useless), the on-line SQL syntax verification that shows the
>>>>>>> syntax to be 100% correct, and other sources, we are still getting the
>>>>>>> error. The current syntax is:
>>>>>>>
>>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', 
>>>>>>> '"
>>>>>>> & WhoAdm & "', '" & DidWhat & "');
>>>>>>>
>>>>>>> Everything is simple plain text except LogVal, which is a date & time
>>>>>>> compilation that is converted to text. Our own syntax checker that 
>>>>>>> uses
>>>>>>> the same content as the live version returns this result which appears
>>>>>>> to be valid:
>>>>>>>
>>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 
>>>>>>> 'John
>>>>>>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>>>>>>
>>>>>>> Any help and insights are appreciated.
>>>>>>> -- best regards,
>>>>>>>
>>>>>>> Neil
>>>>>>
>>>>>> is LogIn expecting a date?
>>>>>>
>>>>> No.
>>>>>
>>>>>> ?isdate('2019/5/11') fails with Expected: expression
>>>>>> ?isdate("2019/5/11") returns true
>>>>>> ?isdate(#2019/5/11#) returns true
>>>>>>
>>>>>> Newer version of Access are pickier on data type.
>>>>>>
>>>>>> PS:
>>>>>> Have your considered using named and typed parameters? The tend to move 
>>>>>> the data errors into your code where they are easier to trap.
>>>>>>
>>>>>
>>>>> All of the values entered into the database are parameterized. I'm
>>>>> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs.
>>>>> the older Access drivers.
>>>>>
>>>>> -- best regards,
>>>>>
>>>>> Neil
>>>>
>>>> The query you posted is not parameterized.
>>>> The following code will execute your query with the parameters values 
>>>> properly typed and sized before the execute. You could also save this 
>>>> query for faster execution.
>>>>
>>>> dim addLogQuery as DAO.Querydef
>>>>
>>>> set addLogQuery = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS 
>>>> loginDate DateTime, adminName Text ( 25 ), actionText Text ( 255 );
>>>> INSERT INTO AdTrack (LogIn, Admin, Action)
>>>> VALUES ([loginDate], [adminName], [actionText]);")
>>>>
>>>> addLogQuery.Paramiters(0) = #2019/5/1/11#
>>>> addLogQuery.Paramiters(1) = "John Smith"
>>>> addLogQuery.Paramiters(2) = "Upload of email attachment failed at 
>>>> 11:30:00 AM:"
>>>>
>>>> addLogQuery.Execute
>>>> set addLogQuery = nothing
>>>>
>>> Thanks for your thoughts, Ron. However, I think the main point is being 
>>> missed. What I mean by "parameterized" is that all values that are 
>>> actually entered into the database by the program are specifically 
>>> formatted code sequences, and the process will not accept generic inserted 
>>> data for input. It is not possible to inject something into the database 
>>> externally.
>>>
>>> The data fields in use are 3 TEXT fields, no number or date fields. In my 
>>> tests, I determined whether it was the format of any of the content that 
>>> triggered the error. It was not.
>>>
>>> My above SQL sequence can be inserted into on-line SQL checker, such as:
>>> <https://www.eversql.com/sql-syntax-check-validator/>
>>> and the result is that it is 100% correct.
>>>
>>> And, without making any changes in the syntax, after reverting to the 
>>> "old" MS Access driver the process worked flawlessly as it had for over 20 
>>> years.
>>>
>>> The basic question I asked here is, what is it about the ACE.OLEDB driver 
>>> that is so quirky with MS-Access?
>> 
>> Grasping at straws here, but are the values you are showing in your example 
>> the actual values that are in the failing query?  Can you supply DDL for 
>> the table AdTrack so we can try for a repro?  i am just not seeing the 
>> problem.  Sorry :-(
>> 
>> Rdub
>> 
> No need to apologize...the problem is that there isn't a problem with the SQL 
> structure itself, it's in how the ACE.OLEDB driver handles it. ;-)
>
> In my tests, I've proven that the actual values entered into the field do not 
> change the outcome. It's an issue with how the driver wants the SQL statement 
> structured, but I have not found one source of information that suggests that 
> the way it is currently structured is incorrect.
>
> Another bizarre aspect, which is OT for this thread but not irrelevant, is 
> that the UPDATE and even complex SELECT commands work just fine with the 
> ACE.OLEDB driver, yet every instance of INSERT fails, sometimes with an error 
> message, but most of the time without one.

OK I have tried to make a repro.  I created a new Access 2007 accdb 
database (2007 is the newest version of Access I have).  Then I created 
and ran the following DDL Sql to create a table with a bunch of 
different column data types.

CREATE Table MyTable
  (
  MyID AutoIncrement CONSTRAINT MyIdConstraint PRIMARY KEY,
  MyText CHAR(50),
  MyByte BYTE,
  MyInteger SMALLINT,
  MyLong LONG,
  MySingle SINGLE,
  MyDouble DOUBLE,
  MyDate DATETIME,
  MyCurrency CURRENCY,
  MyYesNo YESNO
  )

Then I created the following Update query.

"INSERT INTO MyTable (MyText, MyByte, MyInteger, MyLong, MySingle, 
MyDouble, MyDate, MyCurrency, MyYesNo)
Values('Simple Text', 16, 22000, 128000, 2.1, 3.14159, #1/30/1947#, 
10.01, 1);"

Finally from the Immediate Window I ran the above Sql using the 
following Access commands.

DoCmd.RunSQL ("..the above Sql..")
-=-=-=  and  -=-=-=
CurrentDb.Execute ("..the above Sql..")

In both cases it inserted a record into the table MyTable.

So I am pretty sure the problem is not internal to Access itself, but 
likely related to the Connection String you are using to connect your 
app to the database.  Can you share your connection string with us, 
again so we can try for a repro.

Rdub

---
This email has been checked for viruses by AVG.
https://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13613

Neil wrote on 5/4/2019 :
> On 5/3/2019 8:51 PM, Ron Weiner wrote:
>> Neil pretended :
>>> On 5/3/2019 8:25 AM, Ron Paii wrote:
>>>> On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
>>>>> On 5/2/2019 3:33 PM, Ron Paii wrote:
>>>>>> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>>>>>>> We recently completed updating our Access database to .accdb format 
>>>>>>> and
>>>>>>> using the ACE.OLEDB driver. Last week, one of our tracking functions
>>>>>>> began to fail with the message:
>>>>>>>
>>>>>>> Microsoft Access Database Engine error '80040e14'
>>>>>>> Syntax error in INSERT INTO statement
>>>>>>>
>>>>>>> After testing, including checking the Microsoft documentation (the 
>>>>>>> error
>>>>>>> number is useless), the on-line SQL syntax verification that shows the
>>>>>>> syntax to be 100% correct, and other sources, we are still getting the
>>>>>>> error. The current syntax is:
>>>>>>>
>>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', 
>>>>>>> '"
>>>>>>> & WhoAdm & "', '" & DidWhat & "');
>>>>>>>
>>>>>>> Everything is simple plain text except LogVal, which is a date & time
>>>>>>> compilation that is converted to text. Our own syntax checker that 
>>>>>>> uses
>>>>>>> the same content as the live version returns this result which appears
>>>>>>> to be valid:
>>>>>>>
>>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 
>>>>>>> 'John
>>>>>>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>>>>>>
>>>>>>> Any help and insights are appreciated.
>>>>>>> -- best regards,
>>>>>>>
>>>>>>> Neil
>>>>>>
>>>>>> is LogIn expecting a date?
>>>>>>
>>>>> No.
>>>>>
>>>>>> ?isdate('2019/5/11') fails with Expected: expression
>>>>>> ?isdate("2019/5/11") returns true
>>>>>> ?isdate(#2019/5/11#) returns true
>>>>>>
>>>>>> Newer version of Access are pickier on data type.
>>>>>>
>>>>>> PS:
>>>>>> Have your considered using named and typed parameters? The tend to move 
>>>>>> the data errors into your code where they are easier to trap.
>>>>>>
>>>>>
>>>>> All of the values entered into the database are parameterized. I'm
>>>>> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs.
>>>>> the older Access drivers.
>>>>>
>>>>> -- best regards,
>>>>>
>>>>> Neil
>>>>
>>>> The query you posted is not parameterized.
>>>> The following code will execute your query with the parameters values 
>>>> properly typed and sized before the execute. You could also save this 
>>>> query for faster execution.
>>>>
>>>> dim addLogQuery as DAO.Querydef
>>>>
>>>> set addLogQuery = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS 
>>>> loginDate DateTime, adminName Text ( 25 ), actionText Text ( 255 );
>>>> INSERT INTO AdTrack (LogIn, Admin, Action)
>>>> VALUES ([loginDate], [adminName], [actionText]);")
>>>>
>>>> addLogQuery.Paramiters(0) = #2019/5/1/11#
>>>> addLogQuery.Paramiters(1) = "John Smith"
>>>> addLogQuery.Paramiters(2) = "Upload of email attachment failed at 
>>>> 11:30:00 AM:"
>>>>
>>>> addLogQuery.Execute
>>>> set addLogQuery = nothing
>>>>
>>> Thanks for your thoughts, Ron. However, I think the main point is being 
>>> missed. What I mean by "parameterized" is that all values that are 
>>> actually entered into the database by the program are specifically 
>>> formatted code sequences, and the process will not accept generic inserted 
>>> data for input. It is not possible to inject something into the database 
>>> externally.
>>>
>>> The data fields in use are 3 TEXT fields, no number or date fields. In my 
>>> tests, I determined whether it was the format of any of the content that 
>>> triggered the error. It was not.
>>>
>>> My above SQL sequence can be inserted into on-line SQL checker, such as:
>>> <https://www.eversql.com/sql-syntax-check-validator/>
>>> and the result is that it is 100% correct.
>>>
>>> And, without making any changes in the syntax, after reverting to the 
>>> "old" MS Access driver the process worked flawlessly as it had for over 20 
>>> years.
>>>
>>> The basic question I asked here is, what is it about the ACE.OLEDB driver 
>>> that is so quirky with MS-Access?
>> 
>> Grasping at straws here, but are the values you are showing in your example 
>> the actual values that are in the failing query?  Can you supply DDL for 
>> the table AdTrack so we can try for a repro?  i am just not seeing the 
>> problem.  Sorry :-(
>> 
>> Rdub
>> 
> No need to apologize...the problem is that there isn't a problem with the SQL 
> structure itself, it's in how the ACE.OLEDB driver handles it. ;-)
>
> In my tests, I've proven that the actual values entered into the field do not 
> change the outcome. It's an issue with how the driver wants the SQL statement 
> structured, but I have not found one source of information that suggests that 
> the way it is currently structured is incorrect.
>
> Another bizarre aspect, which is OT for this thread but not irrelevant, is 
> that the UPDATE and even complex SELECT commands work just fine with the 
> ACE.OLEDB driver, yet every instance of INSERT fails, sometimes with an error 
> message, but most of the time without one.

Hmmm...  Just had an Idea for you to try.  Change your Sql from:

NSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" 
& WhoAdm & "', '" & DidWhat & "');

To:

NSERT INTO AdTrack (LogIn, Admin, Action) SELECT '" & LogVal & "', '" & 
WhoAdm & "', '" & DidWhat & "';

Wonder if that will work????

Rdub

---
This email has been checked for viruses by AVG.
https://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13614

On 5/4/2019 3:56 PM, Ron Weiner wrote:
> Neil wrote on 5/4/2019 :
>> On 5/3/2019 8:51 PM, Ron Weiner wrote:
>>> Neil pretended :
>>>> On 5/3/2019 8:25 AM, Ron Paii wrote:
>>>>> On Thursday, May 2, 2019 at 4:45:54 PM UTC-5, Neil wrote:
>>>>>> On 5/2/2019 3:33 PM, Ron Paii wrote:
>>>>>>> On Thursday, May 2, 2019 at 8:56:12 AM UTC-5, Neil wrote:
>>>>>>>> We recently completed updating our Access database to .accdb 
>>>>>>>> format and
>>>>>>>> using the ACE.OLEDB driver. Last week, one of our tracking 
>>>>>>>> functions
>>>>>>>> began to fail with the message:
>>>>>>>>
>>>>>>>> Microsoft Access Database Engine error '80040e14'
>>>>>>>> Syntax error in INSERT INTO statement
>>>>>>>>
>>>>>>>> After testing, including checking the Microsoft documentation 
>>>>>>>> (the error
>>>>>>>> number is useless), the on-line SQL syntax verification that 
>>>>>>>> shows the
>>>>>>>> syntax to be 100% correct, and other sources, we are still 
>>>>>>>> getting the
>>>>>>>> error. The current syntax is:
>>>>>>>>
>>>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & 
>>>>>>>> "', '"
>>>>>>>> & WhoAdm & "', '" & DidWhat & "');
>>>>>>>>
>>>>>>>> Everything is simple plain text except LogVal, which is a date & 
>>>>>>>> time
>>>>>>>> compilation that is converted to text. Our own syntax checker 
>>>>>>>> that uses
>>>>>>>> the same content as the live version returns this result which 
>>>>>>>> appears
>>>>>>>> to be valid:
>>>>>>>>
>>>>>>>> INSERT INTO AdTrack (LogIn, Admin, Action) VALUES 
>>>>>>>> ('2019/5/1/11', 'John
>>>>>>>> Smith', 'Upload of email attachment failed at 11:30:00 AM: ');
>>>>>>>>
>>>>>>>> Any help and insights are appreciated.
>>>>>>>> -- best regards,
>>>>>>>>
>>>>>>>> Neil
>>>>>>>
>>>>>>> is LogIn expecting a date?
>>>>>>>
>>>>>> No.
>>>>>>
>>>>>>> ?isdate('2019/5/11') fails with Expected: expression
>>>>>>> ?isdate("2019/5/11") returns true
>>>>>>> ?isdate(#2019/5/11#) returns true
>>>>>>>
>>>>>>> Newer version of Access are pickier on data type.
>>>>>>>
>>>>>>> PS:
>>>>>>> Have your considered using named and typed parameters? The tend 
>>>>>>> to move the data errors into your code where they are easier to 
>>>>>>> trap.
>>>>>>>
>>>>>>
>>>>>> All of the values entered into the database are parameterized. I'm
>>>>>> primarily puzzled by the syntax failures of the ACE.OLEDB drivers vs.
>>>>>> the older Access drivers.
>>>>>>
>>>>>> -- best regards,
>>>>>>
>>>>>> Neil
>>>>>
>>>>> The query you posted is not parameterized.
>>>>> The following code will execute your query with the parameters 
>>>>> values properly typed and sized before the execute. You could also 
>>>>> save this query for faster execution.
>>>>>
>>>>> dim addLogQuery as DAO.Querydef
>>>>>
>>>>> set addLogQuery = currentdb.CreateQueryDef(vbnullstring, 
>>>>> "PARAMETERS loginDate DateTime, adminName Text ( 25 ), actionText 
>>>>> Text ( 255 );
>>>>> INSERT INTO AdTrack (LogIn, Admin, Action)
>>>>> VALUES ([loginDate], [adminName], [actionText]);")
>>>>>
>>>>> addLogQuery.Paramiters(0) = #2019/5/1/11#
>>>>> addLogQuery.Paramiters(1) = "John Smith"
>>>>> addLogQuery.Paramiters(2) = "Upload of email attachment failed at 
>>>>> 11:30:00 AM:"
>>>>>
>>>>> addLogQuery.Execute
>>>>> set addLogQuery = nothing
>>>>>
>>>> Thanks for your thoughts, Ron. However, I think the main point is 
>>>> being missed. What I mean by "parameterized" is that all values that 
>>>> are actually entered into the database by the program are 
>>>> specifically formatted code sequences, and the process will not 
>>>> accept generic inserted data for input. It is not possible to inject 
>>>> something into the database externally.
>>>>
>>>> The data fields in use are 3 TEXT fields, no number or date fields. 
>>>> In my tests, I determined whether it was the format of any of the 
>>>> content that triggered the error. It was not.
>>>>
>>>> My above SQL sequence can be inserted into on-line SQL checker, such 
>>>> as:
>>>> <https://www.eversql.com/sql-syntax-check-validator/>
>>>> and the result is that it is 100% correct.
>>>>
>>>> And, without making any changes in the syntax, after reverting to 
>>>> the "old" MS Access driver the process worked flawlessly as it had 
>>>> for over 20 years.
>>>>
>>>> The basic question I asked here is, what is it about the ACE.OLEDB 
>>>> driver that is so quirky with MS-Access?
>>>
>>> Grasping at straws here, but are the values you are showing in your 
>>> example the actual values that are in the failing query?  Can you 
>>> supply DDL for the table AdTrack so we can try for a repro?  i am 
>>> just not seeing the problem.  Sorry :-(
>>>
>>> Rdub
>>>
>> No need to apologize...the problem is that there isn't a problem with 
>> the SQL structure itself, it's in how the ACE.OLEDB driver handles it. 
>> ;-)
>>
>> In my tests, I've proven that the actual values entered into the field 
>> do not change the outcome. It's an issue with how the driver wants the 
>> SQL statement structured, but I have not found one source of 
>> information that suggests that the way it is currently structured is 
>> incorrect.
>>
>> Another bizarre aspect, which is OT for this thread but not 
>> irrelevant, is that the UPDATE and even complex SELECT commands work 
>> just fine with the ACE.OLEDB driver, yet every instance of INSERT 
>> fails, sometimes with an error message, but most of the time without one.
> 
> Hmmm...  Just had an Idea for you to try.  Change your Sql from:
> 
> NSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" & 
> WhoAdm & "', '" & DidWhat & "');
> 
> To:
> 
> NSERT INTO AdTrack (LogIn, Admin, Action) SELECT '" & LogVal & "', '" & 
> WhoAdm & "', '" & DidWhat & "';
> 
> Wonder if that will work????
> 
> Rdub
> 
I'm not clear as to what the "SELECT" portion is doing...but it 
validates in the on-line SQL checker. I'll give it a try and see what 
happens.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13615

On 5/4/2019 3:56 PM, Ron Weiner wrote:
> Neil wrote:
> 
> Hmmm...  Just had an Idea for you to try.  Change your Sql from:
> 
> NSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" & 
> WhoAdm & "', '" & DidWhat & "');
> 
> To:
> 
> NSERT INTO AdTrack (LogIn, Admin, Action) SELECT '" & LogVal & "', '" & 
> WhoAdm & "', '" & DidWhat & "';
> 
> Wonder if that will work????
> 
> Rdub
> 
Well, although that syntax fails in the same way as the others, your 
discussion of this made a light bulb come on in my head! I think the 
syntax error message was ambiguous, in that it is probably not the SQL 
syntax but it just might be the *command* structure. I've been leaving 
the ADO commands in the routines. So, the "syntax error" might be that 
ACE.OLEDB 12.0 can't handle the ADO command structures.

So, I'm looking for a good reference. The Microsoft pages on OLEDB 
command structures drive me nuts because they're so inefficiently 
organized and presented in small, unrelated chunks on separate web 
pages. Our site is also based on classic ASP, so the DOT.NET references 
are of little use too. I'd rather not have to convert thousands of 
routines to DOT.NET just to get this done.

Thanks for your thoughts! They were quite helpful.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13616

Neil presented the following explanation :
> On 5/4/2019 3:56 PM, Ron Weiner wrote:
>> Neil wrote:
>> 
>> Hmmm...  Just had an Idea for you to try.  Change your Sql from:
>> 
>> NSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" & 
>> WhoAdm & "', '" & DidWhat & "');
>> 
>> To:
>> 
>> NSERT INTO AdTrack (LogIn, Admin, Action) SELECT '" & LogVal & "', '" & 
>> WhoAdm & "', '" & DidWhat & "';
>> 
>> Wonder if that will work????
>> 
>> Rdub
>> 
> Well, although that syntax fails in the same way as the others, your 
> discussion of this made a light bulb come on in my head! I think the syntax 
> error message was ambiguous, in that it is probably not the SQL syntax but it 
> just might be the *command* structure. I've been leaving the ADO commands in 
> the routines. So, the "syntax error" might be that ACE.OLEDB 12.0 can't 
> handle the ADO command structures.
>
> So, I'm looking for a good reference. The Microsoft pages on OLEDB command 
> structures drive me nuts because they're so inefficiently organized and 
> presented in small, unrelated chunks on separate web pages. Our site is also 
> based on classic ASP, so the DOT.NET references are of little use too. I'd 
> rather not have to convert thousands of routines to DOT.NET just to get this 
> done.
>
> Thanks for your thoughts! They were quite helpful.

Can you post the Connection string are using to connect your app to the 
Access Db, and the code your app is executing the sql?

I am pretty sure that ACE.OLEDB does work with ADO, but without seeing 
your apps code that ececutes the query, and the Connection string you 
are using, it is impossible from me to make an attempt at a Repro.

Rdub

---
This email has been checked for viruses by AVG.
https://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13619

On 5/6/2019 2:46 PM, Ron Weiner wrote:
> 
> Can you post the Connection string are using to connect your app to the 
> Access Db, and the code your app is executing the sql?
> 
> I am pretty sure that ACE.OLEDB does work with ADO, but without seeing 
> your apps code that ececutes the query, and the Connection string you 
> are using, it is impossible from me to make an attempt at a Repro.
> 
> Rdub
> 

Connection String:
     Dst = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & 
Server.MapPath("\[to dB file]\dBFile.accdb") & ";"

Command Syntax:
     rsx = "INSERT INTO [dBFile] (LogIn, Admin, Action) VALUES ('" & 
LogVal & "', '" & WhoAdm & "', '" & DidWhat & "') & ";"

     set DoConn = Server.CreateObject("ADODB.Connection")
     DoConn.ConnectionString = Dst
     DoConn.open

     Set DoCmd=Server.CreateObject("ADODB.command")
     WITH DoCmd
         .CommandText=rsx
         .CommandType=1
         set .ActiveConnection=DoConn
     END WITH

     DoCmd.Execute , , 128

     DoConn.Close
     set DoConn=Nothing
     set DoCmd=Nothing


-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13620

That is a lot of work to insert a row into a table. The following function can do it it 4 lines (1st example) using DAO.


Public Sub InsertLine(ByVal LogVal As String, ByVal WhoAdm As String, ByVal DidWhat As String)
    On error GoTo errInsertLine
    Dim qryInsert As DAO.QueryDef ' DAO must be included if also using ADO in application
    
    ' If dbFile is linked
    Set qryInsert = CurrentDb.CreateQueryDef(vbNullString, _
         "INSERT INTO [dBFile] (LogIn, Admin, Action) " & _
         "VALUES ('" & LogVal & "', '" & WhoAdm & "', '" & DidWhat & "');")
    qryInsert.Execute dbFailOnError
    
    ' Using parameters
    Set qryInsert = CurrentDb.CreateQueryDef(vbNullString, _
         "PARAMETERS LogVal Text ( 25 ), WhoAdm Text ( 25 ), DidWhat Text ( 255 ); " & _
         "INSERT INTO [dBFile] (LogIn, Admin, Action) " & _
         "VALUES ([LogVal], [WhoAdm], [DidWhat]);")
    qryInsert.Parameters(0) = LogVal
    qryInsert.Parameters(1) = WhoAdm
    qryInsert.Parameters(2) = DidWhat
    qryInsert.Execute dbFailOnError
    
    ' Not linked but access BE table using parameters
    Set qryInsert = CurrentDb.CreateQueryDef(vbNullString, _
       "PARAMETERS LogVal Text ( 25 ), WhoAdm Text ( 25 ), DidWhat Text ( 255 ); " & _
       "INSERT INTO [\\Server\Path\DBName.mdb].[dbFile] (LogIn, Admin, Action) " & _
       "VALUES ([LogVal], [WhoAdm], [DidWhat]);")
    qryInsert.Parameters(0) = LogVal
    qryInsert.Parameters(1) = WhoAdm
    qryInsert.Parameters(2) = DidWhat
    qryInsert.Execute dbFailOnError
    
doneInsertLine:
    On Error Resume Next
    Exit Sub
    
errInsertLine:
    Debug.Print Err.Description
    Resume doneInsertLine
End Sub

[toc] | [prev] | [next] | [standalone]

#13621

On 5/7/2019 9:32 AM, Ron Paii wrote:
> That is a lot of work to insert a row into a table. The following function can do it it 4 lines (1st example) using DAO.
> 
(snipped for brevity)

Thanks for your thoughts, Ron. However, to use your 4-line solution, its 
unique SUB structure means I'd have to create over 40 instances of it on 
the site, whereas the structure that I presented requires *only one* 
instance because the connection strings and commands for all functions, 
including UPDATE, SELECT etc. are submitted as parameter variables by 
the routines accessing the various databases.

I'd really like to get back to the problem at hand.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13622

On Tuesday, May 7, 2019 at 9:49:45 AM UTC-5, Neil wrote:
> On 5/7/2019 9:32 AM, Ron Paii wrote:
> > That is a lot of work to insert a row into a table. The following function can do it it 4 lines (1st example) using DAO.
> > 
> (snipped for brevity)
> 
> Thanks for your thoughts, Ron. However, to use your 4-line solution, its 
> unique SUB structure means I'd have to create over 40 instances of it on 
> the site, whereas the structure that I presented requires *only one* 
> instance because the connection strings and commands for all functions, 
> including UPDATE, SELECT etc. are submitted as parameter variables by 
> the routines accessing the various databases.
> 
> I'd really like to get back to the problem at hand.
> 
> -- 
> best regards,
> 
> Neil

Add a parameter to the sub to pass it the location of the BE file. 

PS: It concerns me that you have 40 database files accepting this type of input from an Access application.

[toc] | [prev] | [next] | [standalone]

#13623

On 5/7/2019 10:54 AM, Ron Paii wrote:
> On Tuesday, May 7, 2019 at 9:49:45 AM UTC-5, Neil wrote:
>> On 5/7/2019 9:32 AM, Ron Paii wrote:
>>> That is a lot of work to insert a row into a table. The following function can do it it 4 lines (1st example) using DAO.
>>>
>> (snipped for brevity)
>>
>> Thanks for your thoughts, Ron. However, to use your 4-line solution, its
>> unique SUB structure means I'd have to create over 40 instances of it on
>> the site, whereas the structure that I presented requires *only one*
>> instance because the connection strings and commands for all functions,
>> including UPDATE, SELECT etc. are submitted as parameter variables by
>> the routines accessing the various databases.
>>
>> I'd really like to get back to the problem at hand.
>>
>> -- 
>> best regards,
>>
>> Neil
> 
> Add a parameter to the sub to pass it the location of the BE file.
> 
> PS: It concerns me that you have 40 database files accepting this type of input from an Access application.
> 
I don't have 40 "database files" at all. What gave you that idea? There 
are over 40 routines that access the database files that are on the site.

Ron, I don't wish to go through the side-track of why things are as they 
are on the site, since it has NOTHING to do with the curious syntax 
error. As I wrote numerous times now, the functions have worked reliably 
on the site for over 20 years.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13624

On Tuesday, May 7, 2019 at 10:17:14 AM UTC-5, Neil wrote:
> On 5/7/2019 10:54 AM, Ron Paii wrote:
> > On Tuesday, May 7, 2019 at 9:49:45 AM UTC-5, Neil wrote:
> >> On 5/7/2019 9:32 AM, Ron Paii wrote:
> >>> That is a lot of work to insert a row into a table. The following function can do it it 4 lines (1st example) using DAO.
> >>>
> >> (snipped for brevity)
> >>
> >> Thanks for your thoughts, Ron. However, to use your 4-line solution, its
> >> unique SUB structure means I'd have to create over 40 instances of it on
> >> the site, whereas the structure that I presented requires *only one*
> >> instance because the connection strings and commands for all functions,
> >> including UPDATE, SELECT etc. are submitted as parameter variables by
> >> the routines accessing the various databases.
> >>
> >> I'd really like to get back to the problem at hand.
> >>
> >> -- 
> >> best regards,
> >>
> >> Neil
> > 
> > Add a parameter to the sub to pass it the location of the BE file.
> > 
> > PS: It concerns me that you have 40 database files accepting this type of input from an Access application.
> > 
> I don't have 40 "database files" at all. What gave you that idea? There 
> are over 40 routines that access the database files that are on the site.
> 
> Ron, I don't wish to go through the side-track of why things are as they 
> are on the site, since it has NOTHING to do with the curious syntax 
> error. As I wrote numerous times now, the functions have worked reliably 
> on the site for over 20 years.
> 
> -- 
> best regards,
> 
> Neil

Sorry I misunderstood when you posted "I'd have to create over 40 instances on the site". Do you have multiple applications using a common Access BE? If so are all the applications Access? I have use OLEDB for non Access application and found some SQL syntax differences from Access/DAO.  Your reply to Ron Weiner included code to create a connect and command to add the record, how do you distribute it to your applications?

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.databases.ms-access

csiph-web