Retrieve and update 1 record at a time

[Guest]

I'm not sure how complicated this is, but thought it be worth asking...

I created a macro in excel using ADO to retrieve records from an
Access database (code below). My db table is called tblSales and has 2
fields: saleID, flag (flag datatype=True/False, with a default of False).
Currently the macro is pulling all saleIDs and storing them into Sheet2. I
want to modify the macro to only pull one row record at a time and at the
same time update the flag field of the current record to True. The reason
for the flag is so when there are multiple users, a SaleID will only be
retrieved once. Since we are pulling only 1 record at a time, the SaleID
will only be stored in cell A1, Sheet2 each time. Therefore, the excel sheet
will only display the current record at a time. I hope this makes sense, my
code current code is below. I am new to programming and have been stuck with
this one for awhile now. Thank you to anyone who can provide me with
assistance!


Sub GetRecord()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim sSql As String

Set WSOrig = ActiveSheet

sSql = "SELECT saleID FROM tblSales"
sSql = sSql & " WHERE Flag=False"

MyConn = "C:\SalesDB.mdb"

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSql, ActiveConnection:=cnn, _
CursorType:=AdForwardOnly, LockType:=adLockOptimistic, _
Options:=adCmdText

Worksheets("sheet2").Range("A1").CopyFromRecordset rst

rst.Close
cnn.Close

End Sub

 

[Jim Rech]

I'm not very knowledgable in this area so for what it's worth here's example
code that bring in field name and database items, one at a time. Maybe this
will give you some ideas.

Sub Demo1()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim RowCounter As Long, ColCounter As Long

Sheet1.Cells.ClearContents
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
ThisWorkbook.Path & "\adodb_database.xls" & ";Extended Properties=Excel
8.0;"
rst.Open "ourdb", cnn, adOpenForwardOnly, adLockReadOnly

'Fields names
For Each fld In rst.Fields
ColCounter = ColCounter + 1
Sheet1.Cells(1, ColCounter).Value = fld.Name
Next

'List values for all fields record by record
RowCounter = 2
Do Until rst.EOF
ColCounter = 1
For Each fld In rst.Fields
Sheet1.Cells(RowCounter, ColCounter).Value = fld.Value
ColCounter = ColCounter + 1
Next
RowCounter = RowCounter + 1
rst.MoveNext
Loop
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

 

[Guest]

thankyou!

I'm not very knowledgable in this area so for what it's worth here's example
code that bring in field name and database items, one at a time. Maybe this
will give you some ideas.

Sub Demo1()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim RowCounter As Long, ColCounter As Long

Sheet1.Cells.ClearContents
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
ThisWorkbook.Path & "\adodb_database.xls" & ";Extended Properties=Excel
8.0;"
rst.Open "ourdb", cnn, adOpenForwardOnly, adLockReadOnly

'Fields names
For Each fld In rst.Fields
ColCounter = ColCounter + 1
Sheet1.Cells(1, ColCounter).Value = fld.Name
Next

'List values for all fields record by record
RowCounter = 2
Do Until rst.EOF
ColCounter = 1
For Each fld In rst.Fields
Sheet1.Cells(RowCounter, ColCounter).Value = fld.Value
ColCounter = ColCounter + 1
Next
RowCounter = RowCounter + 1
rst.MoveNext
Loop
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub