Run-Time error '429' ActiveX component can't create object

[artefact]

Hello,
I'm struggling with a VBA error at the following line: marked in bold:
Sub test()
Dim strDBName As String
Dim strMessage As String
Dim appAccess As Access.Application
Dim vartest As String
Dim hd As String
Dim hf As String
Dim databasename As String
databasename = "test.accdb"
Dim currentweek As String
strDBName = ThisWorkbook.path & "\" & databasename
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set appAccess = New Access.Application
With appAccess
.OpenCurrentDatabase strDBName
.Visible = False ' Useful for debugging when true
.Eval ("semaineencours()")
End With
currentweek = appAccess.Eval("semaineencours()")
MsgBox currentweek
With appAccess
.CloseCurrentDatabase
.Quit
End With
End Sub

Here is the error: Run-Time error '429' ActiveX component can't create object

Everything is working well with access 2010 installed. When I run this code in Excel 2010, I've got no issue.
I'm trying to run this same piece of code on a different machine on which I've installed access 2010 runtime.
I'm still facing this error? It is really possible to run excel 2010 vba code with the access 2010 runtime?
I've installe run-tim 32 bit on Win7
Thanks for your help.
Arte

 

[RoryA]

I don't believe you can use automation with the Access runtime.

 

[artefact]

I don't believe you can use automation with the Access runtime.

Does this mean I should install Access 2010 in every machine?
If so, this is not handy and access 2010 would be useless for automation.

Thanks.

Arte

 

[RoryA]

In my experience it's pretty rare that you actually need to automate the Access application. For the most part you want to query the database in some way, which doesn't require Access at all.

 

[artefact]

Hello,
For those who need to automate Access application (with its RUN TIME env as well), with access. Here is the way set the object between those application:

Dim bddobjet As Object
databasenamepath = ThisWorkbook.path & "\prev.accdb"
'********************Object to handle database)
Set bddobjet = accessobjet(databasenamepath)


Function accessobjet(databasenamepath As String) As Object
strProgramName = "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"
strArgument = databasenamepath
Call Shell("""" & strProgramName & """ """ & strArgument & """", vbMinimizedNoFocus)
Set accessobjet = GetObject(, "Access.Application")
End Function

With bddobjjet set, you can call VBA function from the accdb file for instance.

Arte

 

[artefact]

In my experience it's pretty rare that you actually need to automate the Access application. For the most part you want to query the database in some way, which doesn't require Access at all.

Hello,
For those who need to automate Access application (with its RUN TIME env as well), with access. Here is the way set the object between those application:

Dim bddobjet As Object
databasenamepath = ThisWorkbook.path & "\prev.accdb"
'********************Object to handle database)
Set bddobjet = accessobjet(databasenamepath)


Function accessobjet(databasenamepath As String) As Object
strProgramName = "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"
strArgument = databasenamepath
Call Shell("""" & strProgramName & """ """ & strArgument & """", vbMinimizedNoFocus)
Set accessobjet = GetObject(, "Access.Application")
End Function

With bddobjjet set, you can call <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> function from the accdb file for instance.

Arte

 

[Kyle123]

You still need access installed for that though?

 

[artefact]

You still need access installed for that though?

Hello Kyle123, no you don't need to set up the full access application, I only needed the access run-time environment, which is free, to distribute my apps.
Arte