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

 

[Norie]

Scott

Eh, that wasn't the code I was referring to.

It was the code from RickXL in post #2 I was referring to.

By the way, I'm pretty sure we could help if you were you give us an idea of what you actually want to do.

PS I did mock something up that involved opening a form in an Access database from a userform in an Excel workbook, using data from the userform as criteria to filter the data. Not sure is that's the sort of thing you are looking for.

 

[ScottyFl]

OK
You must admit this is getting nowhere. I have included routines that either people have sent me or I got from forums like this.

THEY ALL DON'T WORK.

I have been getting very stupid responses like put this line in or do this line.

Here is the subs. The last is just a mess of code. All don't work.

The last loads access but as soon as the userform closes. low and behold...so does access..
'Sub 1
'Dim appAccess As Object
'Set appAccess = CreateObject("Access.Application")
'Call appAccess.OpenCurrentDatabase("R:\NewDB.accdb")
'appAccess.Visible = True


'Sub 2
'Set MyAccess = CreateObject("Access.Application")
'MyAccess.Visible = True
'MyAccess.OpenCurrentDatabase ("R:\NewDB.accdb")



'Sub 3


'Dim LPath As String
'Dim LCategoryID As Long
'LPath = "R:\NewDB.accdb"
'Set oApp = CreateObject("Access.Application")
'oApp.Visible = True
'oApp.OpenCurrentDatabase LPath


'Sub 4
'strdb = "R:\NewDB.accdb"
'Set appAccess = CreateObject("Access.Application")
'appAccess.OpenCurrentDatabase strdb
'appAccess.DoCmd.OpenForm "frmsysteminformation"
'Set appAccess = Nothing


'Sub 5
'Static appXS As Object
'Set appXS = CreateObject("Access.Application")
'appXS.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

 

[Norie]

Before I post anything can I just make sure what you want to do?

You want to open a database in Access from a userform in Excel, then open a form in the database and have it filtered on the field 'Circuit' using a value from the Excel worksheet for the criteria?

Is that right?

 

[AkaTrouble]

although i sense you are getting frustrated in getting help to solve what it appears you think is a simple issue

it is only simple once we know the answer

the questions asked of you have been in order to try to help you

no one has made any assumptions and the questions are relative for those that are trying to gleen information to help

lets try again i will ask a couple of questions and lets see if we can get back on track

===========================================================

what is your aim what are you trying to do ?

are you simply trying to open a new access file from within excel?

are you trying to open an existing file from within excel?

are you trying to open a new access file and have Access properties or settings in this new file set during the file creation process ?

are you trying to create a new access file with properties and then copy excel data into this file?

this may help us understand better and help you

 

[RoryA]

Scott,

I appreciate that you are frustrated but I suggest you change your tone if you expect people to carry on trying to help you.

Pending your answers to Norie's questions, all you should really need is:

Sub OpenAccess()
    With CreateObject("Access.Application")
        .OpenCurrentDatabase "R:\NewDB.accdb"
        .UserControl = True
    End With
End Sub

to open a database and leave it open.

 

[AkaTrouble]

Before I post anything can I just make sure what you want to do?

You want to open a database in Access from a userform in Excel, then open a form in the database and have it filtered on the field 'Circuit' using a value from the Excel worksheet for the criteria?

Is that right?

sorry you posted while i was typing my reply

 

[Norie]

This is what works for me, I think the important bit is the UserControl part.

Private Sub CommandButton1_Click()
Dim oApp As Object
Dim LPath As String
Dim LCategoryID As String
Dim DATABASE As String

    LPath = "C:\Documents\"

    DATABASE = "World.accdb"

    Set oApp = CreateObject("Access.Application")
    
    oApp.Visible = True
    
    oApp.OpenCurrentDatabase LPath & DATABASE

    LCategoryID = "K*"
    oApp.DoCmd.OpenForm "frmCity", , , "[Name]Like '" & LCategoryID & "'"

    oApp.UserControl = True

    Set oApp = Nothing
    
End Sub

Obviously I've changed some things to accomodate for my database/form so you'll need to make some changes.

By the way, if 'Circuit' is a number field you don't need the single quotes.

LLocation = Range("A2").Value
oApp.DoCmd.OpenForm "ReviewEBDOrdersFrm", , , "[Circuit]=" & & LLocation

 

[ScottyFl]

Tone?
maybe. Just frustrated by people on these forums that waste my time.

I plugged your code in and it works. Much appreciated.

Glad to see I finally ran into someone who understands what I was asking and sent me the sub.

Thanks again

Happy holidays to you and yours.

Scott

Scott,

I appreciate that you are frustrated but I suggest you change your tone if you expect people to carry on trying to help you.

Pending your answers to Norie's questions, all you should really need is:

Sub OpenAccess()
    With CreateObject("Access.Application")
        .OpenCurrentDatabase "R:\NewDB.accdb"
        .UserControl = True
    End With
End Sub

to open a database and leave it open.

 

[ScottyFl]

I get the feeling that:

.UserControl = True

was the key

Thanks again


.UserControl = True</pre>

 

[Norie]

Scott

Glad you got a solution.

One thing I'd like to say though is that this forum is free, the members here aren't paid and we give our free time to try and help people.

PS This is probably the best forum to use for this kind of help.