how to call Access function&module in Excel VBA??

[Guest]

I have a question for how to call Access procedure/Function in Excel VBA?

Now I have Access, And I define a simple subform for test

data_pool.mdb
Sub test1()
Dim conDatabase As ADODB.Connection
Set conDatabase = Application.CurrentProject.Connection

Dim SQL As String
SQL = "update country set scale = scale + 1 where country = ""dd"" "
conDatabase.Execute (SQL)
'conDatabase.CommitTrans
'Call table_conversion

End Sub

right now I wanna call the test1() in my Excel file
(add Microsoft Access 10 library in reference)

myExl.xls

Sub Button36_Click()
'Dim cmd As ADODB.Command
'Dim conn As ADODB.Connection

Dim accessObj As Object
Dim ac As Access.Application
Dim path As String

path = ActiveWorkbook.path
path = path & "\data_pool.mdb"
Set accessObj = GetObject(path) ', "Access.Application")
Set ac = accessObj.Application

ac.Run test1
'accessObj.Application.Visible = True

'accessObj.Application.Run Module1.test1

End Sub

I can open this Access in the above code, but I can not run the test1() function here.

anyone can help me out, thanks a lot!!

 

[merjet]

Your code works for me adding quotes around the macro
name, i.e.: ac.Run "test1"

I'd recommend adding the following afterwards:
ac.CloseCurrentDatabase
ac.Quit

HTH,
Merjet