error 3027 odbc sql

  • Thread starter Thread starter Merco
  • Start date Start date
M

Merco

i have converted a front-end with linked table to an MDB file to a
front-end database (MSACC2000) with linked table to SQL 2000.

I have both put primary keys and timestamp in my tables but this
recordset is still read only.
StrSQL = "SELECT DBProd.*, SchedeArticoli.GIACENZA,
[dbprod]![qta]-[dbprod]![qtaprelevata] AS Diff" _
& " FROM ODL INNER JOIN (DBProd LEFT JOIN SchedeArticoli ON
(DBProd.IDMagazzino = SchedeArticoli.IDMAGAZZINO) AND (DBProd.Cod_F =
SchedeArticoli.IDCODICEARTICOLO)) ON ODL.RowId = DBProd.RifRowIdODL" _
& " WHERE (((DBProd.Cod_F) Like '" +
[Forms]![PrelievoDaODL]![Codice] + "') AND ((nulltostring([Commessa]))
Like '" + [Forms]![PrelievoDaODL]![Commessa] + "') AND
((ODL.Stato)='A') AND ((DBProd.ODLTipo) Like '" +
[Forms]![PrelievoDaODL]![Tipo] + "') AND ((DBProd.ODLNumero) Like '" +
[Forms]![PrelievoDaODL]![Numero] + "') AND
((DBProd.Qta)>[dbprod]![qtaprelevata]));"

Set db = CurrentDb
Set Rs = db.OpenRecordset(StrSQL, dbOpenDynaset, dbSeeChanges)

but the same SQL in the MSSQL enterprise manager let me update the
fields...
SELECT dbo.DBProd.*, dbo.SchedeArticoli.GIACENZA AS Expr1,
dbo.DBProd.Qta - dbo.DBProd.QtaPrelevata AS Diff
FROM dbo.ODL INNER JOIN
dbo.DBProd LEFT OUTER JOIN
dbo.SchedeArticoli ON dbo.DBProd.IDMagazzino =
dbo.SchedeArticoli.IDMAGAZZINO AND
dbo.DBProd.Cod_F =
dbo.SchedeArticoli.IDCODICEARTICOLO ON dbo.ODL.RowId =
dbo.DBProd.RifRowIdODL
WHERE (dbo.DBProd.Cod_F LIKE N'%') AND (dbo.ODL.Commessa LIKE
'GEN') AND (dbo.ODL.Stato = 'A') AND (dbo.DBProd.ODLTipo LIKE N'%')
AND
(dbo.DBProd.ODLNumero LIKE N'%') AND
(dbo.DBProd.Qta > dbo.DBProd.QtaPrelevata)

THE QUESTION IS:
IS THERE A WORKAROUND WITHOUT MODIFING THE SOURCE CODE ?

thanks
 
Merco said:
i have converted a front-end with linked table to an MDB file to a
front-end database (MSACC2000) with linked table to SQL 2000.

I have both put primary keys and timestamp in my tables but this
recordset is still read only.
StrSQL = "SELECT DBProd.*, SchedeArticoli.GIACENZA,
[dbprod]![qta]-[dbprod]![qtaprelevata] AS Diff" _
& " FROM ODL INNER JOIN (DBProd LEFT JOIN SchedeArticoli ON
(DBProd.IDMagazzino = SchedeArticoli.IDMAGAZZINO) AND (DBProd.Cod_F =
SchedeArticoli.IDCODICEARTICOLO)) ON ODL.RowId = DBProd.RifRowIdODL" _
& " WHERE (((DBProd.Cod_F) Like '" +
[Forms]![PrelievoDaODL]![Codice] + "') AND ((nulltostring([Commessa]))
Like '" + [Forms]![PrelievoDaODL]![Commessa] + "') AND
((ODL.Stato)='A') AND ((DBProd.ODLTipo) Like '" +
[Forms]![PrelievoDaODL]![Tipo] + "') AND ((DBProd.ODLNumero) Like '" +
[Forms]![PrelievoDaODL]![Numero] + "') AND
((DBProd.Qta)>[dbprod]![qtaprelevata]));"

Set db = CurrentDb
Set Rs = db.OpenRecordset(StrSQL, dbOpenDynaset, dbSeeChanges)

but the same SQL in the MSSQL enterprise manager let me update the
fields...
SELECT dbo.DBProd.*, dbo.SchedeArticoli.GIACENZA AS Expr1,
dbo.DBProd.Qta - dbo.DBProd.QtaPrelevata AS Diff
FROM dbo.ODL INNER JOIN
dbo.DBProd LEFT OUTER JOIN
dbo.SchedeArticoli ON dbo.DBProd.IDMagazzino =
dbo.SchedeArticoli.IDMAGAZZINO AND
dbo.DBProd.Cod_F =
dbo.SchedeArticoli.IDCODICEARTICOLO ON dbo.ODL.RowId =
dbo.DBProd.RifRowIdODL
WHERE (dbo.DBProd.Cod_F LIKE N'%') AND (dbo.ODL.Commessa LIKE
'GEN') AND (dbo.ODL.Stato = 'A') AND (dbo.DBProd.ODLTipo LIKE N'%')
AND
(dbo.DBProd.ODLNumero LIKE N'%') AND
(dbo.DBProd.Qta > dbo.DBProd.QtaPrelevata)

THE QUESTION IS:
IS THERE A WORKAROUND WITHOUT MODIFING THE SOURCE CODE ?

thanks
Click to expand...

Hey, there's no need to shout! Anyway, the point is, it is NOT the same
query in each case, is it? One of them is an Access/Jet query, the other is
a SQL Server query. They are quite different beasts. If you look up
"updatable" in Access help, you will find a longish (but not comprehensive)
explanation of when Access queries are or are not updatable. Without
examining your query in detail, or trying it, my first impression is that
I'm not at all surprised: I wouldn't expect an Access query with three
tables and moderately complex joins to be updatable.

So, I guess the answer is that, no, there isn't a workaround. Something of
a design rethink will be required.
 
thank for the answer,
but the original query (in Access) is *still* updatable
so i suppose that ODBC driver would be a so "good translator" to give
SQL SERVER the right query (the second one is updateble in the
Enterprise manager)
 
Back
Top