Delete access record

[EdmCath]

I have a routine that I copied off of the Autodesk NG from a few years back. I have tweaked it to work almost exactly as I need. One problem I have is if I delete an object from the acad drawing, it is still referenced in the access database. What I want to do is delete all the records in the table that match the current drawing name. I have looked for help elsewhere on this, but I dont quite understand the concept of records yet. I know this routine is written to work in autocad, but I just need help with the access record, everything autocad I have figured out. Could any one help fill in the blank to finish my code?

Public Sub PushToAccess()

Dim SchedApp As New AecScheduleApplication
Dim cPropSets As AecSchedulePropertySets
Dim PropSet As AecSchedulePropertySet
Dim cProps As AecScheduleProperties
Dim Prop As AecScheduleProperty
Dim space As AecSpace
Dim obj As AcadEntity

Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection
Dim fso As New FileSystemObject

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=I:\Facilities\Facilities Database\Facilities Utilization.mdb"
Set cat.ActiveConnection = cnn
Dim spaceTbl As New ADOX.Table
Dim sTable As String
Dim bFoundTable As Boolean

sTable = Mid(ThisDrawing.GetVariable("dwgname"), 1, 3)

bFoundTable = False
For Each spaceTbl In cat.Tables
If UCase(spaceTbl.Name) = UCase(sTable) Then
bFoundTable = True
Exit For
End If
Next

If bFoundTable Then
'''Delete all records in the table that matches sTable whoes field, DrawingName, matches dwgname
'I currently have the next line in here, but that deletes everything, including the table.
cat.Tables.Delete sTable
End If
With spaceTbl
.Name = sTable
.Columns.Append "Handle"
.Columns.Append "RoomNumber"
.Columns.Append "RoomName"
.Columns.Append "Area"
.Columns.Append "Style"

.Columns("RoomNumber").Attributes = adColNullable
.Columns("RoomName").Attributes = adColNullable
.Columns("Area").Attributes = adColNullable
.Columns("Style").Attributes = adColNullable

Dim index As New ADOX.index
index.Name = "Handle"
index.Columns.Append "Handle"
index.Unique = True
index.PrimaryKey = True
spaceTbl.Indexes.Append index

cat.Tables.Append spaceTbl

Set cnn = cat.ActiveConnection

End With

Dim spaceRs As New ADODB.Recordset
spaceRs.Open sTable, cnn, adOpenKeyset, adLockOptimistic

For Each obj In ThisDrawing.ModelSpace
If TypeOf obj Is AecSpace Then
Set space = obj
Set cPropSets = SchedApp.PropertySets(space)
Set PropSet = cPropSets.Item("SpaceObjects")
If Not PropSet Is Nothing Then
Set cProps = PropSet.Properties
spaceRs.Find "Handle = '" & space.Handle & "'"
If spaceRs.EOF Then
spaceRs.AddNew
spaceRs!Handle = space.Handle
End If
spaceRs!Area = cProps.Item("BaseArea").value
spaceRs!Style = cProps.Item("Style").value
spaceRs.Update
End If
Set PropSet = cPropSets.Item("RoomObjects")
If Not PropSet Is Nothing Then
Set cProps = PropSet.Properties
spaceRs.Find "Handle = '" & space.Handle & "'"
If spaceRs.EOF Then
spaceRs.AddNew
spaceRs!Handle = space.Handle
End If
spaceRs!RoomNumber = cProps.Item("Number").value
spaceRs!RoomName = cProps.Item("Name").value
spaceRs.Update
End If

End If
Next
Set cat = Nothing
End Sub

 

[KenReay]

Hi

Your routine is deleting the entire table by removing it from the tables collection of the catalogue as you rightly say the command

cat.Tables.Delete sTable

does this

This could also have been achieved by running an SQL command:

"DROP TABLE tablename;"

If you want to retain the definition of the table, but remove the data from it, you use an SQL delete query so

"DELETE * FROM tablename;"

Since I do not know what the routine you posted is supposed to be doing, I cannot advise where you would execute these statements

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

oops, forgot to say

if you wish to delete a single record (or records), say relating to an individual drawing number, rather than all records, you can refine the SQL so

"DELETE * FROM tablename WHERE drawingno = '123-456';"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[EdmCath]

I guess my question is, how do I use the SQL statement to do this? I know what the statement should be, I dont know how to use it. How do I execute the statement?

 

[KenReay]

Hi

Unfortunately there is more than one way to do this

You can use DoCmd.RunSQL "DELETE...etc"

alternatively you can execute it using your ADO objects, just making the transition from DAO to ADO, so I am not immeduiately familiar with how to do this in ADO I am afraid



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[EdmCath]

I figured it out, I was doing it right to begin, just missing some double quotes in my sql. I didn't figure it out until I tried the docmd then, Access told me my sql was wrong. thanks for the help