• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1248
  • 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 Access MVP)Database ArchitectCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
JP_TechGroupAuthor Commented:
That did it. Thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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