Simple ADO Recordset, Run Time Error: 3709

Posted on 2004-11-23
Last Modified: 2009-07-29
Hello Experts,

I am having trouble with what I think is a simple ADO recordset. I am simply trying to count the number of records.

This code works fine:
Dim rstBooks As New ADODB.Recordset

    rstBooks.Open "SELECT * FROM [Books] WHERE [CategoryID]=2", CurrentProject.Connection, adOpenStatic, adLockReadOnly

    MsgBox rstBooks.RecordCount

HOWEVER, when I modify the code so that I can type in the CategoryID from a InputBox
the code fails on the "rstBooks.Open" line.  "RunTime Error: 3709 The connection can not be used to perfrom this operation. It is either closed or invalid in this context.".
Here is the code that fails:

Dim rstBooks As New ADODB.Recordset
Dim intWhatCategory As Integer
    intWhatCategory = InputBox("What Category: 1,2,3 or 4")
    rstBooks.Open "SELECT * FROM [Books] WHERE [CategoryID]= " & intWhatCategory & ", CurrentProject.Connection, adOpenStatic, adLockReadOnly"
    MsgBox rstBooks.RecordCount

I have used the syntax: " & VariableName & "  in other procedures before and it works fine. Why is it failing now?

Question by:Jeffrey Coachman
    LVL 2

    Accepted Solution


    Try removing the "" marks after the variable, i.e. Change this:

    rstBooks.Open "SELECT * FROM [Books] WHERE [CategoryID]= " & intWhatCategory & ", CurrentProject.Connection, adOpenStatic, adLockReadOnly"

    To this:

    rstBooks.Open "SELECT * FROM [Books] WHERE [CategoryID]= " & intWhatCategory, CurrentProject.Connection, adOpenStatic, adLockReadOnly

    I think this is where your error is coming from... if not please let me know.

    LVL 74

    Author Comment

    by:Jeffrey Coachman
    Thanks for your speedy reply,

    You were correct.

    Funny thing though, when I left both quotes around the variable name, VBA put a closing " at the end of adLockReadOnly.

    So when I deleted the closing quote at the end of the Variable it still gave me errors. I missed seeing the quotes at the end of the line!

    Thanks again!

    Works fine

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now