dbSeeChanges & Identity Column error

[SlowGirl]

I'm new to VBA and need to delete a record using VBA in an ODBC linked SQL
table. I'm getting the "use dbSeeChanges" error due to an Identity column.
I've done lots of research and I get the concept I should be using a
recordset but most examples are too complicated for my novice brain to
decipher at this point in time.

Would someone be kind and help me (I really need specifics)?
here is my code, as you can see it's simple:

Private Sub cmdCANCEL_Click()

Dim strCancelDistSQL As String

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE

(dbo_tblSI_CorrectionDist.C_LOG_ID)=[Forms]![frmCorrectionRequest]![C_LOG_ID];"

CurrentDb.Execute strCancelDistSQL
End Sub

 

[Roger Carlson]

There's no reason not to use a SQL statement. I can't test this, but have
you used dbSeeChanges?

CurrentDb.Execute strCancelDistSQL, dbSeeChanges

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[SlowGirl]

when I try that, I get a message box with the error:

"Too Few Parameters. Expected 1."

Any Ideas?

There's no reason not to use a SQL statement. I can't test this, but have
you used dbSeeChanges?

CurrentDb.Execute strCancelDistSQL, dbSeeChanges

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm new to VBA and need to delete a record using VBA in an ODBC linked SQL
table. I'm getting the "use dbSeeChanges" error due to an Identity column.
I've done lots of research and I get the concept I should be using a
recordset but most examples are too complicated for my novice brain to
decipher at this point in time.

Would someone be kind and help me (I really need specifics)?
here is my code, as you can see it's simple:

Private Sub cmdCANCEL_Click()

Dim strCancelDistSQL As String

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE

(dbo_tblSI_CorrectionDist.C_LOG_ID)=[Forms]![frmCorrectionRequest]![C_LOG_ID
];"

CurrentDb.Execute strCancelDistSQL
End Sub

 

[Roger Carlson]

Oh. Sure. A query run through VB doesn't know about [forms!...]. You need
to create the query like this instead:

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE
(dbo_tblSI_CorrectionDist.C_LOG_ID)=" &
[Forms]![frmCorrectionRequest]![C_LOG_ID]

This assumes the C_LOG_ID is a numeric rather than text.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

when I try that, I get a message box with the error:

"Too Few Parameters. Expected 1."

Any Ideas?

There's no reason not to use a SQL statement. I can't test this, but have
you used dbSeeChanges?

CurrentDb.Execute strCancelDistSQL, dbSeeChanges

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm new to VBA and need to delete a record using VBA in an ODBC linked SQL
table. I'm getting the "use dbSeeChanges" error due to an Identity column.
I've done lots of research and I get the concept I should be using a
recordset but most examples are too complicated for my novice brain to
decipher at this point in time.

Would someone be kind and help me (I really need specifics)?
here is my code, as you can see it's simple:

Private Sub cmdCANCEL_Click()

Dim strCancelDistSQL As String

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE

(dbo_tblSI_CorrectionDist.C_LOG_ID)=[Forms]![frmCorrectionRequest]![C_LOG_ID
];"

CurrentDb.Execute strCancelDistSQL
End Sub

 

[SlowGirl]

of course! the dbSeeChanges threw me for a loop. Thank you so much. I've read
posts for years but this is the first time I posted - I really appreciate you
MVP'ers responding to questions that should be obvious.

Oh. Sure. A query run through VB doesn't know about [forms!...]. You need
to create the query like this instead:

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE
(dbo_tblSI_CorrectionDist.C_LOG_ID)=" &
[Forms]![frmCorrectionRequest]![C_LOG_ID]

This assumes the C_LOG_ID is a numeric rather than text.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

when I try that, I get a message box with the error:

"Too Few Parameters. Expected 1."

Any Ideas?

There's no reason not to use a SQL statement. I can't test this, but have
you used dbSeeChanges?

CurrentDb.Execute strCancelDistSQL, dbSeeChanges

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I'm new to VBA and need to delete a record using VBA in an ODBC linked SQL
table. I'm getting the "use dbSeeChanges" error due to an Identity
column.
I've done lots of research and I get the concept I should be using a
recordset but most examples are too complicated for my novice brain to
decipher at this point in time.

Would someone be kind and help me (I really need specifics)?
here is my code, as you can see it's simple:

Private Sub cmdCANCEL_Click()

Dim strCancelDistSQL As String

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE


(dbo_tblSI_CorrectionDist.C_LOG_ID)=[Forms]![frmCorrectionRequest]![C_LOG_ID
];"

CurrentDb.Execute strCancelDistSQL
End Sub

 

[Roger Carlson]

From all the MVPs: You're welcome.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

of course! the dbSeeChanges threw me for a loop. Thank you so much. I've read
posts for years but this is the first time I posted - I really appreciate you
MVP'ers responding to questions that should be obvious.

Oh. Sure. A query run through VB doesn't know about [forms!...]. You need
to create the query like this instead:

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE
(dbo_tblSI_CorrectionDist.C_LOG_ID)=" &
[Forms]![frmCorrectionRequest]![C_LOG_ID]

This assumes the C_LOG_ID is a numeric rather than text.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

when I try that, I get a message box with the error:

"Too Few Parameters. Expected 1."

Any Ideas?

:

There's no reason not to use a SQL statement. I can't test this,

but
have

you used dbSeeChanges?

CurrentDb.Execute strCancelDistSQL, dbSeeChanges

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


I'm new to VBA and need to delete a record using VBA in an ODBC

linked
SQL

table. I'm getting the "use dbSeeChanges" error due to an Identity
column.
I've done lots of research and I get the concept I should be using a
recordset but most examples are too complicated for my novice

brain
to

decipher at this point in time.

Would someone be kind and help me (I really need specifics)?
here is my code, as you can see it's simple:

Private Sub cmdCANCEL_Click()

Dim strCancelDistSQL As String

strCancelDistSQL = "DELETE dbo_tblSI_CorrectionDist.* FROM
dbo_tblSI_CorrectionDist WHERE

(dbo_tblSI_CorrectionDist.C_LOG_ID)=[Forms]![frmCorrectionRequest]![C_LOG_ID

];"

CurrentDb.Execute strCancelDistSQL
End Sub

 

[Holly]

I am trying to Upsize an Access database to sql server (for the first time)
and am in the process of changing the code. I had the error, and this
solution worked fine for the first delete, but if I leave the form and then
open it again and try to delete another record it runs for at least a minute
and then gives me an error message - Run Time error 3156 ODBC Delete on a
linked table TableName failed. What am I doing wrong?