Closing database, Please give me some good info... 
Author Message
 Closing database, Please give me some good info...

Hi all,

In the procedure below, FillListBox( ), there is a call to the
function OpenDB( ) which open the database passed to it
and returns a recordset.
If I close the database before exiting the function OpenDB( )
I'll get an error in FillListBox( ) when working with the rstRecords
because all the recordsets related to the database seems to get
closed by closing the database.
Now I wonder: Is it a good idea to use this code as it is, not closing
the database? Will it not result in any loss of data in some cases?
Does the database close perhaps when the calling procedure is
exited?
Please give me some good info since I'm not used to work with
databases :-)

--
Thanks in advance!
Ali Eghtebas

Private Sub FillListBox( )
    Dim rstRecords as Recordset

    Set rstRecords = OpenDB(App.Path & "\Doc.mdb")

    With rstRecords
        Do While Not .EOF
            lstDocs.AddItem (!doc)
            .MoveNext
        Loop
    End With
    rstRecords.Close
End Sub

Public Function OpenDB(strMyDB$) As Recordset
    Dim dbsData As Database
    Dim rstRecords As Recordset
    Dim strSQL As String

    Set dbsData = OpenDatabase(strMyDB)
    strSQL = "SELECT * " & _
             "FROM Name " & _
             "WHERE ID = '" & gvntID & "'"
    Set rstRecords = dbsData.OpenRecordset(Name:=strSQL)

    Set OpenDB = rstRecords.Clone
    rstRecords.Close
    'dbsData.Close

End Function



Sat, 24 Nov 2001 03:00:00 GMT
 Closing database, Please give me some good info...

Try opening your DB near the top of FillListBox then close the dbsData near
the end (after populating the listbox) instead of opening the dbsData within
OpenDB.  (You could rename OpenDB() to OpenRS() and pass instance of dbsData
to the function since that would be more descriptive of what's going on
within the function.)

Also, it's always a good idea to close your DB and recordsets manually, as
well as setting the references = Nothing.

--
Jon Pope
Coherent Technologies, Inc.
(Remove .NoSpam before Replying)


Quote:
> Hi all,

> In the procedure below, FillListBox( ), there is a call to the
> function OpenDB( ) which open the database passed to it
> and returns a recordset.
> If I close the database before exiting the function OpenDB( )
> I'll get an error in FillListBox( ) when working with the rstRecords
> because all the recordsets related to the database seems to get
> closed by closing the database.
> Now I wonder: Is it a good idea to use this code as it is, not closing
> the database? Will it not result in any loss of data in some cases?
> Does the database close perhaps when the calling procedure is
> exited?
> Please give me some good info since I'm not used to work with
> databases :-)

> --
> Thanks in advance!
> Ali Eghtebas

> Private Sub FillListBox( )
>     Dim rstRecords as Recordset

>     Set rstRecords = OpenDB(App.Path & "\Doc.mdb")

>     With rstRecords
>         Do While Not .EOF
>             lstDocs.AddItem (!doc)
>             .MoveNext
>         Loop
>     End With
>     rstRecords.Close
> End Sub

> Public Function OpenDB(strMyDB$) As Recordset
>     Dim dbsData As Database
>     Dim rstRecords As Recordset
>     Dim strSQL As String

>     Set dbsData = OpenDatabase(strMyDB)
>     strSQL = "SELECT * " & _
>              "FROM Name " & _
>              "WHERE ID = '" & gvntID & "'"
>     Set rstRecords = dbsData.OpenRecordset(Name:=strSQL)

>     Set OpenDB = rstRecords.Clone
>     rstRecords.Close
>     'dbsData.Close

> End Function



Sat, 24 Nov 2001 03:00:00 GMT
 Closing database, Please give me some good info...
Thanks for your advice Jon!
So regarding what you say, it's not a good idea to make a function of
opening
the recordset!
I made it so to be able to use the same code to open the recordset from
other
procedures, to make it nice and clean.
Isn't there anyway to make it so, and be able to close the dbsData manually
without using global variables?

Regards
Ali Eghtebas  



Quote:
> Try opening your DB near the top of FillListBox then close the dbsData
near
> the end (after populating the listbox) instead of opening the dbsData
within
> OpenDB.  (You could rename OpenDB() to OpenRS() and pass instance of
dbsData
> to the function since that would be more descriptive of what's going on
> within the function.)

> Also, it's always a good idea to close your DB and recordsets manually,
as
> well as setting the references = Nothing.

> --
> Jon Pope
> Coherent Technologies, Inc.
> (Remove .NoSpam before Replying)



Sun, 25 Nov 2001 03:00:00 GMT
 Closing database, Please give me some good info...
Actually, I am using a function just like that. But I either pass it a
connection (Database) or have an open connection(database) as a global
variable

Tim


Quote:
> Thanks for your advice Jon!
> So regarding what you say, it's not a good idea to make a function of
> opening
> the recordset!
> I made it so to be able to use the same code to open the recordset from
> other
> procedures, to make it nice and clean.
> Isn't there anyway to make it so, and be able to close the dbsData
manually
> without using global variables?

> Regards
> Ali Eghtebas



> > Try opening your DB near the top of FillListBox then close the dbsData
> near
> > the end (after populating the listbox) instead of opening the dbsData
> within
> > OpenDB.  (You could rename OpenDB() to OpenRS() and pass instance of
> dbsData
> > to the function since that would be more descriptive of what's going on
> > within the function.)

> > Also, it's always a good idea to close your DB and recordsets manually,
> as
> > well as setting the references = Nothing.

> > --
> > Jon Pope
> > Coherent Technologies, Inc.
> > (Remove .NoSpam before Replying)



Sat, 01 Dec 2001 03:00:00 GMT
 Closing database, Please give me some good info...
Then I would appreciate to know how you do it :-)
Do you close the connection by the way?

Regards
Ali Eghtebas



Quote:
> Actually, I am using a function just like that. But I either pass it a
> connection (Database) or have an open connection(database) as a global
> variable

> Tim



Sun, 02 Dec 2001 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Please give opinions on different ways to setup disks for best performance under SQL

2. Please give opinions on how to setup disks for best performance uder SQL

3. Database theory: give me some links please

4. Please give advice on EXECUTEABLE Database Access

5. Please help with problem on closing database tables

6. SQLCE is giving me an index#0 error after I close an application

7. Closing CDaoDatabase gives Assertion error

8. WithEvents - Closing table too fast after open gives error (and other gotchas with event programming)

9. Please some info on what database to use

10. Multi - hierarchical databases - any info on please

11. Need info on Database by 2pm on 7/5, Please help

12. Help: Database process Info please.


 
Powered by phpBB® Forum Software