Opening a recordset in the current database

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

Guest

I just want to loop through some records in a table in the current database.
All of the help examples appear to open another database. Do I need to use
'OpenDatabase' or can I just use 'OpenRecordset'. (I've done this loads of
times before in different jobs, but I don't have my previous code to copy and
I'm having a blonde moment!!)

The code I have so far is:

Dim db1 As Database
Dim rs1, rs2 As DAO.Recordset
Dim dtStartDate, dtEndDate As Date

'select distinct product codes from extract
db1 = OpenDatabase("my_current_database.mdb")
rs1 = OpenRecordset("SELECT DISTINCT [db_chge] FROM Dbchgeextractout")

I'm getting an error msg saying "Invalid use of property"
 
1) In Access, you can use CurrentDb to get a reference to the current
database ...

Set db = CurrentDb

2) The reason for the error message is that you need to use 'Set' when
assigning references to objects in VBA ...

Set db = CurrentDb
Set rst = db.OpenRecordset

3) In VBA, when you declare multiple variables like this ...

Dim rs1, rs2 As DAO.Recordset

.... only rs2 in this example is declared as a DAO.Recordset. rs1 will
default to a Variant.
 
Thank you ... sorted :o)

Brendan Reynolds said:
1) In Access, you can use CurrentDb to get a reference to the current
database ...

Set db = CurrentDb

2) The reason for the error message is that you need to use 'Set' when
assigning references to objects in VBA ...

Set db = CurrentDb
Set rst = db.OpenRecordset

3) In VBA, when you declare multiple variables like this ...

Dim rs1, rs2 As DAO.Recordset

.... only rs2 in this example is declared as a DAO.Recordset. rs1 will
default to a Variant.

--
Brendan Reynolds (MVP)

Donna said:
I just want to loop through some records in a table in the current
database.
All of the help examples appear to open another database. Do I need to
use
'OpenDatabase' or can I just use 'OpenRecordset'. (I've done this loads
of
times before in different jobs, but I don't have my previous code to copy
and
I'm having a blonde moment!!)

The code I have so far is:

Dim db1 As Database
Dim rs1, rs2 As DAO.Recordset
Dim dtStartDate, dtEndDate As Date

'select distinct product codes from extract
db1 = OpenDatabase("my_current_database.mdb")
rs1 = OpenRecordset("SELECT DISTINCT [db_chge] FROM Dbchgeextractout")

I'm getting an error msg saying "Invalid use of property"
Click to expand...
Click to expand...
 
Back
Top