[RESOLVED] Testing for an instance of word

Hi RobDog,

I am having the exact same issue with my code. I get the "ActiveX component can't create object" error even with the On Error Resume Next code. How can I get around this? All I want to do is test for an instance of Access and if Access is open, close it. I am trying to avoid the exclusive lock error that occurs if the database I am ADO Connecting to is already open. Here is the beginning part of my code that contains the line of error (the line in which the error occurs is in red):

VB Code:

Private Sub UserForm_Initialize()
' Opens an ADO Connection to Issues Database and fills in arInfo fields with
' values from the database (ie Contacts, Categories, Status, etc).  This is _
' needed because the values from the DB are dynamic and can be changed by the user.
' This function should only be called 1 time when the "Add to AR Tracker" menu
' item is clicked, even if there are multiple selections in the ActiveExplorer.
On Error Resume Next
 
    Dim CnnA As ADODB.Connection
    Dim statusRS As ADODB.Recordset
    Dim priorityRS As ADODB.Recordset
    Dim categoryRS As ADODB.Recordset
    Dim assignedToRS As ADODB.Recordset
    Dim aApp As Access.Application
 
    Set CnnA = New ADODB.Connection
    Set statusRS = New ADODB.Recordset
    Set priorityRS = New ADODB.Recordset
    Set categoryRS = New ADODB.Recordset
    Set assignedToRS = New ADODB.Recordset
    [COLOR=Red]Set aApp = GetObject(, "Access.Application")[/COLOR]
   
    If TypeName(aApp) <> "Nothing" Then
        'An Access instance is running
        If MsgBox("Outlook must close your AR Tracker database in order to perform the requested action." _
        & vbCr & "Do you want to Outlook to close your AR Tracker?", vbYesNo, "Close AR Tracker?") = vbYes Then
            aApp.Quit
        Else: GoTo End_Sub
        End If
    End If

Wouldnt it be better to try to connect to the db exclusively and trap for that error as the user may have a different db open and not yours?

Hi RiceRocket,

Sorry I can't shed any light on this - it is working fine for me. I'm sure RobDog will have the answer.

That would be the best way, RobDog... I was hoping there would be a way to trap this error... can you show me how to trap this kind of error. The error occurs on the CnnA.Open line.

And how do I connect exclusively?

Thanks for your reply Chimaera_Trev!

ODBC connectionstring for exxclusive connection:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="

Just take out your On Error Resume Next declaration and change it like so.

VB Code:

Private Sub UserForm_Initialize()
' Opens an ADO Connection to Issues Database and fills in arInfo fields with
' values from the database (ie Contacts, Categories, Status, etc).  This is _
' needed because the values from the DB are dynamic and can be changed by the user.
' This function should only be called 1 time when the "Add to AR Tracker" menu
' item is clicked, even if there are multiple selections in the ActiveExplorer.
On Error GoTo MyError
    Dim CnnA As ADODB.Connection
    Dim statusRS As ADODB.Recordset
    Dim priorityRS As ADODB.Recordset
    Dim categoryRS As ADODB.Recordset
    Dim assignedToRS As ADODB.Recordset
    Dim aApp As Access.Application
 
    Set CnnA = New ADODB.Connection
    Set statusRS = New ADODB.Recordset
    Set priorityRS = New ADODB.Recordset
    Set categoryRS = New ADODB.Recordset
    Set assignedToRS = New ADODB.Recordset
    'Set aApp = GetObject(, "Access.Application")
   
    CnnA.Connectionstring = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd=" 
    CnnA.Open
    '...
 
    Exit Sub
MyError:
    If Err.Number = "whatever the exclusive error number is" Then
        MsgBox "DB Can Not Be Opened Exclusive."
    Else
        MsgBox "Other Error"
    End If
End Sub

I don't know why, but whenever I encounter an error, a run-time error dialog appears, but does not go to MyError. This was happening with the GetObject error as well. Any ideas?

You dont need to be using it as shown in my example.

Ps, thread split.

This is how I have my Sub set up now, using your example as a guide:

VB Code:

Private Sub UserForm_Initialize()
' Opens an ADO Connection to Issues Database and fills in arInfo fields with
' values from the database (ie Contacts, Categories, Status, etc).  This is _
' needed because the values from the DB are dynamic and can be changed by the user.
' This function should only be called 1 time when the "Add to AR Tracker" menu
' item is clicked, even if there are multiple selections in the ActiveExplorer.
On Error GoTo MyError
 
    Dim CnnA As ADODB.Connection
    Dim statusRS As ADODB.Recordset
    Dim priorityRS As ADODB.Recordset
    Dim categoryRS As ADODB.Recordset
    Dim assignedToRS As ADODB.Recordset
    'Dim aApp As Access.Application
 
    Set CnnA = New ADODB.Connection
    Set statusRS = New ADODB.Recordset
    Set priorityRS = New ADODB.Recordset
    Set categoryRS = New ADODB.Recordset
    Set assignedToRS = New ADODB.Recordset
    'Set aApp = GetObject(, "Access.Application")
   
    'If TypeName(aApp) <> "Nothing" Then
    '    'An Access instance is running
    '    If MsgBox("Outlook must close your AR Tracker database in order to perform the requested action." _
    '    & vbCr & "Do you want to Outlook to close your AR Tracker?", vbYesNo, "Close AR Tracker?") = vbYes Then
    '        aApp.Quit
    '    Else: GoTo End_Sub
    '    End If
    'End If
    
    CnnA.connectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Documents and Settings\edmastro.AMR\My Documents\Issues Database.mdb;Exclusive=1;Uid=admin;Pwd=" '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\edmastro.AMR\My Documents\Issues Database.mdb';Persist Security Info=False"
    CnnA.Open

VB Code:

MyError:
    If Err.Number = "-2147467259 (80004005)" Then
        MsgBox "DB Can Not Be Opened Exclusive."
    Else
        MsgBox "Other Error"
    End If

What I'm trying to say is if there is a run time error, the sub never goes to MyError, it just displays a run-time error dialog with Debug, End buttons.

In your VBA IDE in the Tools > Options > General tab > make sure "Break on unhandled errors" is selected.


Edit: woot! 27,000 Posts :)

Ahhhhh... that did it... Thanks!!!

Ahhhhh... that did it... Thanks!!! :D

Edit: Maybe if i keep doing this, I will soon have 27,000 posts!!! ;)

Hi RobDog,

Is there a way to check for an open ADODB connection? I want to be able to check for an Open ADO Connection before trying to open one.

You will use your suspect recordset object and test for it being initialzed and then the connection state.

VB Code:

If TypeName(oRs) <> "Nothing Then
    If oRs.State = adStateOpen Then
        'Reuse
    Else
        'ReOpen
    End If
Else
    'Not initialized to create New.
End If

Is there anyway to test the connection itself? I usually encounter an error when attmepting to open a connection since an ADODB connection is required to open a recordset. If there is no way to check for an open connection, then I will use the recordset checki.

Change oRs to your adodb.connection object. The logic and code is the same.

Thanks RobDog!