Help with vBA and Sql (1 Viewer)

[thick_guy_9]

Hello all
As a one time VB programmer, I have been out of touch with VB and need to complete a project using VBa and access.

I need to delete a table "MDP". The problem is that after i do an EXECUTE on the currentDB, the rowsaffected property is = 0? But I have 6 rows in the table!! Please help !!

dim myrec as DAO.recordset
dim wrkcurrent as DAO.workspace

Set myRec = CurrentDb.OpenRecordset("MDP", dbOpenTable)
If Not (myRec.EOF And myRec.BOF) Then
myRec.MoveLast
iCnt = myRec.RecordCount
myRec.Close
Set myRec = Nothing
' truncate table if any records are found
If (iCnt <> 0) Then
Set wrkCurrent = DBEngine.Workspaces(0)
wrkCurrent.BeginTrans
sSQL = "DELETE * FROM MasterData_Plan;"
CurrentDb.Execute sSQL
End If
If iCnt <> CurrentDb.RecordsAffected Then
wrkCurrent.Rollback
Set wrkCurrent = Nothing
sErrorMsg = ""
sErrorMsg = "Not all rows were deleted from table MDP"
sErrorMsg = sErrorMsg & vbCrLf & "Loading Raw data into this table will terminate!"
sErrorTitle = Me.Form.Name & ": Error in cmdLoad_OnClick"
GoTo Error_Handler
End If
wrkCurrent.CommitTrans
Set wrkCurrent = Nothing
End If

 

[bmcgree1]

Why don't you run a DDL query? A Data Definition Language query edits tables, fields, rows, relationships from writing SQL.

If you want to delete your MDP table using VBA then try this:

docmd.setwarnings false 'so access wont prompt you when executing
docmd.runsql ("DROP TABLE MDP;") 'if running this in an access query the only syntax you need is between the " "
docmd.setwarnings true

as long as your table isnt in relationship with other tables that will delete your MDP table. if it does have relation then you'll need a little bit more sql

 

[thick_guy_9]

Thanks for the response. I tried the DoCMD and it worked.

I do not know why my code did not work though.

dropping and recreating the table is not an option because:
a. the table has many columns
b. I am still struggling to get comfortable with DAO and ADO.

Thanks again, but I would appreciate it if someone would point out the mistake in my earlier code.

 

[thick_guy_9]

I think I found out what the problem is:

while executing SQL statements using CurrentDB.execute "<your sql stmt>"
DO NOT terminate it with a ';'.
i.e use "DELETE * FROM TEST"
and not "DELETE * FROM TEST;"

Cheers...!

 

[DCrake]

Another question is why after determining the number of records in the table using .RecordCount did you proceed with the RecordsAffected? If the table was EOF and BOF then obviously there would be no records affected. The simple record count would tell you the number of records affected, wouldn't it?

David

 

[LPurvis]

Hi, the reason for your failing code isn't as tentative as you fear - it's quite quantifiable.
Regardless of the transaction you're within - you have to use the same database object to retrieve the property as that which executed the statement.

Your relevant lines of code are:

...
    CurrentDb.Execute sSQL
End If
If iCnt <> CurrentDb.RecordsAffected Then
...

In each case CurrentDb is returning a new database object (they just happen to be pointing at the same physical database instance).
If you persist the object and use it in both calls then you'll get the results you'd expect.

e.g.

...
With CurrentDb
    ....
        .Execute sSQL
    End If
    If iCnt <> .RecordsAffected Then
    ...
End With

Or, more traditionally, using a database object variable...

Dim db As DAO.Database
Set db = CurrentDb
 
...
    db.Execute sSQL
End If
If iCnt <> db.RecordsAffected Then
...

I don't understand the reason for the check either - are you worried that the table isn't empty after the delete?
Locks placed on certain rows by some means?
Apart from anything else - if that was a concern then what about checking for any rows after the delete is executed. (You're at least then not pulling over however many rows to check first).

Cheers.