OpenDatabase Working?

[Guest]

I'm looking for a way to verify that a database is open after running the
OpenDatabase method.

I purchased and ran the AccessAnalyzer (very cool tool) and one of the
peformance recommendations was to open the database for linked tables. I've
set the following code to run OnOpen event of my splashscreen:

'open Back_End
Dim wrkJet As Workspace
Dim dbscurrent As Database

'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database
Set dbscurrent = wrkJet.OpenDatabase("O:\SalesDB\SalesDB_be.mdb", False)

How can I verify that the database is actually opened? (checked the server
for open files and the SalesDB_be.mdb database does not appear as opened).

Thanks in advance for your help.

Gary

 

[Dirk Goldgar]

I'm looking for a way to verify that a database is open after running
the OpenDatabase method.

I purchased and ran the AccessAnalyzer (very cool tool) and one of the
peformance recommendations was to open the database for linked
tables. I've set the following code to run OnOpen event of my
splashscreen:

'open Back_End
Dim wrkJet As Workspace
Dim dbscurrent As Database

'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database
Set dbscurrent = wrkJet.OpenDatabase("O:\SalesDB\SalesDB_be.mdb",
False)

How can I verify that the database is actually opened? (checked the
server for open files and the SalesDB_be.mdb database does not appear
as opened).

Thanks in advance for your help.

I'm not sure what you mean when you say you checked for open files, but
you could look for the existence of the matching .ldb file:
"O:\SalesDB\SalesDB_be.ldb". I'd expect that the OpenDatabase method
would raise an error if the database wasn't opened, anyway.

Does your splash screen form close after a brief period, or after
opening another form? If so, the Database and Workspace objects will be
destroyed when it does (unless they are actually declared in a standard
module, and not in the form's module), and so the connection to the
back-end will be closed. To make this sort of thing work, you need to
maintain a persistent connection to the back-end, and that means putting
it in a module that won't be unloaded while the application is open.
You could just hide your splash screen, rather than closing it, to make
that happen.

 

[Guest]

Thanks, Dirk. I'm the IT Manager so I opened a terminal service connection
to the server (that the SalesDB resides on) and checked for open files. No
files were open and there is no associated LDB file for the back-end.

Splash screen doesn't close (hidden) and I placed the wrong path into the
code as a test and there were no errors so it appears as though the method
doesn't check it.

Any other suggestions? Again, thanks.

Gary

 

[Dirk Goldgar]

Thanks, Dirk. I'm the IT Manager so I opened a terminal service
connection to the server (that the SalesDB resides on) and checked
for open files. No files were open and there is no associated LDB
file for the back-end.

Splash screen doesn't close (hidden) and I placed the wrong path into
the code as a test and there were no errors so it appears as though
the method doesn't check it.

If your declarations for the workspace and the database are made in the
Form_Open event procedure, as you said, and not at the module level, the
same problem applies: as soon as that procedure exists, the objects
will be destroyed. So that could be part of your problem, whch could be
solved by simply moving those Dim statements to the Declarations section
of the form's module.

It's odd that you get no error when you run the code with a non-existent
database name. When I try it, I get this error:

---------------------------
Microsoft Visual Basic
---------------------------
Run-time error '3024':

Could not find file 'C:\Temp\foo.mdb'.
---------------------------
OK Help
---------------------------

I can't explain why you wouldn't. have you stepped through the code in
debug mode to see exactly what is happening?

Incidentally, I'm not sure why you need to open an entirely separate
connection to the back-end. I'd think it sufficient just to open a
persistent recordset on one of the linked tables, as in

'---- start of example code for form module ----
Option Compare Database
Option Explicit

Dim m_rsBackend As DAO.Recordset

Private Sub Form_Open(Cancel As Integer)

Set m_rsBackend = DBEngine(0)(0).OpenRecordset("tblProfile")

End Sub

Private Sub Form_Close()

On Error Resume Next
m_rsBackend.Close
Set m_rsBackend = Nothing

End Sub
'---- end of example code for form module ----