[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

Looping through table records in Access

Can vba be used to loop through records in a table in Access in the same way it can in Excel?
0
JSSenior
Asked:
JSSenior
4 Solutions
 
hnasrCommented:
You can use VBA to create a recordset (rs)  from the table.

Loop through recordset While Not (rs.EOF)

Private Sub Command7_Click()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("a")
    
    rs.MoveFirst
    Do While Not rs.EOF
    'insert code here
        rs.MoveNext
    Loop
End Sub

Open in new window

0
 
Jeffrey CoachmanCommented:
1. Sure, but for what reason?
A lot of what people want to do with a recordset, can be done faster with SQL...

2. Slight tweaks on hnasr's code:
Add:
    rs.close
    set rs=Nothing

...to the end of the code

JeffCoachman
0
 
Gustav BrockCIOCommented:
And skip the rs.MoveFirst. Not needed.

/gustav
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JSSeniorAuthor Commented:
I could write the 200 queries but was thinking of just having one query with the variables in a table.
0
 
hnasrCommented:
Thanks boag2000. Tend to overlook obvious things.

"I could write the 200 queries but was thinking of just having one query with the variables in a table."
Good Idea!
If you you need help in that, upload a sample database with 2 or 3 similar queries showing the variations in each, if it is a field name or table name, ...to see how the record set will help.
0
 
Helen FeddemaCommented:
You might be able to write code using two loops -- the outer one looping through a table of table names, query names, or whatever is relevant, and the inner one processing each table (or query, or whatever) in turn.
0
 
Jeffrey CoachmanCommented:
JSSenior,

Are you still out there?

As you can see, we need to know more of the details of what you are trying to do...
0
 
Jeffrey CoachmanCommented:
hnasr,
Still, you posted exactly what the asker wanted.
So the logic behind recordset looping may be similar to what they were doing in Excel.
So using a recordset may be the way to go...

My suggestion of using SQL was just exploratory...
...and might confuse the issue...
...so I'll step aside here.

I am sure between you, gustav and Helen, you can get this going.

;-)

Jeff
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now