Recordset question

[ready4data]

Can anyone see any reason why the following statements dont delete the records. strDBPath is defined earlier and works.

        'Delete existing project name records.
        cnnDBServer = New ADODB.Connection
        cnnDBServer.Provider = "Microsoft.Jet.OLEDB.4.0"
        cnnDBServer.Open(strDBPath & "\AbcServer.mdb")

        strSql = "DELETE tblClientFileDetails.*, tblClientFileDetails.FileName FROM tblClientFileDetails WHERE (((tblClientFileDetails.FileName) Like 'AbcServer*'))"
        cnnDBServer.Execute(strSql)
        strSql = "DELETE tblClientFileDetails.*, tblClientFileDetails.FileName FROM tblClientFileDetails WHERE (((tblClientFileDetails.FileName) Like 'AbcClient*'))"
        cnnDBServer.Execute(strSql)
        ' Close Connection object and destroy object variable.
        cnnDBServer.Close()
        cnnDBServer = Nothing

This earlier piece of code runs ok
strSql = "Delete tblClientFileDetails.* From tblClientFileDetails"
cnnDBServer.Execute(strSql)
I'm coding this in Visual Studio 2010

Thanks,
Scott

 

[boblarson]

The correct way is

[FONT=Times New Roman][SIZE=3]strSql = "DELETE tblClientFileDetails.* FROM tblClientFileDetails WHERE (((tblClientFileDetails.FileName) Like 'AbcServer*'))"[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]       cnnDBServer.Execute(strSql)[/FONT][/SIZE]

You can't specify a particular field in the delete part. It is for entire records. If you want delete data from a certain field you need to use an UPDATE query.

 

[ready4data]

Thanks Bob,
I made the corrections to the sql but it still doesn't delete the records. I can take the sql statement and paste it into a query in MS Access and it does work, just not through my code.

Scott

 

[boblarson]

Is this a different database than the one that the code is in that you are deleting the records from?

 

[ready4data]

Bob,
I'm coding it in Visual Studio 2010 and accessing the .mdb file through code.
Its for an application external from the database. The rest of the code runs correctly and I can access and write to the tables. The delete piece is what is giving me trouble.

Scott