Access Database Wont Stay Open

[iwantacookie]

Hi,

I did find another similar post but it didn't solve my specific problem and I didn't want to hijack it.

I am trying to convert my Excel 2003 workbooks to Excel 2007. My current macros all work great and one of them is to open an MS access 2003 db. I've converted the MS Access db to 2007 and the workbooks to 2007. Now when I run the macro to open the database, it opens for an instant, then immediately closes. I've stepped through the VBA script and it is working fine, but as soon as the macro hits "End Sub", Access closes.

From what I've gathered, I need to be using code to first open Access, and then open my database. This is not what my code does currently...

Currently, my code is this:

Dim acApp As Object
Set acApp = GetObject(AccessLocation, "access.application")
acApp.Visible = true

the AccessLocation, is a conglomeration of other bits of code i've put together to direct to a H:/XXX/XXX/XXX.accdb location (the prefix to the *.accdb file is always different thus the AccessLocation bit).

So, my question is, what do I change my code to?

I noticed some people using a "Shell" command? But I cannot work out how to get my AccessLocation bit into that.

HELP!

Thanks

 

[Roli001]

Try this:

Sub runAccess()

    Set a = CreateObject("Access.Application")
     
    strDB = "C:\Database2.accdb"
     
    a.OpenCurrentDataBase strDB ' open the database
    a.Visible = True
    a.Quit ' Close the database
     
    Set a = Nothing
    
End Sub

 

[iwantacookie]

I'm not creating a database though... i'm trying to open an existing one. the path will vary evertime, only the file name is constant.

my bit of code for for AccessLoction

file = ThisWorkBook.FullName
filename = ThisWorkBook.Name
position = InStr(1,file,filename,1)
position = position - 2
mypath = Left(file, position)
AccessLocation = mypath & "\" & "summary.accdb"

 

[Roli001]

The code doesn't create a database, it opens an existing database. Try it. Just update the path and filename for an existing database.

 

[Roli001]

I'm not creating a database though... i'm trying to open an existing one. the path will vary evertime, only the file name is constant.

my bit of code for for AccessLoction

file = ThisWorkBook.FullName
filename = ThisWorkBook.Name
position = InStr(1,file,filename,1)
position = position - 2
mypath = Left(file, position)
AccessLocation = mypath & "\" & "summary.accdb"

This is with your code:

Sub runAccess()

    file = ThisWorkbook.FullName
    Filename = ThisWorkbook.Name
    Position = InStr(1, file, Filename, 1)
    Position = Position - 2
    mypath = Left(file, Position)
    AccessLocation = mypath & "\" & "summary.accdb"
     
    Set a = CreateObject("Access.Application")
     
    strDB = AccessLocation
     
    a.OpenCurrentDataBase strDB
    a.Visible = True
    a.Quit
     
    Set a = Nothing
    
End Sub

 

[iwantacookie]

Nope, tried it (even without the quit bit) and it won't stay open.

 

[Roli001]

works for me just fine.