Cannot open an access file by triggering excel macro
Sub AccessTest1(oName As String)
Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("C:\Documents and Settings\All Users\Desktop\TestDB.accdb")
A.DoCmd.RunMacro "sayhi", oName
End SubHi,
I have the above macro
when called, it is saying 'Microsoft Office Access cannot open the db because it is missing, or opened exclusively by another user, or it is not an ADP file'
Where as I checked and it is an accdb file and is closed (this gave me the same error even when I copied the db and changed the name in both the file and macro)
Any help is appreciated!
If I try to Open the same db the code is in, I get a similar error ...
mx
mx
ASKER
If I do this,
The error dont come in but on the next line
Ac.DoCmd.RunMacro "sayhi", oName
it says 'An expressing you entered is the wrong data type for one of the arguments'
This is the 'sayhi' sub inside the TestDB.accdb
Ac.OpenCurrentDatabase ("C:\TestDB.accdb")
The error dont come in but on the next line
Ac.DoCmd.RunMacro "sayhi", oName
it says 'An expressing you entered is the wrong data type for one of the arguments'
This is the 'sayhi' sub inside the TestDB.accdb
Option Compare Database
Sub sayhi(PValue As String)
MsgBox "Hi " & PValue
End SubASKER
Ow and by the way the code is being run from Excel VBA
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is ""C:\Documents and Settings\All Users\Desktop\TestDB.accdb
a Trusted Location ?
mx
a Trusted Location ?
mx
ASKER
I don't get what you mean by 'Trusted Location'? How do I make a location 'trusted'?
Lets say (and it looks like it works) we put it in C:\
In this case how would you verse the runmacro according to the standards you posted?
expression.RunMacro(MacroN
A.DoCmd.RunMacro "sayhi"
^ but how would I pass the variable? as discussed here
https://www.experts-exchange.com/questions/27437830/Calling-Access-Macro-VBA-from-Excel-and-passing-values.html
Lets say (and it looks like it works) we put it in C:\
In this case how would you verse the runmacro according to the standards you posted?
expression.RunMacro(MacroN
A.DoCmd.RunMacro "sayhi"
^ but how would I pass the variable? as discussed here
https://www.experts-exchange.com/questions/27437830/Calling-Access-Macro-VBA-from-Excel-and-passing-values.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well that message is not showing up anymore!
What I really don't understand is, how this
expression.RunMacro(MacroN
is connected to the solution proposed to this thread
https://www.experts-exchange.com/questions/27437830/Calling-Access-Macro-VBA-from-Excel-and-passing-values.html
I meant to pass a variable but how is it possible with this?
expression.RunMacro(MacroN
What I really don't understand is, how this
expression.RunMacro(MacroN
is connected to the solution proposed to this thread
https://www.experts-exchange.com/questions/27437830/Calling-Access-Macro-VBA-from-Excel-and-passing-values.html
I meant to pass a variable but how is it possible with this?
expression.RunMacro(MacroN
ASKER
Changing the line to
Ac.Run "sayhi", sStr
worked
Ac.Run "sayhi", sStr
worked
Otherwise, it works for me opening a different db ...
mx