Access Application to Nothing

[Susan]

I am reading an Access db through VB 6.0 with the Visible
property set to FALSE. Throughout my use of the db, I
don't see Access at all ... which is good. But then,
when I use the statement "Set MyAccessApp = Nothing", an
empty Access window is briefly displayed. I believe that
setting the application to Nothing is important, but the
window flashing is annoying.

Any clues that would keep this from happening would be
greatly appreciated.

Thanks!
Susan
(e-mail address removed)

 

[Dirk Goldgar]

I am reading an Access db through VB 6.0 with the Visible
property set to FALSE. Throughout my use of the db, I
don't see Access at all ... which is good. But then,
when I use the statement "Set MyAccessApp = Nothing", an
empty Access window is briefly displayed. I believe that
setting the application to Nothing is important, but the
window flashing is annoying.

Any clues that would keep this from happening would be
greatly appreciated.

Do you call MyAccessApp.Quit before setting MyAccessApp = Nothing? Does
the database have Compact On Close set, and if so, does it make a
difference if you have that option turned off?

 

[Susan]

Hi Dirk!

I tried the two things that you suggested. The Compact
On Close property was not set. I tried setting it (just
for kicks) and it didn't make a difference.

Using the MyAccessApp.Quit call, the Access window now
pops up on _this_ call instead of the set to Nothing
statement.

Ideas?

 

[Dirk Goldgar]

Hi Dirk!

I tried the two things that you suggested. The Compact
On Close property was not set. I tried setting it (just
for kicks) and it didn't make a difference.

Using the MyAccessApp.Quit call, the Access window now
pops up on _this_ call instead of the set to Nothing
statement.

Ideas?

It doesn't do that for me, in a simple test (CreateObject, Debug.Print,
Quit). Do you have code that runs
in this Access application that may try to make some form or object
visible before it closes? What are you doing with MyAccessApp?

 

[Susan Coddington]

Hi Dirk!

Ok, I tried the following code and am getting the flash:

Private Sub PerformTestButton_Click()
Dim MyAccessApp As Access.Application
Dim DBRecordSet As DAO.Recordset
Dim SQLStatement As String

SQLStatement = "SELECT * from MajorFunctions"

Set MyAccessApp = CreateObject("Access.Application")
MyAccessApp.OpenCurrentDatabase ("sql.mdb")
Set DBRecordSet = MyAccessApp.CurrentDb.OpenRecordset
(SQLStatement)
MyAccessApp.Quit
Set MyAccessApp = Nothing
End Sub

Before I put in the reference to the recordset, I didn't
get the flash. So it seems that my problem is with using
the DAO recordset. Is there a better way to access the
table data in Access?

Thanks!
Susan
(e-mail address removed)

 

[Dirk Goldgar]

Hi Dirk!

Ok, I tried the following code and am getting the flash:

Private Sub PerformTestButton_Click()
Dim MyAccessApp As Access.Application
Dim DBRecordSet As DAO.Recordset
Dim SQLStatement As String

SQLStatement = "SELECT * from MajorFunctions"

Set MyAccessApp = CreateObject("Access.Application")
MyAccessApp.OpenCurrentDatabase ("sql.mdb")
Set DBRecordSet = MyAccessApp.CurrentDb.OpenRecordset
(SQLStatement)
MyAccessApp.Quit
Set MyAccessApp = Nothing
End Sub

Before I put in the reference to the recordset, I didn't
get the flash. So it seems that my problem is with using
the DAO recordset. Is there a better way to access the
table data in Access?

Indeed there is, Susan! Your problem, I think, comes from the fact that
you have a reference to the database open in your recordset object.
I'll bet your problem wouldn't occur if you closed the recordset and set
it to Nothing before exiting the application. *BUT* opening an instance
of Access is *not* what you should be doing if all you want is to
process Access data in VB.

Instead, just use DAO to open the database as a DAO Database object, and
then open a recordset from that object. There's no need to start an
instance of Access for this purpose. For example:

Dim DB As DAO.Database
Dim DBRecordSet As DAO.Recordset
Dim SQLStatement As String

SQLStatement = "SELECT * from MajorFunctions"

Set DB = OpenDatabase("sql.mdb")
Set DBRecordSet = DB.OpenRecordset(SQLStatement)

' ... work with recordset ...

DBRecordSet.Close
Set DBRecordSet = Nothing
DB.Close
Set DB = Nothing

Doing it this way will involve *much* less overhead than opening an
instance of Access.

 

[Susan]

Hi Dirk!

Thank you, thank you, thank you!!

Susan
(doing the happy dance!)

 

[Dirk Goldgar]

Hi Dirk!

Thank you, thank you, thank you!!

Susan
(doing the happy dance!)

LOL Wheee!