Running Access from Excel

[Daxton A.]

Im somewhat of a beginner. I know a little vb and im just starting to get my feet wet in OLE. Now what i currently have is working but it has alot of useless steps. I keep my data in access. I run a form that runs a query based off the inputed data from the form. Access then exports the data into a user-named excel file. I then have to use a macro in excel that prompts the user to browse for the data file. It then opens it, does what it needs with the data and the data file is never used again.

Nirvana:
I want to run a form within excel that opens access >> the correct db >> and then runs the query based on the data inputed on the form in excel. Then I want the retrieved data to be applied directly into excel instead of saving the data to an individual file.

What will satisfy me:
I currently have a button that is nothing more than a shortcut to the form in access. It opens access, then the DB, then the correct form. But that's as for as i've taken it. I feel like i can figure the jest of this out but I dont know how to get past opening the form. How do I access the controls on the form just like they were local to excel? If i could do that I would still have data files but the user would only have to work out of excel instead of going into access....exporting the data and then going back to excel to import the data. It would be nice for them if excel exported the data based on prompted inputs and then closed out the db and access.

ANY AND ALL HELP IS APPRECIATED!

 

[scottfarcus]

email me at farcuss@hotmail.com

 

[Daxton A.]

.

will do

 

[scottfarcus]

This code will start Access, open your database, and run your query. We can work from here...

NOTE: You must first set a reference to the Access Object library. Go to any code window in your workbook, then Tools>>References, then click the checkbox for Access (8.0, 9.0, 10.0)


'BEGIN CODE
Dim strDBName As String

Set appAccess = CreateObject("Access.Application")
strDBName = "DATABASE PATH AND NAME (C:\My Documents\pcalc.mdb"

appAccess.OpenCurrentDatabase strDBName

appAccess.DoCmd.OpenQuery "Query Name"

'END CODE

 

[Daxton A.]

Ok this is where im @

Sub OpenMainDB()
Dim appAccess As Access.Application
Dim frmAccess As Access.Form



Set appAccess = Access.Application

appAccess.OpenCurrentDatabase ("s:\Main Database\First_
American Main Database 082702.mdb")
appAccess.Visible = True
appAccess.DoCmd.OpenForm ("frmSupplierMenu")



End Sub

frmSupplierMenu runs my query based on the user-supplied inputs from the form. Which consist of:
Supplier Name
Begginning Date
Ending Date

I would like to have the form in Excel and have it run the query from there and return the data to Excel.

Thanks for taking an interest scottfarcus. If you would rather, we can switch over to email and just post the solution after we're done.

Thanks again,
Daxton A.