Running Access from Excel

[Stemdriller]

Hi All,

I am led to believe that we can run various Access forms and queries etc from Excel!

I have used the code below, but when it runs it doesn't error but it doesn't do anything.

I am running Access and Excel 2007.

Can anyone enlighten me.

Thanks


'BEGIN CODE
Dim strDBName As String

Set appAccess = CreateObject("Access.Application")
strDBName = "E:\CustomerSatisfactionDatabase.accdb"

appAccess.OpenCurrentDatabase strDBName

appAccess.DoCmd.OpenQuery "qryTurbo3"

'END CODE

 

[Galaxiom]

Try adding this line.

appAccess.Visible = True

 

[vbaInet]

Try adding this line.

appAccess.Visible = True

... after opening the db or after opening the query of course.

 

[Stemdriller]

Try adding this line.

appAccess.Visible = True

Hi Thanks for replying

The code now fires up Access but only briefly then shutsdown.

 

[Galaxiom]

If you have been running the code invisibly many times you may have many Access sessions running and choking the memory. Take a look in the Task Manager and close them down.

 

[Stemdriller]

If you have been running the code invisibly many times you may have many Access sessions running and choking the memory. Take a look in the Task Manager and close them down.

Nope, nothing is running in the background

My code thus far is

Sub Button1_Click()

'BEGIN CODE
Dim strDBName As String
Set appAccess = CreateObject("Access.Application")
strDBName = "E:\Customer Satisfaction Database\CustomerSatisfactionDatabaseMK2.accdb"
appAccess.OpenCurrentDatabase strDBName
appAccess.DoCmd.OpenForm "frmMainMenu"
appAccess.Visible = True
'END CODE
End Sub

 

[boblarson]

Nope, nothing is running in the background

My code thus far is

Sub Button1_Click()

'BEGIN CODE
Dim strDBName As String
Set appAccess = CreateObject("Access.Application")
strDBName = "E:\Customer Satisfaction Database\CustomerSatisfactionDatabaseMK2.accdb"
appAccess.OpenCurrentDatabase strDBName
appAccess.DoCmd.OpenForm "frmMainMenu"
appAccess.Visible = True
'END CODE
End Sub

Change your code to this and see if that helps:

Dim strDBName As String
[B]Dim appAccess As Access.Application[/B]
 
[B]Set appAccess = New Access.Application[/B]
 
appAccess.Visible = True
 
strDBName = "E:\Customer Satisfaction Database\CustomerSatisfactionDatabaseMK2.accdb"
 
appAccess.OpenCurrentDatabase strDBName
 
[B]appAccess.DoCmd.RunCommand acCmdAppMaximize[/B]
 
appAccess.DoCmd.OpenForm "frmMainMenu"

I just tested that and it works fine for me.

 

[Stemdriller]

Change your code to this and see if that helps:

Dim strDBName As String
[B]Dim appAccess As Access.Application[/B]
 
[B]Set appAccess = New Access.Application[/B]
 
appAccess.Visible = True
 
strDBName = "E:\Customer Satisfaction Database\CustomerSatisfactionDatabaseMK2.accdb"
 
appAccess.OpenCurrentDatabase strDBName
 
[B]appAccess.DoCmd.RunCommand acCmdAppMaximize[/B]
 
appAccess.DoCmd.OpenForm "frmMainMenu"

I just tested that and it works fine for me.

Bob

Thanks for replying

Ran the code and got 'Compile Error' User-defined type not defined.
It doesn't like Dim appAccess As Access.Application

Gareth

 

[Stemdriller]

Bob

Thanks for replying

Ran the code and got 'Compile Error' User-defined type not defined.
It doesn't like Dim appAccess As Access.Application

Gareth

Thanks for replying

Ran the code and got 'Compile Error' User-defined type not defined.
It doesn't like Dim appAccess As Access.Application

 

[JANR]

Ran the code and got 'Compile Error' User-defined type not defined.
It doesn't like Dim appAccess As Access.Application

The code Bob gave you uses early binding so you haven't set a refrence to Microsoft Access xx.0 Object Library.

You find it under Tools -->Refrences

JR

 

[boblarson]

The code Bob gave you uses early binding so you haven't set a refrence to Microsoft Access xx.0 Object Library.

You find it under Tools -->Refrences

JR

Or to be safe (version non-specific - change to late binding. I wasn't thinking straight and was thinking you were opening from another Access database for some unknown reason - my mind wasn't too clear yesterday I think. And I forgot just now that you were using late binding before but perhaps not with the declaration and not with the visible.).

Dim strDBName As String
[B]Dim appAccess As Object[/B]
 
[B]Set appAccess = CreateObject("Access.Application")[/B]
 
appAccess.Visible = True
 
strDBName = "E:\Customer Satisfaction Database\CustomerSatisfactionDatabaseMK2.accdb"
 
appAccess.OpenCurrentDatabase strDBName
 
[B]appAccess.DoCmd.RunCommand acCmdAppMaximize[/B]
 
appAccess.DoCmd.OpenForm "frmMainMenu"

 

[JANR]

You might supply a constant for specific access commands.

more here: http://msdn.microsoft.com/en-us/library/aa172255(v=office.11).aspx

Dim strDBName As String
[B]Dim appAccess As Object[/B]
[COLOR=red][/COLOR]
[COLOR=red]const acCmdAppMaximize = 10[/COLOR]
 
[B]Set appAccess = CreateObject("Access.Application")[/B]
 
appAccess.Visible = True
 
strDBName = "E:\Customer Satisfaction Database\CustomerSatisfactionDatabaseMK2.accdb"
 
appAccess.OpenCurrentDatabase strDBName
 
[B]appAccess.DoCmd.RunCommand acCmdAppMaximize[/B]
 
appAccess.DoCmd.OpenForm "frmMainMenu"

JR

 

[boblarson]

You might supply a constant for specific access commands.

Yep, I'm continuing the "brain-dead" session from yesterday .