Can't UPDATE OR INSERT using DAO code

[MC]

Hello,
I have an access 2000 application with linked tables to sql server
2000. I have used DAO queries before to get data( i.e. select staements) and
works fine.References are set for DAO 3.6 object library, and I am trying to
update records in a table from VBA code and it gives an "Operation is not
supported for this type of object", but the query by itself updates the table
fine.
I moved to ado code when this did not work but am curious to know the answer.

code for query: PARAMETERS [@projID] IEEEDouble, [@ParentProjID] IEEEDouble;
UPDATE SubProjectDetails SET parent_projId = [@ParentProjID]
WHERE [SubProjectDetails].[Projectid]=[@projID];
DAO code: Set mydef = db.QueryDefs("Q_InsertParentProjID") ' name of query
above
mydef![@Projid] = intprojid
'[Forms]![frmsubProj_Admin]![txtprojid]
mydef![@ParentProjid] = intparentprojid
For Each daoerr In DBEngine.Errors
MsgBox daoerr.Description
Next daoerr
mydef.Close

any idea if this is a bug? or am I doing something wrong?
Thanks,
MC

 

[MarkInSalemOR]

Try the ADO command object and just use the input query don't use any
parameters. If it doesn't work tweak the string until it prints the input
query that works in SQL Server. This code should do the same as putting the
input query into SQL Server manually. You will have declare and create the
ADODB objects and set your references to ADO, I think 2.5 and up. Use F1 help
or the MSDN website if you need syntax examples.

cmTest is the ADO command object
strTestSQL contains the input query
conConnect is the ADO connection


strTestSQL = "UPDATE SubProjectDetails SET parent_projId = [@ParentProjID]
WHERE [SubProjectDetails].[Projectid]=[@projID];"
cmTest.CommandText = strTestSQL
Set cmTest.ActiveConnection = conConnect
cmTest.Execute

~Mark

 

[MC]

Thanks for your response, I did move to Using ADO( which works fine) when DAO
did not work for updating, or inserting.. what I was wanting to know if this
is a known problem, or if I was doing something wrong in my DAO code???

Thanks,
MC

Try the ADO command object and just use the input query don't use any
parameters. If it doesn't work tweak the string until it prints the input
query that works in SQL Server. This code should do the same as putting the
input query into SQL Server manually. You will have declare and create the
ADODB objects and set your references to ADO, I think 2.5 and up. Use F1 help
or the MSDN website if you need syntax examples.

cmTest is the ADO command object
strTestSQL contains the input query
conConnect is the ADO connection


strTestSQL = "UPDATE SubProjectDetails SET parent_projId = [@ParentProjID]
WHERE [SubProjectDetails].[Projectid]=[@projID];"
cmTest.CommandText = strTestSQL
Set cmTest.ActiveConnection = conConnect
cmTest.Execute

~Mark

Hello,
I have an access 2000 application with linked tables to sql server
2000. I have used DAO queries before to get data( i.e. select staements) and
works fine.References are set for DAO 3.6 object library, and I am trying to
update records in a table from VBA code and it gives an "Operation is not
supported for this type of object", but the query by itself updates the table
fine.
I moved to ado code when this did not work but am curious to know the answer.

code for query: PARAMETERS [@projID] IEEEDouble, [@ParentProjID] IEEEDouble;
UPDATE SubProjectDetails SET parent_projId = [@ParentProjID]
WHERE [SubProjectDetails].[Projectid]=[@projID];
DAO code: Set mydef = db.QueryDefs("Q_InsertParentProjID") ' name of query
above
mydef![@Projid] = intprojid
'[Forms]![frmsubProj_Admin]![txtprojid]
mydef![@ParentProjid] = intparentprojid
For Each daoerr In DBEngine.Errors
MsgBox daoerr.Description
Next daoerr
mydef.Close

any idea if this is a bug? or am I doing something wrong?
Thanks,
MC