delete multiple records in db table 1

[yahve]

Hi,

I have an access2k db which has 3 tables containing info about products. The first table has a autonumber field "id_product" which I use in the 2 other tables (one to many relation). I have no problem retrieving the info using datashaping.

But, when I try to delete a product, it gets deleted from the first table (where there is only one occurence of the id_product number) and from the second (where there are more than one occurence of id_product) but not from the third table.

Here is the code I'm using:

<%
id_prod = Request.form(&quot;id_prod&quot;)
sqla = &quot;DELETE FROM prod_fr WHERE id_prod = &quot; & id_prod & &quot;;&quot;
sqlb = &quot;DELETE FROM tech WHERE id_prod = &quot; & id_prod & &quot;;&quot;
sqlc = &quot;DELETE FROM image WHERE id_prod = &quot; & id_prod & &quot;;&quot;

strConn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\database.mdb&quot;
Set cnn = Server.CreateObject(&quot;ADODB.Connection&quot;)
cnn.Open strConn
Set cmn = Server.CreateObject(&quot;ADODB.Command&quot;)
Set cmn.ActiveConnection = cnn
cmn.CommandText = sqla
cmn.Execute ,,adCmdText + adExecuteNoRecords
cmn.CommandText = sqlb
cmn.Execute ,,adCmdText + adExecuteNoRecords
cmn.CommandText = sqlc
cmn.Execute ,,adCmdText + adExecuteNoRecords
cnn.close
%>

It executes the first and second delete then I get a syntaxe error in the from clause when it tries to execute the third sql delete command.

Any suggestions?

Thanks.

 

[blurred]

what's the error message ?

 

[jonscott8]

This is a hip-shot, but try adding brackets around the word image. Image may be a reserved word in Access. Example:

sqlc = "DELETE FROM [image] WHERE id_prod = " & id_prod & ";" Jon Hawkins

 

[yahve]

Hi,

Thanks Jon, you were right on the money. I added the brackets and all was fine. A friend of mine suggested to always add the brackets around tables' names when working with Access, what do you think about that?

Thanks again.

P.S.: Blurred, I would have posted the error message but since it is in french, I'm not sure it would have helped you understand, so I translated.

 

[vasah20]

yahve -
FYI - words like image and password are all reserved words in Access (there's a whole list more, I just don't know them). So you need to add brackets around them, to signify that they are columns. You also need to have brackets around any columns with a space in it's name eg: [order id] so that access picks up it's a column.

should you add brackets around every column? if you'd like, but personally I don't unless I need to. I feel it makes things harder to read.

I have heard that if you fully qualify all the columns that you get a slight performance increase (eg: tblName.columnName or tblName.[columnName]), but I could be wrong.

just my opinion
leo leo