Coming from an Access97 environment, I’m getting a bit fed up with ADO… I’d really appreciate it if anyone can help me with this…
I have a VB6 project that uses ADO to connect to an Access97 database. Recently, while trying to get my complied application to run on another machine, I discovered that, because I was using MDAC 2.1 I had to use Jet.OLEDB.4.0 in my connection strings (I had been using 3.51). That solved that problem.
However, now I have noticed that some of my data grids are not working properly even though they worked fine before. I create a ADO recordset and set the datagrid’s recordsource property etc, this works OK, I know that the recordset is returning rows but these rows are not displayed in my datagrid.
I’ve tried changing every aspect of the code I can think of but nothing works.
Here’s [some of] the code as I have left it at present – see what you think! (I know that some of the code is unnecessary but I’ve tried everything.
===== The main connection object is created and opened in another module (this works ok)
Set ConnMain = New ADODB.Connection
ConnMain.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\OPg\OPg.mdb"
ConnMain.Open
===== Then, within my FIND form’s code, I declare the recordset…
Public rstFIND As ADODB.Recordset
===== And, when the user has supplied search criteria and clicks a command button to return the results in the datagrid this code executes…
Private Sub cmdFind_Click()
On Error GoTo EH
Dim strSQL As String
strSQL = BuildSQLstring ‘(This function returns an SQL string using the user-supplied criteria)
Set rstFIND = New ADODB.Recordset
rstFIND.Open strSQL, ConnMain, adOpenStatic, adLockReadOnly
ShowResults ‘(this procedure should display the results in the datagrid… but doesn’t!)
Exit Sub
EH:
If Err.Number = 3021 Then
lblSearchResults.Caption = "SEARCH RESULTS: No records found"
Else
MsgBox Err.Description & vbCr & Err.Number, vbCritical
End If
End Sub
===== This is where it goes wrong… the recordset returns rows, the number of rows returned is displayed and the datagrid shows the added columns but no rows are displayed…
Public Sub ShowResults()
Dim icounter As Integer
rstFIND.MoveFirst
rstFIND.MoveLast
lblSearchResults.Caption = "SEARCH RESULTS: " & rstFIND.RecordCount & " records found"
'Add extra columns to the datagrid (has 2 by default)…
For icounter = 1 To rstFIND.Fields.Count - 2
ResultsDataGrid.Columns.Add 0
Next icounter
'Set the datafields
Set ResultsDataGrid.DataSource = rstFIND
For icounter = 0 To rstFIND.Fields.Count - 1
ResultsDataGrid.Columns(icounter).DataField = rstFIND.Fields(icounter).Name
Next icounter
ResultsDataGrid.Refresh
End Sub
Anyone? Please!
