Run-time error 3709

[fox]

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

 

[strive4peace]

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]

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

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
*

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

 

[strive4peace]

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
*

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

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
*

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

 

[fox]

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

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
*

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

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

 

[strive4peace]

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
*

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

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
*

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

:

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