How can I determine if my database connection is open

[Colin Steadman]

I have a template with an AutoNew macro. The AutoNew macro prompts the user for an employee number, gets data from a database and builds a standard document. If the user wants to create another document, they can click a button in a Custom Toolbar to re-run AutoNew. So far so good

What I'd like to do in my DatabaseConnect function is check if the connection object is still open. If it is there is no need to reconnect to the database again. The problem is that my connection object doesn't seem to have any properties I can check to determine this. Is this actually possible

Regards

Coli

======================================================================
Public ws As DAO.Workspace ' Create a work spac
Public cn As DAO.Connection ' Create a connection va

Sub AutoNew(

If Not DatabaseConnect Then En

getPayrollNoForPAN.Sho

If fetchDataFromDatabase(1) The
i = fetchDataFromDatabase(2
i = fetchDataFromDatabase(3
i = fetchDataFromDatabase(4
i = fetchDataFromDatabase(5
Els
MsgBox "No data were found for requested payroll number", vbInformation, "No data found
End I

putPreparedDat
RefreshDocVariable

End Su


'-- Connect to Remu
Public Function DatabaseConnect(

On Error Resume Nex

'<<<< Need to check that connection not already open >>>

Set ws = DBEngine.CreateWorkspace("MyWorkspace", "admin", "", dbUseODBC
Set cn = ws.OpenConnection("ODBCConnection", dbDriverNoPrompt, False, "ODBC;DATABASE=live;UID=;PWD=;DSN=remus"

If Err.Number = 0 The
DatabaseConnect = Tru
Els
ans = MsgBox("IngresNet not running. Attempt to start IngresNet now?", vbYesNo, "Connection Failed"
If ans = 6 The
Shell32.ShellAndWait ("ingstart"
MsgBox "IngresNet started.", vbInformation, "Ingstart complete
End I
DatabaseConnect = Fals
End I

On Error GoTo

End Functio

=======================================================================

 

[Word Heretic]

G'day "Colin Steadman" <[email protected]>,

Either declare the object that holds the connection to be static or
decalre it as a global public var in a code module.

Steve Hudson - Word Heretic Sydney Australia
Tricky stuff with Word or words

Email: steve at wordheretic.com


Colin Steadman reckoned:

 

[Colin Steadman]

G'day "Colin Steadman" <[email protected]>,

Either declare the object that holds the connection to be static or
decalre it as a global public var in a code module.

G'day Steve, could you expand on your static connection suggestion.
I'm not sure what a static connection is and I cant find anything in
my VBA Unleashed manual on it either.

TIA,

Colin

 

[Word Heretic]

G'day (e-mail address removed) (Colin Steadman),

Static SomeVar as ObjectType


Steve Hudson - Word Heretic Sydney Australia
Tricky stuff with Word or words

Email: steve at wordheretic.com


Colin Steadman reckoned: