VB6 ADO-Excel 97- Data Grid linked to ado appears 'Read Only'? 2

[h4fod]

Hi
I had nearly exhausted all ways of connecting an ado recordset to an Excel 97 workbook for data processing. I then finally had success setting up a local PC ODBC Data Source. The ado recordset populates the ado recordset correctly and the Data Grid linked to the ado datasource displays the underlying Excel Row/col data including Column headers correctly. However, it is not possible to modify data in the data grid cells. They appear 'locked' despite all the properties set for the Data Grid being set appropriately; AllowAddnew, AllowUpdate, Allowdelete all set to 'True'.

I have set the ado connection string to include 'readOnly = False' and used an 'adOpenkeyset' argument thinking it was a data connection problem. Is this an issue with the ODBC Data Source I have set up - but limited connection options with this object anyway, or something else. I am trying to avoid other Data Source connections because I have tried most using KB articles and associated threads in Tek-Tips and all my attempts report errors when the data linking is executed.

Many thanks in anticipation of your help.

PS Processing part of code in its 'infancy' below!

Private Sub cmdExcelDataLink_Click()
Dim connString As String
Dim oconn As ADODB.Connection
Dim ors As ADODB.Recordset
Dim nCols As Integer
Dim strSQL As String
Dim strTableName As String
Dim strTemp As String
Dim intRecordCount As Integer
Dim J As Integer

'Note Read Only attriute must be set to false with ODBC - True by default
connString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=KumonExcel; ReadOnly = false;"

' this assumes that the first row contains headers
strTableName = "[Kumon$]"
strSQL = "SELECT * FROM " & strTableName
'strSQL = strSQL & Where

Set ors = New ADODB.Recordset
ors.Open strSQL, connString, adOpenKeyset, adLockOptimistic

nCols = ors.Fields.Count

Set DataGrid1.DataSource = ors.DataSource
DataGrid1.Refresh


With ors
.MoveLast
.MoveFirst
intRecordCount = .RecordCount

.MoveFirst
MsgBox "No of Records: " & intRecordCount

    For J = 1 To intRecordCount - 1

             If IsNull(.Fields(0).Value) = True Then
                  
                Exit Sub
                'If no field data Exit. recordcount may contain data in other
                'workbook columns below record count
                
                         Else
                              strTemp = .Fields(0).Value
                            MsgBox strTemp
            End If

        .MoveNext

    Next
End With


End Sub

 

[dilettante]

A spreadsheet makes a fairly poor substitute for even a single-table Jet MDB database. There are limitations galore connecting to Excel Workbooks as data sources.

When doing so I feel I get better results ignoring the ancient ODBC Desktop Drivers though and just using the Jet Intallable ISAMs for text, Excel, etc. The "Excel 8.0" ISAM handles both the creaky Excel 97 and more stable Excel 2000 formats.

Also see

http://support.microsoft.com/kb/257819

Often people aren't really aware of how a DataGrid is used. There are several properties governing how Tab and arrow keys work, but people don't seem to know the roles of Esc, Enter, and Del, or that "entering edit mode" for a cell typically needs a mouse-click.

Access table views typically use F2 as a keyboard shortcut for this so I tend to implement F2 for my own DataGrids.

Option Explicit

Private Sub DataGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
    'Implement "F2 open cell for editing" but this is only effective
    'for some MarqueeStyle settings.  To replace the cell users
    'can just stop typing but to edit they'll want to "open"
    'the cell.
    '
    '"For free" we also get Esc = cancel change, Del = delete
    'selected row, and Enter = commit change.
    '
    'Also implement Ctrl-Home and Ctrl-End.
    
    If KeyCode = vbKeyF2 Then
        DataGrid1.EditActive = True
    ElseIf Shift = vbCtrlMask Then
        Select Case KeyCode
            Case vbKeyEnd
                Adodc1.Recordset.MoveLast
            Case vbKeyHome
                Adodc1.Recordset.MoveFirst
            Case Else
                Exit Sub
        End Select
        DataGrid1.Col = 0
    Else
        Exit Sub
    End If
    KeyCode = 0
End Sub

Private Sub Form_Load()
    With Adodc1
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
                          & App.Path & "\Sample.xls';" _
                          & "Extended Properties='Excel 8.0;HDR=Yes'"
        .CommandType = adCmdTable
        .RecordSource = "[Students$]"
        .CursorType = adOpenStatic
        .Refresh
    End With
    With DataGrid1
        .AllowAddNew = True
        .AllowArrows = True
        .AllowDelete = False 'Not supported in this ISAM anyway.  See KB 257819.
        .AllowUpdate = True
        .MarqueeStyle = dbgSolidCellBorder
        .TabAcrossSplits = False
        .TabAction = dbgGridNavigation
        Set .DataSource = Adodc1
    End With
End Sub

I'd guess that you either didn't get your connecting string formatted correctly to set "ReadOnly" to False or you are having trouble with the "enter edit" operation on cells from the user interface at runtime.

But the whole thing with Tab and arrow keys can get confusing since they have several levels of possible meaning depending on property settings and the current mode of operation of the grid. I won't even try to get into Splits on a DataGrid.

ADODCs are not necessary with DataGrids but even using an invisible one can save you some fighting over trying to use a bare ADO Recordset. You also get some additional useful events for specialized ways you might want to modify the behavior of the grid.

 

[h4fod]

Hi
Your solution worked fine using an ADO object on the form and the connection string suggested. I can now edit data in the Data Grid and the changes are written to the Excel File - data linked. Grid cell editing on the KeyDown event is a cool addition too!

Presumably this (implied) can be used when the Data Grid displays MS Access records?

Again, I would not ordinary use a 'Flat File' spreadsheet to store data given data integrity issues etc. However, in this case the source data for legacy reasons is stored and managed in this way.

Many thanks again for your help on this one.

 

[dilettante]

Yeah, sometimes it can just be a requirement.

The biggest problems are that the Worksheets need to really have "rectangular" (tabular) data and you can't do row deletes.

Often people want all kids of miracles, and then you have no choice but automating Excel and writing a ton of code. When it fits, simply using ADO can save a lot of writing and debugging.

The F2 and other keystroke extensions should work for any data source. I find this handy for working with DataGrids without reaching for the mouse to click a cell to select it and clicking again to "open" it for editing. The choice of F2 was arbitrary, but matches what Access and Excel use.

This (F2) is for a case where you have tabbed or "arrowed" to a cell and you want to alter the cell contents. If you want to type all new text there you can just start typing. But here I'm talking about switching to "edit mode" where you can use the field like a TextBox (move the cursor, use Del key, End key, Backspace key, etc.) until ready to Tab or Enter out of edit mode... or Esc to abort the edits.

The odd thing is that various data grids have been around for a long, long time... but if there is a standard keystroke that already does this without the KeyDown logic I sure can't remember what it is! I have searched off and on several times over the years and never found a list of standard "special keys" for MS data grids.

 

[h4fod]

Hi again
Thanks for enlightening me on this topic. You are a 'star'. will now continue to develop my solution!

Incidentaly, on an 'aside' since I have several grids (same context), each will show 'maths' Science' and 'english' data sets respectively each based upon a corresponding worksheet in the Excel workbook. Spent over an hour now trying to find an appropriate property of the SSTab control to set the focus to the appropriate tab. So, if 'Maths' student data to be displayed, then 'Maths' tab and assoc DataGrid will be preseted to the user. I'm sure this is (should be) trivial ut taxing me at the mo! If you can ansewer this quickly would appreciate it.

Many thanks again

Mike

 

[Andrzejek]

If your tab control is named SSTab, try:

SSTab.Tab = 0

to show the first Tab, but...
I use SSTab in my vb 6 applications and found this control to be very tricky and not very stable sometimes.

Have fun.

---- Andy

 

[h4fod]

Hi Andy
Tab problem solved! Many thanks again.
Mike