PDA

View Full Version : Access Function from Excel



akn112
03-12-2008, 08:04 AM
Hey all,

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


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

Thanks!

BreakfastGuy
03-12-2008, 08:11 AM
You can probably find what you need here (http://www.excelguru.ca/taxonomy/term/9)

akn112
03-12-2008, 08:17 AM
You can probably find what you need here (http://www.excelguru.ca/taxonomy/term/9)

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

BreakfastGuy
03-12-2008, 08:26 AM
try this one! (http://www.excelguru.ca/node/23)

akn112
03-12-2008, 08:37 AM
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
03-12-2008, 09:03 AM
I am no Access guru (hate Access with a passion), but couldn't you just issue a DoCmd via automation?

BreakfastGuy
03-12-2008, 09:04 AM
Something like this?

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

BreakfastGuy
03-12-2008, 09:07 AM
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
03-12-2008, 09:13 AM
Using DoCmd as I suggested would be something like this


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

Bob Phillips
03-12-2008, 09:14 AM
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
03-12-2008, 09:16 AM
I stand corrected!
:ipray:

akn112
03-12-2008, 09:18 AM
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