Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Unanswered: Run Time Error 3709

    Hi,
    My application keeps showing 'The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    Dim UserNameCheck As String
    Dim adoConnection As ADODB.Connection
    Dim adoRecordset As ADODB.Recordset
    Dim connectString As String
    Dim RecordSQL As String

    UserNameCheck = cboxUserName.Text

    RecordSQL = "SELECT * From Login WHERE UserName=" & UserNameCheck"
    Set adoConnection = New ADODB.Connection
    Set adoRecordset = New ADODB.Recordset
    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Eagle Rights Database.mdb"
    adoConnection.Open connectString

    adoRecordset.Open "RecordSQL, adoConnection"

    Im quite new to VB so any help would be greatly appreciated.
    Thanks

  2. #2
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    Try this

    RecordSQL = "SELECT * From Login WHERE UserName=" & UserNameCheck"
    Set adoConnection = New ADODB.Connection
    Set adoRecordset = New ADODB.Recordset

    adoConnection.connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Eagle Rights Database.mdb"
    adoConnection.Open
    adoRecordset.Open RecordSQL, adoConnection

  3. #3
    Join Date
    Jan 2005
    Posts
    6

    Thanks...

    Thanks for your response. I tried what you suggested but now I get a syntax error on the following line.

    RecordSQL = "SELECT * From Login WHERE UserName=" & UserNameCheck"

    Any ideas? Is there a " missing or one too many?

    Many thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    it looks like you have to slightly rewrite this line

    try
    RecordSQL = "SELECT * From Login WHERE UserName='" & UserNameCheck & "'"
    that should probably sort it for you

    Dave

  5. #5
    Join Date
    Jan 2005
    Posts
    6

    Making Progress ...

    Thanks Dave,
    At least my application got past that line.
    Its currently now stuck on this one.

    adoConnection.connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Eagle Rights Database.mdb"

    Error message now 'Run Time Error 46' - Object Doesnt Support Named Arguements. Any ideas?

    Many, many thanks.

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    you need to be using connectionstring instead of Connectstring

    so your code should read something like this

    Code:
    Sub yourSubName()
    Dim UserNameCheck As String
    Dim adoConnection As ADODB.Connection
    Dim adoRecordset As ADODB.Recordset
    Dim connectString As String
    Dim RecordSQL As String
    
        'pick up username from the combobox
        UserNameCheck = cboxUserName.Text
        
        'sql to pick up all instances of the user
        RecordSQL = "SELECT * From Login WHERE UserName='" & UserNameCheck & "'"
        
        'apply new instances of the objects
        Set adoConnection = New ADODB.Connection
        Set adoRecordset = New ADODB.Recordset
        'open a connection to the eagle rights database
        adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                            "Data Source=C:\Eagle Rights Database.mdb"
        adoConnection.Open
        'fill recordset
        adoRecordset.Open RecordSQL, adoConnection
        
        'set object references to nothing
        Set adoConnection = Nothing
        Set adoRecordset = Nothing
    End Sub
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •