Re: Run-time error 3709

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Fox,

What is the value of this?

..ActiveConnection = Gn

Is Gn a form control? If so, it would be good to use Me.Gn to make that clear


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



fox wrote:
Hi!
I add the code into the module to check the SQL. Make a new query from the strSQL print. The SQL query run well in SQL Windows. The Open line should be correct. Any idea why cause 3709 error?
Thank you very much.

fox

"strive4peace" wrote:

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: Run-time error 3709
    ... Hi Fox ... dim strSQL as string ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • Re: Run-time error 3709
    ... I add the code into the module to check the SQL. ... the strSQL print. ... The SQL query run well in SQL Windows. ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL statement is truncated.
    ... When you concatenating string for the SQL ... > tried the strSQL as a variant and as a string. ... > along with this Excel. ...
    (microsoft.public.data.ado)
  • Re: Calculated Mean
    ... As I have asked a number of times, you need to copy what you have in the SQL ... window and paste it back here in a message. ... On the Query screen, I clicked New, then> Design View, then SQL. ... I tried it again this>>> morning and got the following error messages: ...
    (microsoft.public.access.queries)
  • Re: How to ignore extra or missing columns?
    ... execute the SQL statement for an append query that gets data from the ... through the two collections matching the field names and building a SQL ... Dim strSQL As String ...
    (microsoft.public.access.externaldata)