Re: Run-time error 3709

debug.print strSQL
---


Hi Fox

assign the SQL statement to a variable

dim strSQL as string
strSQL = "SELECT ..."
debug.print strSQL

then use the variable in your Open statement

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



fox wrote:
Hi!
AstrKey is from the form VBA code, and the function is in a module. I check the Astrkey value in watch window, and it show the correct string, but running this line still popup 3709 error. The value in SCtl table is set "text"

Any advice?
Thank you.

fox

"strive4peace" wrote:

Hi Fox,

where does AstrKey get its value? Are you behind a form with that controlname? You have no variables and since AstrType did not seem to have a poblem, I am assuming you are behind a form...if so, then check the NAME property of what you are trying to reference

Also, check the DATA TYPE of CKey in your SCtl table

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



fox wrote:
Hi!
I am try to debug a program, but I can't figure out the problem. When I call this function, it show "Run-time error 3709 The connection cannot be used to perform this operation." Please tell me where could be wrong. Thank you very much.

fox

The error happen at " And [CKey] = '" & AstrKey & "'"

Here is the function.
Dim rs As ADODB.Recordset
Set rs = new ADODB.Recordset
With rs
.ActiveConnection = Gn
.Open Source:="Select * " & _
"From [SCtl] " & _
" Where [CType] = '" & AstrType & "' " & _
" And [CKey] = '" & AstrKey & "'"
If Not .EOF Then
.MoveFirst
GetID = rs![ID].Value
Else
GetID = 0
End If
End With
rs.Close
Set rs = Nothing
.

Relevant Pages

  • Re: SQL string problem
    ... clean up your parameters before sending them to SQL. ... > You must "escape" any single quotes when sending SQL statements ... > Just pass your SQL statement through this function when opening your ... >> Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)
  • Re: SQL string problem
    ... You must "escape" any single quotes when sending SQL statements directly to ... Just pass your SQL statement through this function when opening your ... > Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)
  • Re: Run-time error 3709
    ... fox wrote: ... I add the code into the module to check the SQL. ... Make a new query from the strSQL print. ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem updating memo field with string data from form
    ... Im just wondering if the syntax of the Update section of the SQL ... the SQL statement ... Docmd.RunSQL strSQL ... Bob Quintal ...
    (comp.databases.ms-access)
  • Re: SQL TOP question
    ... Therefore you will need to generate the SQL statement dynamically. ... Assuming you have created a query named Query1 that contains the right syntax, switch it to SQL View. ... Dim strSql As String ... we base the team award on the top 3 scores. ...
    (microsoft.public.access.queries)