Searching Recordsets in code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm a relatively new database programmer. I am wondering if there is any
kind of tutorial or information on how to properly use recordsets in code to
locate and manipulate data. I am mostly self-taught in Access, and feel like
I missed a pretty important ability here...

As usual, ant and all help is greatly appreciated.
~MATT
 
The following code will show you how to use DAO recordsets in access just cut
and paste into a module and study. Hope this helps! - SPARKER

Private Sub subRecordSetDAO()

'Cut an paste this entire example into a module:
'This sample is set up to look at a table named Table1
'with the columns or fields of EmployeeID, FirstName, LastName
'You will need to edit the table and field names to work
'with your own sql query statement. This example is also set up
'to loop through each record that your query pulls one record
'at a time so that you can do something with the data.
'Using this DAO code you will need to add a reference to
'Microsoft DAO 3.6 Object Library
'To add the reference to the Microsoft DAO 3.6 Object Library:
'While you have the module open in the database click on
'Tools then
'References then
'Scroll Down until you find
'Microsoft DAO 3.6 Object Library
'then add it so the following code will work.


Dim daoDbs As DAO.Database 'Declare Database
Dim daoRec As DAO.Recordset 'Declare Recordset
Dim intEmployeeID As Integer 'Declare Variables for Fields to
be returned by your query
Dim strFirstName As String
Dim strLastName As String
Dim dteHireDate As Date
Dim strSql As String 'Declare the Query itself
strSql = "Select * From Table1;" 'Set the query
Set daoDbs = CodeDb 'Set the Database
Set daoRec = daoDbs.OpenRecordset(strSql) 'Set the Recordset

If Not (daoRec.BOF And daoRec.EOF) Then 'Check to see if we have any
records to work with
daoRec.MoveFirst 'Move to the very first record
Do While Not daoRec.EOF 'Start Loop and continue
until we run out of records

intEmployeeID = daoRec("EmployeeID").Value 'Set values from our
query to our variables
strFirstName = daoRec("FirstName").Value
strLastName = daoRec("LastName").Value
dteHireDate = daoRec("HireDate").Value
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'In here you can do whatever you want to do with the data such as alter,
append, delete, transfer
'Lets say place employees last name Smith Hired recently
'into a temp table named Table2 for further manipulation etc...
'Yes I know this sounds rediculous but it is just a hypothetical
'example to show the use of DAO recordsets

Dim strSqlAppend As String 'Declare and set our SQL Append Query
strSqlAppend = "INSERT INTO Table2 ( EmployeeID, [First Name], [Last Name] )
" & _
"SELECT " & intEmployeeID & ", " & strFirstName & ", " & strLastName & " ;"
'If Last name is Smith and employee was hired in the past 30 days then
If strLastName = "Smith" And dteHireDate > Date - 30 Then
CodeDb.Execute strSqlAppend 'Append Employee
End If

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
daoRec.MoveNext 'Move to the next record
Loop 'Loop back to the start of
the loop
Else 'Else the check said there
is no data to work with
MsgBox "This Query Produced 0 Records To Work With.",
vbInformation, "No Records:"
End If
End Sub
 
Melvis said:
I'm a relatively new database programmer. I am wondering if there is any
kind of tutorial or information on how to properly use recordsets in code to
locate and manipulate data. I am mostly self-taught in Access, and feel like
I missed a pretty important ability here...
Click to expand...


I don't know of a tutorial, which is not to say they don't
exist. I suggest that you start by looking up Recordset in
Help and follow the links to all its Properties and Methods.
Be sure to distinguish the difference between DAO (probably
the one you want) and ADO recordset objects.
 
How would this be effected if the database id to be upsized to a Microsoft
SQL Backend?

sparker said:
The following code will show you how to use DAO recordsets in access just cut
and paste into a module and study. Hope this helps! - SPARKER

Private Sub subRecordSetDAO()

'Cut an paste this entire example into a module:
'This sample is set up to look at a table named Table1
'with the columns or fields of EmployeeID, FirstName, LastName
'You will need to edit the table and field names to work
'with your own sql query statement. This example is also set up
'to loop through each record that your query pulls one record
'at a time so that you can do something with the data.
'Using this DAO code you will need to add a reference to
'Microsoft DAO 3.6 Object Library
'To add the reference to the Microsoft DAO 3.6 Object Library:
'While you have the module open in the database click on
'Tools then
'References then
'Scroll Down until you find
'Microsoft DAO 3.6 Object Library
'then add it so the following code will work.


Dim daoDbs As DAO.Database 'Declare Database
Dim daoRec As DAO.Recordset 'Declare Recordset
Dim intEmployeeID As Integer 'Declare Variables for Fields to
be returned by your query
Dim strFirstName As String
Dim strLastName As String
Dim dteHireDate As Date
Dim strSql As String 'Declare the Query itself
strSql = "Select * From Table1;" 'Set the query
Set daoDbs = CodeDb 'Set the Database
Set daoRec = daoDbs.OpenRecordset(strSql) 'Set the Recordset

If Not (daoRec.BOF And daoRec.EOF) Then 'Check to see if we have any
records to work with
daoRec.MoveFirst 'Move to the very first record
Do While Not daoRec.EOF 'Start Loop and continue
until we run out of records

intEmployeeID = daoRec("EmployeeID").Value 'Set values from our
query to our variables
strFirstName = daoRec("FirstName").Value
strLastName = daoRec("LastName").Value
dteHireDate = daoRec("HireDate").Value
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'In here you can do whatever you want to do with the data such as alter,
append, delete, transfer
'Lets say place employees last name Smith Hired recently
'into a temp table named Table2 for further manipulation etc...
'Yes I know this sounds rediculous but it is just a hypothetical
'example to show the use of DAO recordsets

Dim strSqlAppend As String 'Declare and set our SQL Append Query
strSqlAppend = "INSERT INTO Table2 ( EmployeeID, [First Name], [Last Name] )
" & _
"SELECT " & intEmployeeID & ", " & strFirstName & ", " & strLastName & " ;"
'If Last name is Smith and employee was hired in the past 30 days then
If strLastName = "Smith" And dteHireDate > Date - 30 Then
CodeDb.Execute strSqlAppend 'Append Employee
End If

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
daoRec.MoveNext 'Move to the next record
Loop 'Loop back to the start of
the loop
Else 'Else the check said there
is no data to work with
MsgBox "This Query Produced 0 Records To Work With.",
vbInformation, "No Records:"
End If
End Sub

Melvis said:
Hi,

I'm a relatively new database programmer. I am wondering if there is any
kind of tutorial or information on how to properly use recordsets in code to
locate and manipulate data. I am mostly self-taught in Access, and feel like
I missed a pretty important ability here...

As usual, ant and all help is greatly appreciated.
~MATT
Click to expand...
Click to expand...
 
There will be no problem running this code on linked SQL Server tables in an
Access database.
When using a SQL Server backend you link the tables into your Access
database and they function almost identical to Access tables.

Note: When using SQL Server 2000 for a back end try to do as much of your
code writing and data manipulation using the SQL Server Native SQL called T -
SQL using stored procedures, views, triggers, and DTS. That is much faster...

Let me know if I can be of any more assistance. - SPARKER



Melvis said:
How would this be effected if the database id to be upsized to a Microsoft
SQL Backend?

sparker said:
The following code will show you how to use DAO recordsets in access just cut
and paste into a module and study. Hope this helps! - SPARKER

Private Sub subRecordSetDAO()

'Cut an paste this entire example into a module:
'This sample is set up to look at a table named Table1
'with the columns or fields of EmployeeID, FirstName, LastName
'You will need to edit the table and field names to work
'with your own sql query statement. This example is also set up
'to loop through each record that your query pulls one record
'at a time so that you can do something with the data.
'Using this DAO code you will need to add a reference to
'Microsoft DAO 3.6 Object Library
'To add the reference to the Microsoft DAO 3.6 Object Library:
'While you have the module open in the database click on
'Tools then
'References then
'Scroll Down until you find
'Microsoft DAO 3.6 Object Library
'then add it so the following code will work.


Dim daoDbs As DAO.Database 'Declare Database
Dim daoRec As DAO.Recordset 'Declare Recordset
Dim intEmployeeID As Integer 'Declare Variables for Fields to
be returned by your query
Dim strFirstName As String
Dim strLastName As String
Dim dteHireDate As Date
Dim strSql As String 'Declare the Query itself
strSql = "Select * From Table1;" 'Set the query
Set daoDbs = CodeDb 'Set the Database
Set daoRec = daoDbs.OpenRecordset(strSql) 'Set the Recordset

If Not (daoRec.BOF And daoRec.EOF) Then 'Check to see if we have any
records to work with
daoRec.MoveFirst 'Move to the very first record
Do While Not daoRec.EOF 'Start Loop and continue
until we run out of records

intEmployeeID = daoRec("EmployeeID").Value 'Set values from our
query to our variables
strFirstName = daoRec("FirstName").Value
strLastName = daoRec("LastName").Value
dteHireDate = daoRec("HireDate").Value
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'In here you can do whatever you want to do with the data such as alter,
append, delete, transfer
'Lets say place employees last name Smith Hired recently
'into a temp table named Table2 for further manipulation etc...
'Yes I know this sounds rediculous but it is just a hypothetical
'example to show the use of DAO recordsets

Dim strSqlAppend As String 'Declare and set our SQL Append Query
strSqlAppend = "INSERT INTO Table2 ( EmployeeID, [First Name], [Last Name] )
" & _
"SELECT " & intEmployeeID & ", " & strFirstName & ", " & strLastName & " ;"
'If Last name is Smith and employee was hired in the past 30 days then
If strLastName = "Smith" And dteHireDate > Date - 30 Then
CodeDb.Execute strSqlAppend 'Append Employee
End If

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
daoRec.MoveNext 'Move to the next record
Loop 'Loop back to the start of
the loop
Else 'Else the check said there
is no data to work with
MsgBox "This Query Produced 0 Records To Work With.",
vbInformation, "No Records:"
End If
End Sub

Melvis said:
Hi,

I'm a relatively new database programmer. I am wondering if there is any
kind of tutorial or information on how to properly use recordsets in code to
locate and manipulate data. I am mostly self-taught in Access, and feel like
I missed a pretty important ability here...

As usual, ant and all help is greatly appreciated.
~MATT
Click to expand...
Click to expand...
Click to expand...
 
Back
Top