The Access DB definitely does not need to be open. In fact, it should
be possible to run your update query even if Access is not installed
on the target system, as the relevant Jet engine is distributed with
all versions of Office including Standard.
What happens if you try to run the SQL of the query directly in
the .CommandText property, rather than the name of the query? If you
choose to try this you will need to change .CommandType to adCmdText.
Incidentally, you can just run the query name as a method on the
Connection object. This approach is not well-documented, but should
also work:
objConn.qryTest
In any case, it would be helpful to see the SQL for your qryTest, as
well as the SQL for any other queries on which it depends. COuld you
post these, if my suggestions above don't work?
On Sep 16, 12:53 pm, "Kishan Reddy, K"
<kishanreddy.kethire...@gmail.com> wrote:
> Hello James,
>
> I think for using any forms/running any quires, the access has to be
> opened.
>
> Also I think Connection method only used to share the data from the
> table/queries.
>
> Try the following code.
>
> Sub MyOpenDb()
> Dim appAccess As Object
>
> Set appAccess = CreateObject("Access.Application")
>
> appAccess.OpenCurrentDatabase ("D:\XXX\ABCD.MDB")
>
> appAccess.Visible = True
>
> appAccess.docmd.openquery "XXXXXXXX"
>
> appAccess.Quit
>
> End Sub
>
> Revert back, if any comments, issues
>
> Regards,
> Kishan Reddy, K
>
> On Sep 14, 3:31 pm, "ja...@macstop.co.uk" <ja...@macstop.co.uk> wrote:
>
>
>
>
>
>
>
> > Hi,
>
> > I have posted this on the access group too, it can't really be
> > classified as one or the other, but it's driving me mad, can anyone
> > help.
>
> > I have an Access 2010 database, which I have saved as .mdb. (ie not
> > accdb)
>
> > It has an update query in, which takes 2 string parameters. When I run
> > it from Access it works fine - asks me for the 2 parameters then
> > updates the table accordingly.
>
> > However, I am trying to run it from Excel VBA. At first I thought the
> > issue was the parameters, so I created another update query which
> > takes 0 parameters. The continual error I get is 'Operation must use
> > an updateable query'.
>
> > It goes without saying that I can connect to the database, and I can
> > run normal Select statements - anyone got any ideas? Code below:
> > (obviously the execute line is the one that gives the error).
>
> > Function RunUpdateQuery(objConn As ADODB.Connection)
> > Dim objCommand As ADODB.Command
>
> > Set objCommand = New ADODB.Command
>
> > With objCommand
> > .ActiveConnection = objConn
> > .CommandType = adCmdStoredProc
> > .CommandText = "qryTest"
> > .Execute
> > End With
>
> > End Function
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
