• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1102
  • Last Modified:

Open Access table from Excel vba - Office 2007

I have s small macro intended to open a table in Access for viewing. The code executes, but the database only remains open long enough to immediately close! How to address so the access table remains visible?
Public Sub open_dbase()

    Dim LPath As String
    Dim LCategoryID As Long

    'Path to Access database
    LPath = Sheet2.Cells(5, 2)

    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
   

    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath
    oApp.DoCmd.OpenTable "tblRawData", acViewNormal
    oApp.Visible = True
End Sub

Open in new window

0
JP_TechGroup
Asked:
JP_TechGroup
  • 3
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try adding the line of code shown below:


    Dim LPath As String
    Dim LCategoryID As Long

    'Path to Access database
    LPath = Sheet2.Cells(5, 2)

    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
   

    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath
    oApp.DoCmd.OpenTable "tblRawData", acViewNormal

    oApp.Visible = True
    oApp.UserControl = True   ' ************ ADD THIS LINE

mx
0
 
rockiroadsCommented:
what do you want to achieve by opening the table?

if u want the data from it then use a recordset

or are you actually wanting msaccess to open and remain open with that table open
0
 
rockiroadsCommented:
Have you double checked that access is still left opened? it might be behind your excel window, check the taskbar
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
JP_TechGroupAuthor Commented:
That did it. Thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

mx
0
 
rockiroadsCommented:
so mx, whats the secret? I gave it a little test from excel and for me db with table remains open, didnt need the usercontrol
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Secret ?
Not sure.  It was the only thing I could think of.  I have in my database loader, which keeps the loaded mdb open, then closes the loader.

mx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now