Re: Syntax error in INSERT INTO Statement

Thanks Foster,

This worked! I dont know what was wrong in my code
because I have always used it that way earlier but the
point is your suggestion worked. Thanks a lot.

I will investigate the problem with my code later on when
I have time.

Cheers
Hemil.
>-----Original Message-----
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Since you're not using any Access tables, instead of
running the query
>against the linked table, try an SQL pass-thru (SPT)
query. I usually
>keep a QueryDef set up as an SPT and stuff it w/ an SQL
statement & then
>execute it. E.g.:
>
>strSQL = ".... the SQL statement..."
>
>dim db as dao.database
>dim qd as dao.querydef
>set db = currentdb
>set qd = db.querydefs("query name")
>qd.SQL = strSQL
>qd.Execute dbFailOnError
>
>--
>MGFoster:::mgf00 <at> earthlink <decimal-point> net
>Oakland, CA (USA)
>
>-----BEGIN PGP SIGNATURE-----
>Version: PGP for Personal Privacy 5.0
>Charset: noconv
>
>iQA/AwUBQlJFOoechKqOuFEgEQJjYQCfcRZwuZhfMeX5ojsjVwB/KpBHYT
8An19S
>EkGAtj7r2IosltH11J4uWw8I
>=aI5h
>-----END PGP SIGNATURE-----
>
>Hemil wrote:
>> Hi,
>>
>> My application has an access front-end and a SQL Server
>> back-end.
>>
>> I am trying to use an Insert Statement as following:
>>
>> sql = "INSERT INTO OrderNote"
>>
>> sql = sql + " (OrderTypeCode, [LineNo], NoteSeqNo,
>> PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement,
Note,
>> OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)"
>>
>> sql = sql + " Values ('" + NtRecId + "', " +
>> NtCustPOLineNo + ", " + NtSeqNo + ", '" + PrintOnAck
>> + "', '" + PrintOnPickSlip + "', '" + PrintOnInv
+ "', '"
>> + PrintOnSt + "', '" + MsgTxt + "', '" + OrderHeaderId
>> + "' ,'" + OrderDetailId + "', '" + LnUserDt1 + "', '"
>> + "TextFileImport')"
>>
>> Debug.Print sql
>> db.Execute sql
>>
>> I get a "Syntax error in INSERT INTO Statement error"
on
>> the code "db.Execute sql".
>>
>> When I find the value of ?sql in the immediate window I
>> get:
>>
>> INSERT INTO OrderNote (OrderTypeCode, [LineNo],
NoteSeqNo,
>> PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement,
Note,
>> OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)
>> Values ('IN', 001, 0001, 'N', 'Y', 'Y', 'N', 'HALF FEED
>> COILS. REBATE ITEM 1768 - 125 =
>> 1643 ', '158' ,'171', '05/04/2005', 'TextFileImport')
>>
>> The above insert statement works perfectly alright when
>> used from SQL Server Query Analyser. But it gives the
>> error when executed from the access application.
>>
>> I know that the column 'Note' is causing the problem
>> because the insert statement works fine from the access
>> application if I remove the field 'Note' and the
>> corresponding variable 'MsgTxt'.
>>
>> The column note in the database is of datatype varchar
(50)
>> and the variable MsgTxt is declared as a 'String' in
the
>> access code. I know this is where it is breaking but
dont
>> understand why and how to resolve it.
>>
>> Please help. Any ideas will be appreciated.
>>
>> Thanks & Regards,
>> Hemil.
>.
>
.

Relevant Pages

  • Re: ADO teething problems
    ... Try inserting or pasting more data ... ... like the SQL of the Insert Query. ... As you wish - although i dont think it will help much. ...
    (comp.databases.ms-access)
  • CSV Download
    ... I have created a script which connects to a SQL DB, runs a query, writes the ... results out to a file (Using FSO) then presents the user with a link to ... As the file contains sensitive data, I dont want to leave the file on the ...
    (microsoft.public.inetserver.asp.general)
  • Re: Syntax error in INSERT INTO Statement
    ... > My application has an access front-end and a SQL Server ... > OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)" ... > INSERT INTO OrderNote (OrderTypeCode, [LineNo], NoteSeqNo, ... > and the variable MsgTxt is declared as a 'String' in the ...
    (microsoft.public.access.formscoding)
  • RE: MS Access Query using calculated field - Access ignores criteria.
    ... SQL from your query and I'll take a look. ... record I want excluded does not exist in the LastOfBudgetLimit output query ... So dont understand why that would be a problem. ...
    (microsoft.public.office.developer.automation)
  • Syntax error in INSERT INTO Statement
    ... My application has an access front-end and a SQL Server ... PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement, Note, ... OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)" ... INSERT INTO OrderNote (OrderTypeCode,, NoteSeqNo, ...
    (microsoft.public.access.formscoding)