Operation must use an updateable query. Error 3073 
Author Message
 Operation must use an updateable query. Error 3073

Access 97

Hi

I have craeted the following query using code :

Set qryInfo = db.CreateQueryDef
("AddMachineToUser", "PARAMETERS UserName Text,MachineID
Text;Insert into Users_and_machines
(NT_UserName,Machine_ID) values (UserName,MachineID)")

It is worth noting that the table Users_and_machines has
one more column, an Autonumbered ID column.

Now when i run this query within access, it works fine.

However, when running it via code I get the error
message "Operation must use an updateable query"

I looked on MSDN and that suggested to remove the
DISTINCTROW keyword, which I do not have.

Anyone any ideas?

Thanks



Sat, 02 Jul 2005 18:21:24 GMT  
 Operation must use an updateable query. Error 3073
Perhaps you are getting the wrong error message.

How are you running this from Code?  What does your code look like?

I suspect that you are not setting the values of the parameters and running into
trouble there, especially since UserName is a property of the workspace object.

If you just want to run the query and UserName and MachineId are string
variables declared in your module, then you could use.

   strSQL = "Insert into Users_and_machines " & _
      "(NT_UserName,Machine_ID) values (""" & UserName & """, """ & MachineID &
""")")
   DoCmd.RunSQL strSQL

If you want to run the query as written you need to get the values of the
parameters into the query.

  qryInfo.Parameters("UserName") = "some value here"
  qryInfo.Parameters("MachineId") = "Some other value"
  qryInfo.Execute

Your method seems a bit clumsy in that you would have to delete the query if you
wanted to build it again.

Quote:

> Access 97

> Hi

> I have craeted the following query using code :

> Set qryInfo = db.CreateQueryDef
> ("AddMachineToUser", "PARAMETERS UserName Text,MachineID
> Text;Insert into Users_and_machines
> (NT_UserName,Machine_ID) values (UserName,MachineID)")

> It is worth noting that the table Users_and_machines has
> one more column, an Autonumbered ID column.

> Now when i run this query within access, it works fine.

> However, when running it via code I get the error
> message "Operation must use an updateable query"

> I looked on MSDN and that suggested to remove the
> DISTINCTROW keyword, which I do not have.

> Anyone any ideas?

> Thanks



Sun, 03 Jul 2005 03:12:08 GMT  
 Operation must use an updateable query. Error 3073
Hi John, thanks for the reply.

I don't think it's a problem with the setting of the
parameter values. I have a function which runs a query,
given the query name, and the parameter names and values,
and whether it's an execution only query, viz. :

Private Function RunQuery(ByVal boolExecuteOnly As
Boolean, ByVal strQueryName As String, _
                    ByRef p_udtParameters4Qry() As
QueryArgs, Optional ByRef p_rsToReturn As Recordset) As
Boolean
    'all definitions in here
    Const FUNCTION_NAME As String = "RunQuery"
    Dim dbWkbks As DAO.Database
    Dim qryTemp As DAO.QueryDef
    Dim intParameter As Integer

On Error GoTo ErrorTrap

    RunQuery = False

    'go to DB and get recordsets
    Set dbWkbks = DBEngine.OpenDatabase(DATABASE_PATH,
False, True)

    Set qryTemp = dbWkbks.QueryDefs(strQueryName)

    If qryTemp.Parameters.Count > 0 Then
        For intParameter = 0 To UBound(p_udtParameters4Qry)
            qryTemp.Parameters(p_udtParameters4Qry
(intParameter).Name) = p_udtParameters4Qry
(intParameter).Value
        Next
    End If

    If boolExecuteOnly Then
        qryTemp.Execute
    Else
        Set p_rsToReturn = qryTemp.OpenRecordset
    End If

    RunQuery = True
    GoTo PrivateExitPoint

ErrorTrap:
    ReportError Err, FUNCTION_NAME, MODULE_NAME

PrivateExitPoint:
    'cleanup
    Set qryTemp = Nothing
    Set dbWkbks = Nothing
End Function

My QueryArgs is a udt defined as :

Private Type QueryArgs
    Name As String
    Value As String
End Type

Rog

Quote:
>-----Original Message-----
>Perhaps you are getting the wrong error message.

>How are you running this from Code?  What does your code
look like?

>I suspect that you are not setting the values of the

parameters and running into
Quote:
>trouble there, especially since UserName is a property of

the workspace object.
Quote:

>If you just want to run the query and UserName and

MachineId are string
Quote:
>variables declared in your module, then you could use.

>   strSQL = "Insert into Users_and_machines " & _
>      "(NT_UserName,Machine_ID) values (""" & UserName

& """, """ & MachineID &
Quote:
>""")")
>   DoCmd.RunSQL strSQL

>If you want to run the query as written you need to get
the values of the
>parameters into the query.

>  qryInfo.Parameters("UserName") = "some value here"
>  qryInfo.Parameters("MachineId") = "Some other value"
>  qryInfo.Execute

>Your method seems a bit clumsy in that you would have to

delete the query if you
Quote:
>wanted to build it again.


>> Access 97

>> Hi

>> I have craeted the following query using code :

>> Set qryInfo = db.CreateQueryDef
>> ("AddMachineToUser", "PARAMETERS UserName Text,MachineID
>> Text;Insert into Users_and_machines
>> (NT_UserName,Machine_ID) values (UserName,MachineID)")

>> It is worth noting that the table Users_and_machines has
>> one more column, an Autonumbered ID column.

>> Now when i run this query within access, it works fine.

>> However, when running it via code I get the error
>> message "Operation must use an updateable query"

>> I looked on MSDN and that suggested to remove the
>> DISTINCTROW keyword, which I do not have.

>> Anyone any ideas?

>> Thanks
>.



Sun, 03 Jul 2005 15:45:19 GMT  
 Operation must use an updateable query. Error 3073
John, have realised what the problem is - i am opening the
DB in read-only mode "Set dbWkbks = DBEngine.OpenDatabase
(DATABASE_PATH, False, True)" !!

Thanks for your help

Quote:
>-----Original Message-----
>Hi John, thanks for the reply.

>I don't think it's a problem with the setting of the
>parameter values. I have a function which runs a query,
>given the query name, and the parameter names and values,
>and whether it's an execution only query, viz. :

>Private Function RunQuery(ByVal boolExecuteOnly As
>Boolean, ByVal strQueryName As String, _
>                    ByRef p_udtParameters4Qry() As
>QueryArgs, Optional ByRef p_rsToReturn As Recordset) As
>Boolean
>    'all definitions in here
>    Const FUNCTION_NAME As String = "RunQuery"
>    Dim dbWkbks As DAO.Database
>    Dim qryTemp As DAO.QueryDef
>    Dim intParameter As Integer

>On Error GoTo ErrorTrap

>    RunQuery = False

>    'go to DB and get recordsets
>    Set dbWkbks = DBEngine.OpenDatabase(DATABASE_PATH,
>False, True)

>    Set qryTemp = dbWkbks.QueryDefs(strQueryName)

>    If qryTemp.Parameters.Count > 0 Then
>        For intParameter = 0 To UBound

(p_udtParameters4Qry)

- Show quoted text -

Quote:
>            qryTemp.Parameters(p_udtParameters4Qry
>(intParameter).Name) = p_udtParameters4Qry
>(intParameter).Value
>        Next
>    End If

>    If boolExecuteOnly Then
>        qryTemp.Execute
>    Else
>        Set p_rsToReturn = qryTemp.OpenRecordset
>    End If

>    RunQuery = True
>    GoTo PrivateExitPoint

>ErrorTrap:
>    ReportError Err, FUNCTION_NAME, MODULE_NAME

>PrivateExitPoint:
>    'cleanup
>    Set qryTemp = Nothing
>    Set dbWkbks = Nothing
>End Function

>My QueryArgs is a udt defined as :

>Private Type QueryArgs
>    Name As String
>    Value As String
>End Type

>Rog

>>-----Original Message-----
>>Perhaps you are getting the wrong error message.

>>How are you running this from Code?  What does your code
>look like?

>>I suspect that you are not setting the values of the
>parameters and running into
>>trouble there, especially since UserName is a property
of
>the workspace object.

>>If you just want to run the query and UserName and
>MachineId are string
>>variables declared in your module, then you could use.

>>   strSQL = "Insert into Users_and_machines " & _
>>      "(NT_UserName,Machine_ID) values (""" & UserName
>& """, """ & MachineID &
>>""")")
>>   DoCmd.RunSQL strSQL

>>If you want to run the query as written you need to get
>the values of the
>>parameters into the query.

>>  qryInfo.Parameters("UserName") = "some value here"
>>  qryInfo.Parameters("MachineId") = "Some other value"
>>  qryInfo.Execute

>>Your method seems a bit clumsy in that you would have to
>delete the query if you
>>wanted to build it again.


>>> Access 97

>>> Hi

>>> I have craeted the following query using code :

>>> Set qryInfo = db.CreateQueryDef
>>> ("AddMachineToUser", "PARAMETERS UserName
Text,MachineID
>>> Text;Insert into Users_and_machines
>>> (NT_UserName,Machine_ID) values (UserName,MachineID)")

>>> It is worth noting that the table Users_and_machines
has
>>> one more column, an Autonumbered ID column.

>>> Now when i run this query within access, it works fine.

>>> However, when running it via code I get the error
>>> message "Operation must use an updateable query"

>>> I looked on MSDN and that suggested to remove the
>>> DISTINCTROW keyword, which I do not have.

>>> Anyone any ideas?

>>> Thanks
>>.

>.



Sun, 03 Jul 2005 16:27:47 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Run time error 3073 -Operation must use an updatable query

2. Error 3073, Operation must use an updatable query

3. Error 3073 - Operation must use an updatable query

4. mdb/sql error: Operation must use an updateable query

5. Error 3073 : Updatable Query required

6. Operation must use an updateable query.

7. 0x80004005 Operation must use an updateable query

8. Operation must use an updateable query.

9. Operation must use an updateable query (URGENT)

10. Operation Must be an updateable query?

11. HELP - Operation must use an updateable query

12. Operation must use an updateable query.

 

 
Powered by phpBB® Forum Software