DAO Recordset in 2003, error 3020 object invalid?

[stephenk1973]

I have upgrade a an Access 97 db top 2003, one of the functions returns a 3420 error execute, falling over when closing the record set.

Code is quite basic

Dim db as database
Dim rst as DA0.recordset

set db=dbEngine(0)(0)
set rst = db.OpenRecordSet("select.....")

rst.movefirst
'Do some things

rst.close
set rst=nothing
db.close
set db=nothing

If the rst.close line is commented out it works? Why is this, sure i've got to close the the record set to reclaim the memory etc?

Explaination appreciated.

Thanks

Stephen

 

[mp9]

Is this a cut-and-paste of your code? Because if it is you've got

Dim rst as DA[red]0[/red].recordset


instead of Dim rst as DA[red]O[/red].recordset


i.e. a zero instead of an 'O'. You need the 'O' version! If that is the case I'm surprised you don't get more errors, but try changing that and see if it flies...

 

[stephenk1973]

Zero is a type..Full code....

Dim dbs As Database
Dim rst As DAO.Recordset

' Calculate the total net holding accross all funds and currencies
Set dbs = DBEngine(0)(0)
Set rst = dbs.OpenRecordset("select sum([HOLDING]+[Movement]) as TotalHolding from [Stiff Table] where [Stiff Table].Fund like 'T2*' and [Stiff Table].ccy='" & getCurrency & "'") ', dbOpenSnapshot)

rst.MoveFirst
GetTotalNetHolding = rst.Fields(0)
If getCurrency = "USD" Or getCurrency = "EUR" Then GetTotalNetHolding = GetTotalNetHolding * GetCurrencyRate(getCurrency)

'
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[mp9]

Access help offers the following, which may be relevant, for the Close method:

Closing an open object removes it from the collection to which it's appended. Any attempt to close the default workspace is ignored.

If the Connection, Database, Recordset, or Workspace object named ... is already closed when you use Close, a run-time error occurs.

 

[bubba100]

What happens if you try: Dim db As DAO.Database ?

 

[TheAceMan1]

How are ya stephenk1973 . . .

You apparently [blue]hand typed[/blue] your code so not sure of the actual, but there are errors:

[blue]Set rst = dbs.OpenRecordset("select sum([HOLDING]+[Movement]) as TotalHolding from [Stiff Table] where [Stiff Table].Fund like 'T2*' and [Stiff Table].ccy='" & getCurrency & "'"[red][b]) '[/b][/red], dbOpenSnapshot)[/blue]

In any case try this ([blue]don't type it[/blue] . . . copy & paste!):

[blue]   Dim dbs As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set dbs = CurrentDb
   SQL = "SELECT Sum([HOLDING]+[Movement]) as TotalHolding " & _
         "FROM [Stiff Table] " & _
         "WHERE [Stiff Table].Fund Like 'T2*' and " & _
               "[Stiff Table].ccy='" & getCurrency & "';"
   Set rst = dbs.OpenRecordset(SQL, dbOpenSnapshot)
   
   If Not rst.BOF Then
      GetTotalNetHolding = rst.Fields(0)
      
      If getCurrency = "USD" Or getCurrency = "EUR" Then
         GetTotalNetHolding = GetTotalNetHolding * GetCurrencyRate(getCurrency)
      End If
   End If
   
   Set rst = Nothing
   Set dbs = Nothing[/blue]

See Ya! . . . . . .