multiple recordsets

[Tom]

I need to open multiple recordsets at the same time.

Function1 (public) opens a table I need to read thru, which will remain open for
my entire task. Function1 calls Function2 (public) and needs to open and close
one table once and another twice, before returning to Function1. In fact,
before exiting Function2, I even call another function to make a log entry into
an additional table, to log how long this file took to import, in addition to
how many records we've added. But until now, I've been able to avoid opening
more than one recordset at a time. Now...I NEED to do so.

I'm trying to keep them separate this way:

Global DAOdbs As DAO.Database
Global DAOdbs1 As DAO.Database
Global DAOrs As DAO.Recordset
Global DAOrs1 As DAO.Recordset

Function1

Set DAOdbs1 = CurrentDb
Set DAOrs1 = DAOdbs1.OpenRecordset("tblMtrBk")

Do While Not DAOrs1.EOF

call Function2

DAOrs1.MoveNext
Loop

Exit Function

End Function


Function2

Set DAOdbs = CurrentDb
Set DAOrs = DAOdbs.OpenRecordset(strTblName)

' --------------------------------------------
' if this is the first file to import, clean out the table first
'
If intLoop1 = 1 Then
DoCmd.RunSQL ("DELETE * FROM " & strTblName)
End If

DAOrs.Close
Set DAOrs = Nothing

' --------------------------------------------
' get a record count of before we add our currrent file
'
Set DAOrs = DAOdbs.OpenRecordset(strTblName)

varBegRecCnt = DCount("*", strTblName)

DAOrs.Close
Set DAOrs = Nothing

' --------------------------------------------
...do my stuff...(import data)...
' --------------------------------------------

' get a record count of after we added our currrent file
'
Set DAOrs = DAOdbs.OpenRecordset(strTblName)

varBegRecCnt = DCount("*", strTblName)

DAOrs.Close
Set DAOrs = Nothing

' --------------------------------------------
' go make my log entry
'
call Function3 ' (opens & closes additional tbl)

' --------------------------------------------

DAOdbs.Close
Set DAOdbs = Nothing

Exit Function

End Function


If anyone can emlighten me as if and how one can open multiple recordsets at the
same time, I'd appreciate it.

Thanks in advance,

Tom

 

[Klatuu]

First, you don't need multiple database objects unless you are getting data
from another database. Second, you can open multiple table as easily as the
first:

Set DAOdbs = CurrentDb
set DAOrs = DAOdbs.OpenRecordSet(tblName)
set DAOrs1 = DAOdbs.OpenRecordSet(tblName1)
etc, etc.

Global DAOdbs As DAO.Database
Global DAOrs As DAO.Recordset
Global DAOrs1 As DAO.Recordset
Global DAOrs2 As DAO.Recordset
Global DAOrs3 As DAO.Recordset
Global DAOrs4 As DAO.Recordset

Function1

Set DAOdbs1 = CurrentDb
Set DAOrs1 = DAOdbs1.OpenRecordset("tblMtrBk")

Do While Not DAOrs1.EOF
' I moved this here and changed it. Why take the time to check each loop
DAOdbs1.Execute("DELETE * FROM " & strTblName)
call Function2
DAOrs1.MoveNext
Loop
Exit Function

End Function


Function2

Set DAOrs = DAOdbs1.OpenRecordset(strTblName)

' --------------------------------------------
' if this is the first file to import, clean out the table first
'

DAOrs.Close
Set DAOrs = Nothing

' --------------------------------------------
' get a record count of before we add our currrent file
'
Set DAOrs = DAOdbs.OpenRecordset(strTblName)

varBegRecCnt = DCount("*", strTblName)

DAOrs.Close
Set DAOrs = Nothing

' --------------------------------------------
...do my stuff...(import data)...
' --------------------------------------------

' get a record count of after we added our currrent file
'
Set DAOrs = DAOdbs.OpenRecordset(strTblName)

varBegRecCnt = DCount("*", strTblName)

DAOrs.Close
Set DAOrs = Nothing

 

[Tcs]

Thanks. I'd *swear* that I tried this, but I'll give it another go... Perhaps
I'm doing "something else" that's messing me up... Er, *was* doing something
else that *was* messing me up.