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

Groups > comp.databases.ms-access > #13614

Re: OLEDB syntax problems

Show all headers | View raw

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

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web