ADO, Access 2000, and Data Grid

Hi,
I originally posted this in the General ? section, and was recommended I try here.

Thanks in advance.
JazzBass

Hi,
I'm really new to ADO and database programming in general, so I'm really trying to learn.

I have a program with an Access 2000 database. I can get this code to work with an Access 97 database,
but for whatever reason it can't work with 2000.

When the form loads, a connection is established and queries the data base to load all distinct States and
loads them into a combo box.

When the user chooses a state, code is run to return the results.

With the 97 version, the datagrid is updated without a problem. In 2000 the code is run and records are retrieved,
but the datagrid is not updated.

I have tried referencing the MSADO 2.0 and 2.1, and DAO 3.6 libraries and that has not changed anything.

I'm using VB6 Pro with SP4 and the MS Datagrid Control 6.0 (SP4)


Any ideas?
Thanks in advance,
JazzBass

Code:

---


Private Sub Form_Load()

Dim rsDistinctState As New ADODB.Recordset
Set cn = New ADODB.Connection
' The ConnectionString contains the path of the 'database.   
   
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source= D:\MYDB.MDB"
        .Open
    End With


sSql = "Select distinct State from Sites"

rsDistinctState.Open sSql, cn, adOpenStatic, adLockOptimistic
Debug.Print rsDistinctState.RecordCount
Do Until rsDistinctState.EOF
   
cmbStates.AddItem IIf(IsNull(rsDistinctState &_
"("Site_State")), "", rsDistinctState("Site_State"))
rsDistinctState.MoveNext
DoEvents
Loop

End Sub


Private Sub cmbStates_Click()
    Dim rsState As New ADODB.Recordset
    Dim sSql As String
   
    sSql = "Select Distinct * from SITES WHERE STATE = " &_
& "'" & cmbStates.Text & "'"
   
    ' Open recordset.
    rsState.Open sSql, cn, adOpenStatic, adLockOptimistic
   
    Debug.Print rsState.RecordCount
   
    Set DataGrid1.DataSource = rsState
   
    DataGrid1.Refresh

End Sub

---

I am not sure if you can do it, but can you use DataGrid1.Requery instead of DataGrid1.Refresh? If you can, that should update the table properly. Let me know if you can't do that and I'll do a little research.

Good Luck!

I doubt if the datagrid will work with Access 2000.

I am not too sure, but I think it won't work, because many bound controls, and even the addins shipped with VB are not compatible with Access 2000.

You can use ADO with Access 2000 to create a recordset, and then use this recordset as the datasource for your bound controls.