Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2004

    Unanswered: ADO recordset for unbound form

    I am using using the following code to open a connection and pull a recordset. I have used this successfully (well, very similar code) with SQL server and it works ok, but now I am trying to use it with MySQL. I want to be able to use the recordset as the recordset for an access form, but for some reason I keep getting an error "Item cannot be found in collection corresponding to the name or ordinal". I am not sure what is going on.. Any help would be appreciated.

    Private Sub Form_Activate()
       Dim conn As ADODB.Connection
         Dim rs As ADODB.Recordset
         Dim fld As ADODB.Field
         Dim sql As String
         Dim mystr As String
         mystr = InputBox("Enter a value")
       'sql = "SELECT * FROM tblcaseissue WHERE MATCH (problem) AGAINST " + "(" + "'" + mystr + "'" + ")"
       sql = "SELECT * FROM tblcaseissue WHERE MATCH (problem) AGAINST ('matrox')"
         'connect to MySQL server using MySQL ODBC 3.51 Driver
         Set conn = New ADODB.Connection
         Set rs = New ADODB.Recordset
         conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
     					 & "SERVER=localhost;" _
     					 & "DATABASE=trenton;" _
     					 & "UID=root;" _
     					 & "PWD=;" _
     					 & "OPTION=3"
         With rs
         rs.Open sql, conn, adOpenKeyset, adLockOptimistic
       	  Debug.Print !Case_Number
       	   Set Me.Recordset = rs
       	End With
         Set rs = Nothing
         Set conn = Nothing
       End Sub
    The debug.print statement does show a value, so I am assuming that I am pulling a recordset.. I just cannot seem to make it be the form's recordset.
    Last edited by Bbender27; 08-09-04 at 11:37.

Posting Permissions

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