Access Function from Excel

[akn112]

Hey all,

was just wondering how you can fire off an access function from excel. My code currently (does not run) looks like this:

[VBA]
Dim db As Access.Application
Set db = GetObject("c:\CSV.mdb")
db.Run "InsertDates", Nz(cboFromDate, "2005-01"), Nz(cboToDate, CStr(Format(Date, "yyyy-mm")))
Set db = Nothing[/VBA]

Thanks!

[BreakfastGuy]

You can probably find what you need here

[akn112]

You can probably find what you need here

i'm not quite sure which article your referring to.

[BreakfastGuy]

try this one!

[akn112]

was looking through the article and could only find examples of connecting to the database and passing an sql statement to retrieve a specific recordset from it. i guess my question is more specifically,

how do you run a public function (within a module) in an Access database from excel?

sorry about the confusion

[Bob Phillips]

I am no Access guru (hate Access with a passion), but couldn't you just issue a DoCmd via automation?

[BreakfastGuy]

Something like this?
[VBA]
Public Sub Access_Procedure()

?this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = CurrentDb
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing

End Sub
[/VBA]

[BreakfastGuy]

Don't forget in Excel you need to set the reference for the Microsoft Access Object Library, in Excel, go to VBA editor (press ALT + F11), then select Tools from the top menu and then References.

[Bob Phillips]

Using DoCmd as I suggested would be something like this

[vba]
Sub CallAccessMacro()
Dim oAccess As Object

Set oAccess = CreateObject("Access.Application")
oAccess.Open "C:\bob.mdb"

oAccess.DoCmd.RunMacro "MyMacro"

'rest of the code

oAccess.Quit
Set oAccess = Nothing

End Sub
[/vba]

[Bob Phillips]

Don't forget in Excel you need to set the reference for the Microsoft Access Object Library, in Excel, go to VBA editor (press ALT + F11), then select Tools from the top menu and then References.

No you don't, you are using late binding.

[BreakfastGuy]

I stand corrected!

[akn112]

BreakfastGuy: the .Run function doesnt seem to kick off my function. It's what i was trying to use before but couldnt get it to work

xld: I had thought about using docmd.runmacro but my function requires inputs which need to be passed from my excel sheet. I'm not sure if there's a way to specify dynamic parameters for a function within a macro in access.

edit: I had the reference in cause i was using early binding. The drop down list of possible functions is like my "cheat sheet" =P