Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
OLE with Access
- Access 2000 Advanced DML Transactions
- Access Recordset to CSV File UDF
- Access RunCommand Constants
- ACCESS Table or Query Exists
- Access to Excel via SQL
- Access Visible Problem
- Adding Fields to an Existing Access Table
- Advanced DML Transactions
- Advanced SQL in the Jet 4.0 Provider Example
- Another OLE with Access Example
- Automating Access Tutorial
- Check if Access Table Exists
- Commit Rollback Begin Trans Updatebatch
- Control Access Forms
- Count Access Records
- Csv to Access
- Dealing with ACCESS 97 Field Types
- execute a macro in Access
- Execute a Public function in Access
- Export Access Report in Choice of Formats
- Export MDB to Flat File
- Find Read and Insert Data in Access
- Gather ACCESS Table and Column Information Into Excel
- Get Access Version That Created MDB File
- Get Column Data From an Access Database
- Get Record Count
- How to Use the OpenTable method
- Launch Access Macro from Commandline
- Loop thru a recordset in Access
- Office 2003 Macro Security Issue
- OLE and Access
- OLE Import a table into Access
- OLE Import Table into Access
- Open Access DB with DAO
- Pack an Access Database
- Query and Export Data from Microsoft Access
- Read Write Delete Tables from Access DB
- Repair MDB
- Retrieve Data in Access
- SQL UPDATE Fails with Access
- UDF to Return Record Count
- Updating Table From Filtered Recordset
- Ways to Get Data from and Access file without Access installed
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Retrieve Data in Access
Keywords: MDB ACCDB loop recordset OLE Access Retrieve Data Grab MicroSoft.Jet.OLEDB.4.0 MicroSoft.Jet.OLEDB.12.0
Sample 1:
This sample code which queries the table and loops through the result set.
file = "C:\Temp\Temp.mdb"
tablename = "Test"
query = StrCat("select * from ",tablename)
; create the access application object...
oApp = objectopen("Access.Application")
; set the mdb name you want to work with...
dbname = "C:\Temp\Temp.mdb"
; open the database...
oApp.opencurrentdatabase(dbname)
; set the db object to the current db...
db = oApp.currentdb
; open the recordset...
rs = db.openrecordset(query)
; set the field...
rfields = rs.fields
; loop thru the recordset...
while rs.eof == @false
fieldcount = rFields.count
For x = 0 to fieldcount-1
field = rFields.Item(x)
message(field.name, field.value)
Next
rs.movenext
endwhile
rs.close
oApp.closecurrentdatabase
oApp.quit
; close up the objects...
objectclose(field)
objectclose(rfields)
objectclose(rs)
objectclose(db)
objectclose(oApp)
; notify the user...
message("Debug", "All Done")
exit
Sample 2:
This sample code loops through the entire table grabbing every record.
file = "C:\Temp\Temp.mdb"
table = "Email"
;Open recordset with data from table.
strCnn = StrCat("Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=",file,";")
adCmdTable = 2
rstRecset = ObjectOpen("ADODB.Recordset")
rstRecset.Open(Table, strCnn, , , adCmdTable)
title = StrCat("Field values in ",table)
BoxOpen(title,"")
rstRecset.MoveFirst
;Enumerate the Fields collection of the table.
rstFields = rstRecset.Fields
While @True
fieldcount = rstFields.count
For x = 0 to fieldcount-1
;Because Value is the default property of a
;Field object, the use of the actual keyword
;here is optional.
field = rstFields.Item(x)
BoxText(StrCat(field.Name," = ",field.Value))
Timedelay(0.5)
Next
;Move to next record
rstRecset.MoveNext
;Trap for EOF.
If rstRecset.EOF Then break
EndWhile
TimeDelay(1)
rstRecset.Close
ObjectClose(rstFields)
ObjectClose(rstRecset)
Sample 3:
This ACCDB sample code loops through the entire table grabbing every record.
file = "C:\Temp\Temp.accdb"
table = "Email"
;Open recordset with data from table.
strCnn = StrCat("Provider=MicroSoft.Jet.OLEDB.12.0; Data Source=",file,";")
adCmdTable = 2
rstRecset = ObjectOpen("ADODB.Recordset")
rstRecset.Open(Table, strCnn, , , adCmdTable)
title = StrCat("Field values in ",table)
BoxOpen(title,"")
rstRecset.MoveFirst
;Enumerate the Fields collection of the table.
rstFields = rstRecset.Fields
While @True
fieldcount = rstFields.count
For x = 0 to fieldcount-1
;Because Value is the default property of a
;Field object, the use of the actual keyword
;here is optional.
field = rstFields.Item(x)
BoxText(StrCat(field.Name," = ",field.Value))
Timedelay(0.5)
Next
;Move to next record
rstRecset.MoveNext
;Trap for EOF.
If rstRecset.EOF Then break
EndWhile
TimeDelay(1)
rstRecset.Close
ObjectClose(rstFields)
ObjectClose(rstRecset)
Article ID: W14672
Filename: loop thru a recordset in Access.txt
File Created: 2012:12:24:09:29:30
Last Updated: 2012:12:24:09:29:30