opening an access form from excel.

[keyur]

hi

i have a macro, that will save and close the file once
executed. I want to open and access form just before it
closes. this is what i found from VBA help

"The following example opens a Microsoft Access database
from another application through Automation and then opens
a form in that database.

You can enter this code in a Visual Basic module in any
application that can act as a COM component. For example,
you might run the following code from Microsoft Excel,
Microsoft Visual Basic, or Microsoft Access.

When the variable pointing to the Application object goes
out of scope, the instance of Microsoft Access that it
represents closes as well. Therefore, you should declare
this variable at the module level.

' Include the following in Declarations section of module.
Dim appAccess As Access.Application

Sub DisplayForm()
' Initialize string to database path.
Const strConPathToSamples = "C:\Program " _
& "Files\Microsoft Office\Office\Samples\"

strDB = strConPathToSamples & "Northwind.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples
' Open Orders form.
appAccess.DoCmd.OpenForm "Orders"
End Sub
"

but when i put this in Excel macro it shows me an
error "user-defined type not defined" for "Dim appAccess
As Access.Application", which i have in declaration.

since i quite new to VBA i might have just missed to
change something in the code.

thanks for any help.

 

[smalltalk_vb]

use

Set obAccess = new access.applicaton
instead of CreateObject("..."

 

[Guest]

In order to declare a variable as an "Access Application" your Visual Basic Environment needs to know the definition for that class. You can do this by going to Tools>References and putting a check next to Microsoft Access Library. This has the benefit of making your variables more effecient and while programming with that object, you can see the properties and methods built into it

-Brad Vontur

 

[onedaywhen]

Don't you mean:

use

Private appAccess As Object

instead of

Dim appAccess As Access.Application

--

 

[soalf]

' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples
Corect write:
appAccess.OpenCurrentDatabase strDB
becose strDB is string for database. I'm testing

 

[keyur]

thanks for all your help. but its still not working after
changing some stuff this is what i have

in declaration:
Private appAccess As Object

in module:
Sub DisplayForm()
' Initialize string to database path.
Const strConPathToSamples = "G:\Maintenance\CMMS\"

strDB = strConPathToSamples & "Stock Cards-35.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
' Open Orders form.
appAccess.DoCmd.OpenForm "Stock Cards"
End Sub

this doesnt do anything. doesnt even show any errors.

thanks again

 

[keyur]

i just realized that when i executed the macro apparently
it starts the access in the background (found tht through
task manager and the presence of ".ldb" format file at the
file location)

also i added the library "Microsoft Access 10.0 Object
Library"

thanks