what is wrong with this access connection?

method · April 27th, 2005, 10:45 AM

hi guys. I wrote this code but i keep geting error under the connection. I be happy if some one help me .How i can print the out put in masage box?Thanks

My code:

Private Sub cmdSelect_Click()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cn.Open("Provider=MicroSoft.Jet.OLEDB.4.0;User ID=Admin;â & _
             âData Source=C:\MSID\ApGe0203\tennis2000.mdb")

rst.Open "select playerno, initials, name from players order by name, initials", cn
rst.MoveFirst
Do Until rst.EOF
    lstPlayers.RowSource = lstPlayers.RowSource & ";" & _
                                     rst("playerno") & ";" & rst("initials") & ";" & rst("name")
    rst.MoveNext
Loop
rst.Close
cn.Close
End Sub

BrianWren · April 27th, 2005, 01:38 PM

To get the text of the error, add the following:

Code:

Private Sub cmdSelect_Click()

    On Error GoTo Er

    Dim cn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cn.Open("Provider=MicroSoft.Jet.OLEDB.4.0;User ID=Admin;" & _
            "Data Source=C:\MSID\ApGe0203\tennis2000.mdb")

   rst.Open "SELECT playerno, initials, name FROM players ORDER BY name, initials", cn
  <s> rst.MoveFirst </s>      ' <ââ<<< This is unnecessary; recordsets [u]always</u> open 
                       '        to the first record, if there are any rec-
                       '        ords returned by the select statement.
   Do Until rst.EOF
     lstPlayers.RowSource = lstPlayers.RowSource & ";" & _
                            rst("playerno") & ";" & rst("initials") & ";" & rst("name")
     rst.MoveNext
   Loop

   rst.Close
   cn.Close

Rs: Exit Sub

Er: Debug.Print Err.Number & ", """ & Err.Description & """"
    MsgBox Err.Number & ", """ & Err.Description & """", vbCritical, "Error"
    Resume Rs        ' Add a breakpoint here. 

End Sub

Once you have run this, post another message with the error description. (You can place it on the clipboard from the debug window.)