Results 1 to 16 of 16

Thread: [RESOLVED] Testing for an instance of word

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Resolved [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:
    1. Private Sub UserForm_Initialize()
    2. ' Opens an ADO Connection to Issues Database and fills in arInfo fields with
    3. ' values from the database (ie Contacts, Categories, Status, etc).  This is _
    4. ' needed because the values from the DB are dynamic and can be changed by the user.
    5. ' This function should only be called 1 time when the "Add to AR Tracker" menu
    6. ' item is clicked, even if there are multiple selections in the ActiveExplorer.
    7. On Error Resume Next
    8.  
    9.     Dim CnnA As ADODB.Connection
    10.     Dim statusRS As ADODB.Recordset
    11.     Dim priorityRS As ADODB.Recordset
    12.     Dim categoryRS As ADODB.Recordset
    13.     Dim assignedToRS As ADODB.Recordset
    14.     Dim aApp As Access.Application
    15.  
    16.     Set CnnA = New ADODB.Connection
    17.     Set statusRS = New ADODB.Recordset
    18.     Set priorityRS = New ADODB.Recordset
    19.     Set categoryRS = New ADODB.Recordset
    20.     Set assignedToRS = New ADODB.Recordset
    21.     [COLOR=Red]Set aApp = GetObject(, "Access.Application")[/COLOR]
    22.    
    23.     If TypeName(aApp) <> "Nothing" Then
    24.         'An Access instance is running
    25.         If MsgBox("Outlook must close your AR Tracker database in order to perform the requested action." _
    26.         & vbCr & "Do you want to Outlook to close your AR Tracker?", vbYesNo, "Close AR Tracker?") = vbYes Then
    27.             aApp.Quit
    28.         Else: GoTo End_Sub
    29.         End If
    30.     End If
    Last edited by RobDog888; Apr 27th, 2006 at 01:58 PM.

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Testing for an instance of word

    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?
    Last edited by RobDog888; Apr 27th, 2006 at 01:58 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3
    New Member
    Join Date
    Apr 2006
    Posts
    4

    Re: Testing for an instance of word

    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.
    Last edited by RobDog888; Apr 27th, 2006 at 01:58 PM.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: Testing for an instance of word

    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!
    Last edited by RobDog888; Apr 27th, 2006 at 01:58 PM.

  5. #5
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Testing for an instance of word

    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:
    1. Private Sub UserForm_Initialize()
    2. ' Opens an ADO Connection to Issues Database and fills in arInfo fields with
    3. ' values from the database (ie Contacts, Categories, Status, etc).  This is _
    4. ' needed because the values from the DB are dynamic and can be changed by the user.
    5. ' This function should only be called 1 time when the "Add to AR Tracker" menu
    6. ' item is clicked, even if there are multiple selections in the ActiveExplorer.
    7. On Error GoTo MyError
    8.     Dim CnnA As ADODB.Connection
    9.     Dim statusRS As ADODB.Recordset
    10.     Dim priorityRS As ADODB.Recordset
    11.     Dim categoryRS As ADODB.Recordset
    12.     Dim assignedToRS As ADODB.Recordset
    13.     Dim aApp As Access.Application
    14.  
    15.     Set CnnA = New ADODB.Connection
    16.     Set statusRS = New ADODB.Recordset
    17.     Set priorityRS = New ADODB.Recordset
    18.     Set categoryRS = New ADODB.Recordset
    19.     Set assignedToRS = New ADODB.Recordset
    20.     'Set aApp = GetObject(, "Access.Application")
    21.    
    22.     CnnA.Connectionstring = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="
    23.     CnnA.Open
    24.     '...
    25.  
    26.     Exit Sub
    27. MyError:
    28.     If Err.Number = "whatever the exclusive error number is" Then
    29.         MsgBox "DB Can Not Be Opened Exclusive."
    30.     Else
    31.         MsgBox "Other Error"
    32.     End If
    33. End Sub
    Last edited by RobDog888; Apr 27th, 2006 at 01:59 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: Testing for an instance of word

    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?
    Last edited by RobDog888; Apr 27th, 2006 at 01:59 PM.

  7. #7
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Testing for an instance of word

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

    Ps, thread split.
    Last edited by RobDog888; Apr 27th, 2006 at 02:00 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: Testing for an instance of word

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

    VB Code:
    1. Private Sub UserForm_Initialize()
    2. ' Opens an ADO Connection to Issues Database and fills in arInfo fields with
    3. ' values from the database (ie Contacts, Categories, Status, etc).  This is _
    4. ' needed because the values from the DB are dynamic and can be changed by the user.
    5. ' This function should only be called 1 time when the "Add to AR Tracker" menu
    6. ' item is clicked, even if there are multiple selections in the ActiveExplorer.
    7. On Error GoTo MyError
    8.  
    9.     Dim CnnA As ADODB.Connection
    10.     Dim statusRS As ADODB.Recordset
    11.     Dim priorityRS As ADODB.Recordset
    12.     Dim categoryRS As ADODB.Recordset
    13.     Dim assignedToRS As ADODB.Recordset
    14.     'Dim aApp As Access.Application
    15.  
    16.     Set CnnA = New ADODB.Connection
    17.     Set statusRS = New ADODB.Recordset
    18.     Set priorityRS = New ADODB.Recordset
    19.     Set categoryRS = New ADODB.Recordset
    20.     Set assignedToRS = New ADODB.Recordset
    21.     'Set aApp = GetObject(, "Access.Application")
    22.    
    23.     'If TypeName(aApp) <> "Nothing" Then
    24.     '    'An Access instance is running
    25.     '    If MsgBox("Outlook must close your AR Tracker database in order to perform the requested action." _
    26.     '    & vbCr & "Do you want to Outlook to close your AR Tracker?", vbYesNo, "Close AR Tracker?") = vbYes Then
    27.     '        aApp.Quit
    28.     '    Else: GoTo End_Sub
    29.     '    End If
    30.     'End If
    31.    
    32.     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"
    33.     CnnA.Open

    VB Code:
    1. MyError:
    2.     If Err.Number = "-2147467259 (80004005)" Then
    3.         MsgBox "DB Can Not Be Opened Exclusive."
    4.     Else
    5.         MsgBox "Other Error"
    6.     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.

  9. #9
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Testing for an instance of word

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


    Edit: woot! 27,000 Posts
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: Testing for an instance of word

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

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: Testing for an instance of word

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

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

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: [RESOLVED] Testing for an instance of word

    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.

  13. #13
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: [RESOLVED] Testing for an instance of word

    You will use your suspect recordset object and test for it being initialzed and then the connection state.
    VB Code:
    1. If TypeName(oRs) <> "Nothing Then
    2.     If oRs.State = adStateOpen Then
    3.         'Reuse
    4.     Else
    5.         'ReOpen
    6.     End If
    7. Else
    8.     'Not initialized to create New.
    9. End If
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: [RESOLVED] Testing for an instance of word

    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.

  15. #15
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: [RESOLVED] Testing for an instance of word

    Change oRs to your adodb.connection object. The logic and code is the same.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Feb 2006
    Posts
    131

    Re: [RESOLVED] Testing for an instance of word

    Thanks RobDog!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width