[AccessD] SQL INSERT Question

Well, you wrote the book. I'll forward to client and let you know what
happens.

Thanks

Rocky





 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Wednesday, January 23, 2008 12:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL INSERT Question

The easiest way to find a syntax error is to define a string variable to
hold the evaulated statement and then run a Debug.Print -- it is such a good
idea that I never run an evaluated string any other way

strSQL = "INSERT INTO tEvictionCasesTemp1 ( UDCaseID, Note ) " & _
>                 "SELECT " & RS(0) & ", '" & RS(10) & "'"
Debug.Print strSQL
DoCmd.RunSQL strSQL

IF you can't find the error by viewing it in the Immediate window, paste it
into a SQL window and run it to get more specific clues as to the real
problem.

BTW, INSERT INTO needs a FROM clause:

INSERT INTO target
SELECT *
FROM source

Using this syntax, target and source may share the same structure.

Susan H.

DoCmd.RunSQL "INSERT INTO tEvictionCasesTemp1 ( UDCaseID, Note ) " & _
>                 "SELECT " & RS(0) & ", '" & RS(10) & "'"


----- Original Message -----
From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Wednesday, January 23, 2008 2:07 PM
Subject: [AccessD] SQL INSERT Question


>I have a client who write a lot of his own stuff.  Right now he's trying to
> insert into an Access table memo field using the following:
>
> DoCmd.RunSQL "INSERT INTO tEvictionCasesTemp1 ( UDCaseID, Note ) " & _
>                 "SELECT " & RS(0) & ", '" & RS(10) & "'"
>
> which generates the error: "Syntax error in INSERT INTO statement"
>
> RA(n) are the fields in a recordset hooked to SQL database.  The fields
> RS(0) and RS(10) contain god data (he checked) with no embedded quotes or
> apostrophes. Although he says that he needs to be able to
>
>
> The target field Note is a memo field in the Access table. RS(0) has 
> already
> been successfully inserted into tEvictionCasesTemp1.
>
> What might be causing the syntax error?
>
> MTIA
>
> Rocky
>
>
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.516 / Virus Database: 269.19.8/1236 - Release Date: 1/21/2008
8:23 PM