Retrieving Records from the Database

Reading records from the Access database is very easy. As you define the recordset, you pass a SQL string to return the records in which you are interested.

After the recordset is defined, use the CopyFromRecordSet to copy all the matching records from Access to a specific area of the worksheet.

The following routine queries the Transfer table to find all records where the Sent flag is not yet set to True. The results are placed on a blank worksheet. The final few lines display the results in a userform to illustrate how to update a record in the next section:

Sub GetUnsentTransfers()

Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim WSOrig As Worksheet Dim WSTemp As Worksheet Dim sSQL as String Dim FinalRow as Long

Set WSOrig = ActiveSheet

'Build a SQL String to get all fields for unsent transfers sSQL = "SELECT ID, Style, FromStore, ToStore, Qty, tDate FROM tblTransfer"

sSQL = sSQL & " WHERE Sent=FALSE"

' Path to Transfers.mdb MyConn = "J:\transfers.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

' Create the report in a new worksheet Set WSTemp = Worksheets.Add

' Add Headings

Range("A1:F1").Value = Array("ID", "Style", "From", "To", "Qty", "Date")

' Copy from the recordset to row 2 Range("A2").CopyFromRecordset rst

0 0

Post a comment