Trying to display contents of access table using dbgrid

[aclayborne]

Does anyone have any code snippets showing how to connect, load, and update and access table using a dbgrid?
Also do you have control over how the fields are display?
Ex. Can a boolean field be displayed as a check box.

I'm a dbgrid begginner.

 

[GerardMcL]

Hi,

I dont have a lot of experience with Flexgrids (which might be the better option), but you can do this with a datagrid.

Create an ODBC to the access database then in the datagrid properties you can connect using the ODBC. There are options on how you represent info - Im nearly sure you can show info as a checkbox.

Hope this helps but as I say maybe flexgrid are the best way to go but I always find datagrids easier to use with no coding neccessary

 

[itmasterw]

I do not know how to update it I do know how through an ADO connection how to fill a Flexgrid from a record set. this code filled my flex grid and created the columns as well. I also had it filling a list box, you can ignore that part. I also should mention that I have a comboBox filled at formload, so I can select a user and his assocated records. You may or may not want this. Throughout the code I have it commented fairly well,but if you have any questions let me know.
Hope it helps

Option Explicit
Dim Rs As Recordset ' General Declarations
Dim Rs2 As Recordset
Dim CN As Connection
Dim rSql As String
Dim stn As String
Dim fld As ADODB.Field
'Dim hflxResults As MSHFlexGrid

Private Sub Combo1_Change()

End Sub

Private Sub Combo2_Click()

On Error Resume Next
Err.Number = 0
rSql = "DROP TABLE [testTable]" ' Deletes the the table
CN.Execute rSql
Err.Number = 0

Me!lstOPut.Clear

' Creates the testTable
rSql = "SELECT [Survey database].Customer, [Survey database].User, [User Id].Name, [Survey database].[Q8 Verb],[Survey database].Department INTO testTable FROM [Survey database] INNER JOIN [User Id] ON [Survey database].User = [User Id].[User ID]Where [User Id].[User ID] = '" & Mid([Combo2], 1, 3) & "'"
CN.Execute rSql

'Sets up the connection string for Rs2
Set Rs2 = New ADODB.Recordset
Rs2.Open "SELECT * FROM testTable", _
CN, adOpenKeyset, adLockOptimistic

DisplayForwardGrid Rs2, hflxResults ' Calls the DisplayForwardGrid Function to fill the Flex Gid

'this is where you put the list view code
Rs2.MoveFirst
Do Until Rs2.EOF
lstOPut.AddItem (Rs2!Customer & Space(5) & Rs2!User & Space(5) & Rs2!Name & Space(5) & Rs2![Q8 Verb] & Space(5) & Rs2!Department)
Rs2.MoveNext ' move to the next record
Loop

Rs2.Close 'Close the recordset
Set Rs2 = Nothing

End Sub

Private Sub DataGrid1_Click()

End Sub

Private Sub Form_Load()
Set CN = New ADODB.Connection

' Sets up the connection
CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:\E544293\Desktop\Survey Database.mdb;"

Set Rs = New ADODB.Recordset

On Error Resume Next
Err.Number = 0
rSql = "DROP TABLE [testTable]" ' Deletes the the table
CN.Execute rSql
Err.Number = 0

'Sets up the connection string Rs
Rs.Open "SELECT * FROM [User Id]", _
CN, adOpenKeyset, adLockOptimistic

Rs.MoveFirst ' moves to the first record

' while not at the end of file, fill the the combobox form the user ID table
Do While Not Rs.EOF
Combo2.AddItem Rs("User ID") & Space(3) & Rs("Name")
Rs.MoveNext 'Move to the next record
Loop

Rs.Close ' Close the Record set
Set Rs = Nothing

End Sub

Private Sub HScroll1_Change()
End Sub

Private Sub DisplayForwardGrid(Rs2 As ADODB.Recordset, hflxResults As MSHFlexGrid)
hflxResults.Redraw = False
hflxResults.FixedCols = 0
hflxResults.FixedRows = 0
hflxResults.Cols = Rs2.Fields.Count
hflxResults.Rows = 1
hflxResults.Col = 0
hflxResults.Clear

'SET UP THE hflxGrid headers
For Each fld In Rs2.Fields
hflxResults.Text = fld.Name
hflxResults.ColAlignment(hflxResults.Col) = 1
hflxResults.ColWidth(hflxResults.Col) = Me.TextWidth(fld.Name & "AA")
If hflxResults.Col < Rs2.Fields.Count - 1 Then
hflxResults.Col = hflxResults.Col + 1
End If

Next fld

'Move Through each row in the record set
Do Until Rs2.EOF
'Set the position in the hflxResults
'hflxResults.Rows = hflxResults.Rows + 1
'hflxResults.Row = hflxResults.Rows + 1
'hflxResults.Col = 0

'Loop through all fields
For Each fld In Rs2.Fields
hflxResults.Text = fld.Value
If hflxResults.ColWidth(hflxResults.Col) < Me.TextWidth(fld.Value & "AA") Then
hflxResults.ColWidth(hflxResults.Col) = Me.TextWidth(fld.Value & "AA")
End If
Next fld

Rs2.MoveNext
Loop

If hflxResults.Rows = 1 Then
hflxResults.Rows = 2
End If

hflxResults.FixedRows = 1
hflxResults.Redraw = True

Set hflxResults.DataSource = Rs2

End Sub

 

[aclayborne]

Thanks a lot guys!