DBGrid Refresh of Records

[MudManiac]

I've been working on this darn problem for over a week can't figure it out. I am updating an older program from a DAO database to an Access 2000 ADO formatted database. I have a grid on a form (Microsoft Data Bound Grid Control 5.0 - SP-3: DBGrid32.OCX) which is attached to a data controller (Microsoft Data Bound List Control 5.0 - SP-3: DBList32.OCX). The controller is named datSub. The form has another data controller named datMain that controls the data on the main form.

I was able to successfully attach the data controller to the Access 2000 database. I converted all the code over to the ADO syntax. Everything in the program works fine except posting new records to the grid. I have a routine that looks up data and ads records to the data table the the data controller is hooked to. After the new records are created I try to refresh the grid and no records appear. If I close the form and reopen it, the new records are there. Additionally, If I move off of the main record and then go back to it, the records appear. If have the following code in my datMain reposition event:

'Restrict the line item information to a specific record number
datSub.RecordSource = "select * from CycleItem " & "where CycleItem.[Cycle Number] = '" & txtCycleNum.Text & "'" & " _
order by [Row Number], [Item Number], [Position] ASC"

datSub.Refresh

This is working perfectly. The routine that won't post data to the grid is as follows:

Private Sub GetStockData()

'Declare form variables
Dim sql1 As String
Dim sqlRow As String
Dim dbname As String
Dim db1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rsRow As ADODB.Recordset
Dim stItemNum as string
Dim stSize As String
Dim inPosition As Integer
Dim cuValue As Currency

'Select database to use
dbname = "dcdata.mdb"
Set db1 = New ADODB.Connection

'Specify the connection string
db1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & dbname

'Open Database
db1.Open

'Initiate the recordsets
Set rsRow = New ADODB.Recordset
Set rs1 = New ADODB.Recordset

'Define recordset to be used for data
sql1 = "select * from [Stock]"
sql1 = sql1 & "where [Row Number] = '" & stRow & "'"
sql1 = sql1 & " order by [Row Number], [Item Number] ASC"
rs1.Open sql1, db1, adOpenKeyset, adLockPessimistic, adCmdText

'Check for at least one record in database
If rs1.RecordCount > 0 Then

'Define recordset to be used for data

sqlRow = "select * from [CycleItem]"

'Open recordset

rsRow.Open sqlRow, db1, adOpenKeyset, adLockPessimistic, adCmdText

'Go to first record

rs1.MoveFirst
Do While Not rs1.EOF

stItemNum = rs1![Item Number]
stSize = rs1![Size]
cuValue = rs1![Value]
inPosition = rs1![Position]

'Set the recordset to rsRow for all operations in the loop

With rsRow

.AddNew
![Cycle Number] = txtCycleNum.Text
![Row Number] = rs1![Row Number]
![Item Number] = rs1![Item Number]
!Size = rs1![Size]
!Position = inPosition
!Description = rs1![Description]
![System Quantity] = rs1![Quantity]
!Unit = rs1![Unit]
!Value = cuValue
![First Count] = 0
![First Variance] = 0
![Second Count] = 0
![Second Variance] = 0
.Update

End With

rs1.MoveNext

Loop

'Reset the data controller record source that is connected to the datagrid

datSub.RecordSource = "select * from CycleItem " & "where CycleItem.[Cycle Number] = '" & _
txtCycleNum.Text & "'" & " order by [Row Number], [Item Number], [Position] ASC"

'Post new data to grid

datSub.Refresh

Else

InUserResponse = MsgBox("No items were found.", vbOKOnly, "Record Check Check")

End If

'Reset recordsets and db
Set rs1 = Nothing
Set rsRow = Nothing
db1.Close

Exit Sub

I have confirmed that the new records are actually in the database table right after the routine is run. I have have tried switching the the Microsoft ADO Data Control 6.0 with the Microsoft DataGrid Control 6.0, and have the exact same issue. The routine use to work perfectly with the Access 97 DAO database. I'm sure it is just something I am not familiar with on the ADO version. Any help would be greatly appreciated!!! Once I get this figured out, I have to convert a bunch of other programs. Thanks!!!

[dilettante]

adOpenKeyset

Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.

Each separate Connection is a "user" and you have at least 3 Connections: datSub, datMain, and db1 here.


The ADO Data Control (not "controller") is only meant for very simple applications that use a single query. What you want here is a DataEnvironment, the ADODC's big brother that can support many queries using the same Connection.


I doubt any of this has anything to do with DAO or the creaky old Jet 3.x ANSI database format. There is no such thing as "an ADO database" or "DAO database."

I'm not sure why you have that loop either. That looks very strange, but is probably the sort of thing better done using an INSERT INTO... SELECT FROM query.


As a quick fix you might try using a adOpenDynamic cursor instead though. I'm just not sure whether that is consistent with your other cursor settings or the bookmark requirements of the DataGrid Control.

[MudManiac]

dilettante,
Thanks for the advise. Unfortunately, when I change the record set to adOpenDynamic, the routine doesn't work (returns a no data found message).

The datMain data control is attached to my "CycleHeader" table. This table includes all the redundant information for the cycle count (i.e. date, row, total count value, etc.). My datSub data control is attached to my "CycleItem" table. This table includes all the individual item detail (i.e. part number, size, item count, etc.). CycleHeader and CycleItem are related by the Cycle Number field. The datSub control is attached to the data grid. Both controls RecordsetType property are set as a 1-Dynaset.

When I change records in my main cycle count form, the reposition event redefines the record source property of the datSub control so it only shows the records in the data grid that are associated with the current Cycle Count on the main from. The reposition event is as follows:

'Restrict the line item information to a specific record number
datSub.RecordSource = "select * from CycleItem " & "where CycleItem.[Cycle Number] = _
'" & txtCycleNum.Text & "'" & " order by [Location Number], [Item Number], [Position] ASC"
datSub.Refresh

This is all working perfectly in the present code. The only problem I am having is that whenever I add a new record to the cycle item table and then try to refresh the datSub data control, the new records do not show up in the grid.

The only way I can get the new records to show up is to either run my save routine from my Save command button or reposition the datMain to another record then back to the original record or close the main form then reopen it.

I've tried adding the save record code I use on the command button to the end of the GetStockData record routine, but that doesn't work either. It only seems to work when I "click" the save command button. My Save Record Code is as follows:

If (datMain.Recordset.EditMode = dbEditAdd) Or (datMain.Recordset.EditMode = dbEditInProgress) Then

datMain.Recordset.Update

If datMain.Recordset.RecordCount > 1 Then

datMain.Recordset.MoveLast

Else

datMain.Refresh

End If

Else
'Force edit mode

datMain.Recordset.Edit

datMain.Recordset.Update

End If

I've even tried closing all the recordsets and database I use in the routine before resetting the datSub Recordsource. No matter what order I do things, the datSub recordcount = 0. When I checked the CycleItem table and the records are actually there. They just won't show up in the datSub recordcount until I either run my save routine from my Save command button or reposition the datMain to another record then back to the original record or close the main form then reopen it.

This all worked perfect when my database was in an Access97 format. This all started when it was changed to an Access2000 format. The code I use to use to open a database and recordset in the Access97 format is as follows:

'Select database to use
dbDCData = "dcdata.mdb"
Set db1 = DBEngine.Workspaces(0).OpenDatabase(dbDCData)

Set rs1 = db1.OpenRecordset(sql1, dbOpenSnapshot) 'sql1 defines the records to use

I initially tried to change the data controls to Access2000 format. The data controls worked with the Access2000 tables. However, whenever I tried to open a record via code, I received an "Unrecognized Database Format" error.

On a side note, I was forced to update everything after a recent Microsoft Office 365 update crashed all of our Access97 programs (i.e. whenever you opened a form, the program would shut down). I have updated all the Access programs to Access365 and they are working fine. It's the VB6 programs I am having problems with now.

I have to tackle much larger programs after I figure this out. Any help would be greatly appreciated. Thanks!!!!!!!!!!!!

[Schmidt]

At the time I was still using the MS AdoDC-control, I've never used its "RecordSource"-Property.

Instead I've always set its RecordSet-Property, using a (globally defined) Helper-Function like:
Set MyAdoDC.Recordset = GetRs("Select stuff From SomeTable")

I highly recommend such a central Rs-retrieval-function, because it can make your live much easier with ADO.

Here is the one I use (with slight variations, depending on whether I work in true disconnected mode, or not).

Public Function GetRs(ByVal SQL As String, ParamArray P()) As ADODB.Recordset
  Set GetRs = New ADODB.Recordset
  
  Dim Cmd As New ADODB.Command, i As Long
  Set Cmd.ActiveConnection = Cnn
      Cmd.CommandText = SQL
  For i = 0 To UBound(P)
    Select Case VarType(P(i))
      Case vbString: Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
      Case Else:     Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
    End Select
  Next

  GetRs.CursorLocation = adUseClient
  GetRs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
  GetRs.Properties("Update Criteria") = adCriteriaKey 'allow only PKs, to locate Rows for Updates in the Where-Clause
End Function

With the above you can then work without any SQL-String-Concat-Fiddling like e.g.

Const strSQL = "Select * From Tbl Where strFld=? And datFld Between ? And ?"
Set Rs = GetRs(strSQL , Text1.Text, DatePicker1.Value, DatePicker2.Value

...meaning, that you can pass the Values in question "as they are" (in their native DataTypes) -
just take care that they are in the same Order as the questionmarks in your SQL-def.


Edit: As for "refreshing" an AdoDC-Control (e.g. after Updates or UpdateBatch-calls)...
A simple AdoDC.Recordset.Requery should do...

Olaf

[dilettante]

If you have a lot of these old VB4/VB5 DAO programs that were never fully converted to VB6 and ADO it might not be worth the cost of trying at this late date.

This was a common problem when Jet 4.0 came out. The band-aid that was offered with VB6 was a newer version of DAO that could make use of the Jet 4.0 database engine. New functionality was lost, and you were stuck with the limitations of the older Jet SQL syntax in order to maintain compatibility. But compatibility with the old VB4/VB5 code was the goal anyway.

So you might get away with very few changes if you just use DAO 3.6 instead of the old DAO 3.5x libraries after upgrading the MDB file(s) to Jet 4.0 format.

Just replacing the reference to DAO 3.5x with a reference to DAO 3.60 instead might get you 95% or more of the way to your goal.

[Niya]

With the above you can then work without any SQL-String-Concat-Fiddling like e.g.

Const strSQL = "Select * From Tbl Where strFld=? And datFld Between ? And ?"
Set Rs = GetRs(strSQL , Text1.Text, DatePicker1.Value, DatePicker2.Value

...meaning, that you can pass the Values in question "as they are" (in their native DataTypes) -
just take care that they are in the same Order as the questionmarks in your SQL-def.

There is actually a far better reason for this approach, it protects you from SQL injection attacks. It prevents malicious end-users from injecting something like Delete TblCustomers into your queries and nuking all your customer data from a TextBox.

[MudManiac]

dilettante,
I changed the reference to DAO 3.60 and my original code worked perfectly with my Access2000/2003 databases (thank you, thank you, thank you). I had to position the DAO 3.60 reference near the top of my references list to ensure it took priority.

I was using use Crystal Reports 5.0 (very old version) for my reporting. Unfortunately, I didn't have much luck trying to connect that version with the Access2000 tables. I ended up upgrading to Crystal Report 10 (had that version in my library) and converting all my reports to the new version. I added a reference to the Crystal Reports 10 reports ActiveX Report Viewer in my program. I then created a new form I named "ReportView" and added the report control to the form. I modified my print code in my other forms to send the data to the ReportView form as follows:

‘These Crystal Report Variables are in the Global Module
Public Appl As New CRAXDRT.Application
Public MyReport As CRAXDRT.Report

Private Sub cmdPrint_Click()

Dim stCycleNum As String
Dim stMsg As String

'Crystal Report Variables
Dim strReportPath As String
Dim strReportName As String

'Set path to report file
strReportPath = App.Path

'Open the New report
Set MyReport = New CRAXDRT.Report

'Set record number variable so we can reposition the record after returning from the report viewing form (goes to last record by default if the data control is not repositioned).
stCycleNum = txtCycleNum.Text

'Define the report to be used
strReportName = "\CycleCountSheet.rpt"
Set MyReport = Appl.OpenReport("" & strReportPath & "" & strReportName)

'Set selection formula to current record
MyReport.RecordSelectionFormula = "{CycleHead.Cycle Number} = '" & stCycleNum & "'"

'Open the form for viewing
ReportView.Show vbModal

'Reset the report definition
Set MyReport = Nothing[/INDENT]

[COLOR="#008080"]'Reposition record to current Cycle Number.
datMain.Recordset.FindLast "[Cycle Number] = '" & stCycleNum & "'"


For all my different reports, all I need to do is change the "strReportName" variable and my report record selection formula.

For now, everything is working great. Thank you so much for your help!!!!!