Access update query

[james]

Hi,

I am normally on the Excel group - but I have had a porblem driving me
crazy all day.

I have an Access 2010 database, which I have saved as .mdb.

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

 

[Bob Barrows]

Hi,

I am normally on the Excel group - but I have had a porblem driving me
crazy all day.

I have an Access 2010 database, which I have saved as .mdb.

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.

By running from Access, I assume you are running a saved query by
double-clicking it in the database window or by clicking Execute with it
open in the Query Builder window. What you need to realize here is that it's
Access prompting for those parameters, not the query engine. Access "helps
out" by processing the query statement before passing it to the query
engine. If there are parameters in the statement, it prompts for values,
inserting the values into the statement before passing it along. If you
tried to execute the same query or statement using DAO or ADO (as shown
below), you no longer have the advantage of Access helping you out with the
parameter resolution, even when running in an Access module. You have to
provide parameter values yourself.

Since you are running from Excel, you can have instructions in the
spreadsheet advising the user to enter parameter values into named cells,
then have your code read the values in those cells so it can pass them to
the query. Using ADO, I would do it like this (I forget the syntax for
referencing a cell, so consider the following to be air code - you need to
verify the syntax yourself):

dim parm1 as string, parm2 as integer
parm1= Range("namedcell1").value
parm1= Range("namedcell2").value
objConn.qryTest parm1,parm2

No explicit command object is involved here. ADO "exposes" stored procedures
(saved queries) as methods of the connection object to which you can pass
arguments. it makes it so much easier to code. If instead of an action
query, you had a select statement in that saved query, you could open a
recordset in much the same way:

set rs=new adodb.recordset
objConn.qryTest parm1,parm2, rs

Simply pass the recordset variable as the last argument

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'.

This seems unrelated to the parameter issue. We are not going to be able to
help with this without seeing the sql statement.

 

[John W. Vinson]

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'.

Not all queries are updatable. Please open qryTest in SQL view and post the
SQL text here; someone may be able to suggest changes that would make it so.

Or use Bob's suggestion to explicitly define the parameters.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com