VBS Userform code to load Access

[ScottyFl]

Hello
FRUSTRATED...

I am trying to load Access 2013 through my userform in 2013 Excel using VBA.

I have downloaded and tried the following pieces of code..

      'Access object
      'Dim appAccess As Object
      'create new access object
      'Set appAccess = CreateObject("Access.Application")
      'open the acces project
      'Call appAccess.OpenCurrentDatabase("R:\Sample.accdt")
      'appAccess.Visible = True
      
      'the path to create the new access database
'Dim strPath As String
'an Access object
'Dim objAccess As Object


'strPath = "R:\NewDB"
'Set objAccess = CreateObject("Access.Application")
'Call objAccess.NewCurrentDatabase(strPath)
'objAccess.Quit




'Access object
'Dim appAccess As Object


'create new access object
'Set appAccess = CreateObject("Access.Application")
'open the acces project
'Call appAccess.OpenCurrentDatabase( _
'"R:\NewDB.accdb")
'appAccess.Visible = True


'Dim db As Access.Application
 'Set db = New Access.Application
'db.OpenCurrentDatabase "R:\NewDB.accdb"
'db.Application.Visible = True


'Global oApp As Object


    'Sub OpenAccess()
    'On Error Resume Next


        'Dim LPath As String
        'Dim LCategoryID As Long
        'Dim DATABASE As String
        'Static oApp As Access.Application
        
        'DATABASE = "R:\NewDB.accdb"


        'Set oApp = GetObject(, "Access.Application")
        'If (Err.Number <> 0) _
       ' Or (oApp.CurrentDb.Name <> DATABASE) Then
        
        'Set oApp = Nothing
        'Set oApp = CreateObject("Access.Application")
            'oApp.Visible = True
            'oApp.OpenAccessProject "R:\NewDB.accdb"
            
            'Set oApp = CreateObject("Access.Application")
            'oApp.Visible = True
            'oApp.OpenCurrentDatabase DATABASE
            
       'End If


        'Open form called Categories filtering by CategoryID
        'LLocation = Range("A2").Value
        'oApp.DoCmd.OpenForm "ReviewEBDOrdersFrm", , , "[Circuit]=" & "'" & LLocation & "'"


strdb = "R:\NewDB.accdb"
Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.OpenCurrentDatabase strdb
appAccess.DoCmd.OpenForm
Set appAccess = Nothing

Many and many different attempts.

Name of my database is... "R:\NewDB.accdb"

most of the code loads Access then it immediately closes down.

I have a master control panel userform that drives my excel file. I want to click on a button and Access loads up.. AND STAYS OPEN. One of the pieces came close except when I closed my userform access followed.

FRUSTRATED...

Scott

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Have you tried:

    rc = Shell("R:\NewDB.accdb")

 

[ScottyFl]

Hi and welcome to the MrExcel Message Board.

Have you tried:

    rc = Shell("R:\NewDB.accdb")

WELL
As you can tell I submitted a hodge podge of code.

Why don't you include your statement in the complete code.

Remember it is coming from a user form in excel, not just a macro. The ones that almost worked would bring up Access then close it down.

Scott

 

[RickXL]

Sorry, I don't understand.

What I posted is the complete code. It should work wherever you want to put it.

 

[AkaTrouble]

WELL
As you can tell I submitted a hodge podge of code.

Why don't you include your statement in the complete code.

Remember it is coming from a user form in excel, not just a macro. The ones that almost worked would bring up Access then close it down.

Scott

try copy the one line of code to your code every time you have file open for access use the code just change the filename from new to your file location


("R:\NewDB.accdb") = ("c:\my files\MyFilename.accdb")</pre>

 

[ScottyFl]

thanks
not looking for sniblets.

i'm looking for the complete sub that will open, and STAY OPEN, an Access file.
remember it is being called from an Excel USERFORM, not a stand alone macro.

 

[Norie]

Scott

If all you want to do is open an Access database that's all you need.

The code you originally posted appears to be for automating Access and if that's what you actually want to to I think we need more information.

 

[ScottyFl]

thats all you need?

I see I am not getting anywhere here. This is bogus.

 

[Norie]

Have you tried the code?

I did with, obviously with my own Access database, and it opens the database.

If you want to to more than open the database, eg interact with a form, pull data via a query, run a report etc, can you give more information?

 

[ScottyFl]

OK Norie

I did as you said and put this line of code in my userform

("R:\NewDB.accdb") = ("c:\my files\MyFilename.accdb")

amazing... I got a compile error.

Thanks for your help. It is obvious you have no clue. Like to see your words in print

Have you tried the code?

I did with, obviously with my own Access database, and it opens the database.

If you want to to more than open the database, eg interact with a form, pull data via a query, run a report etc, can you give more information?