We help IT Professionals succeed at work.

Manipulating recordsets in code

Mike McCracken
on
Medium Priority
330 Views
Last Modified: 2012-05-06
I need to create and open two recordsets in VBA code.

One recordset will be read-only and used to create/update records in the other recordset.

I know how to do  the manipulations but forget how to declare and open recordsets in VBA code.  I would also like to see the code for updating and adding a record in a recordset.

mlmcc
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:

dim rs1 as dao.recordset,rs2 as dao.recordset

set rs1=currentdb.openrecordset("table1")

set rs2=currentdb.openrecordset("table2")
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Author

Commented:
What if I wanted to limit the records with a filter on the product code  I can write the SQL but what changes in the opening of the recordset?

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Author

Commented:
For instance something like

Select productcode, weight from table1 where productcode = 1234\

mlmcc
CERTIFIED EXPERT
Top Expert 2016
Commented:

dim rs1 as dao.recordset,rs2 as dao.recordset
dim sql as string

sql="select * from Table1 where <criteria >"
set rs1=currentdb.openrecordset(sql)

set rs2=currentdb.openrecordset("table2")

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Author

Commented:
Thanks.  I will try that tonight and let you know how it works out.

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Author

Commented:
Worked perfectly.

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Author

Commented:
Thanks.  
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.