URGENT: error 3075 while trying to delete records

B

Broadway

i am trying to use a list from a combo box, which has names that coincide
with table names to delete records whose one field does not match the name of
the table (the table name is the unit the person is in, and there is a field
for unit in the table).
 
O

Ofer

Can you post the delete statment you are using?

It can happen when you try th filter the records you want to delete, no by
the type of the field
For example

For number
"Delete * From Tablename Where FieldName = " & Parameter
For string
"Delete * From Tablename Where FieldName = '" & Parameter & "'"
For date
"Delete * From Tablename Where FieldName = #" & Parameter & "#"
 
B

Broadway

I knew you were going to ask for the code, i just didn't want anyone to see
how horrible it really is. Good luck deciphering it. I hope you can help me
make it work!

Dim a As String
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If obj.Name = units Then
a = a
Else
DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
".UNIT=" & "'" & units & "'" & "));"

End If
Next obj
 
O

Ofer

Where the unit parameter define?
What value do you insert in it?

And try this, add square brackets to the table name

For Each obj In dbs.AllTables
If obj.Name <> units Then
DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE (([" &
obj.Name & "].UNIT=" & "'" & units & "'" & "));"
End If
Next obj
 
B

Brendan Reynolds

The AllTables collection includes every table in the database, including
system tables, many of which (all of which as far as I am aware) do not
include any field named 'UNIT'.

If this code is running in an MDB, you could skip system tables like so ...

Public Sub ListNonSystemTables()

Dim aob As AccessObject
For Each aob In CurrentData.AllTables
If LCase$(Mid$(aob.Name, 2, 3)) <> "sys" Then
Debug.Print aob.Name
End If
Next aob

End Sub

.... assuming, of course, that you don't have any tables you *do* want
included with the letters "sys" in that position in the name.
 
D

Douglas J Steele

Probably a good idea to put it around the table name everywhere:

DoCmd.RunSQL "DELETE * FROM [" & obj.Name & "] " & _
WHERE [" & obj.Name & "].UNIT=" & "'" & units & "'"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ofer said:
Where the unit parameter define?
What value do you insert in it?

And try this, add square brackets to the table name

For Each obj In dbs.AllTables
If obj.Name <> units Then
DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE (([" &
obj.Name & "].UNIT=" & "'" & units & "'" & "));"
End If
Next obj

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Broadway said:
I knew you were going to ask for the code, i just didn't want anyone to see
how horrible it really is. Good luck deciphering it. I hope you can help me
make it work!

Dim a As String
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If obj.Name = units Then
a = a
Else
DoCmd.RunSQL "DELETE * FROM " & obj.Name & " WHERE ((" & obj.Name &
".UNIT=" & "'" & units & "'" & "));"

End If
Next obj
Click to expand...
Click to expand...
 
R

Ron Hinds

Might be easier to use "MSys", wouldn't it? And with the InStr function:

Public Sub ListNonSystemTables()

Dim aob As AccessObject

For Each aob In CurrentData.AllTables
If InStr(1, aob.Name, "MSys") = 0 Then
Debug.Print aob.Name
End If
Next aob

End Sub
 
B

Broadway

Thanks to everyone who helped with the problem, the program is working great
now. Watch for my next question, I'm sure it will be something easy like
this one was too. Appreciate it!
 
B

Brendan Reynolds

Although it is probably seldom used, you can give a table a name beginning
"USys", and Access will hide it when system tables are hidden. The code I
posted is designed to treat "MSys" and "USys" tables the same. Also, I did
some testing once and the results indicated that one of the two prefixes
"MSys" and "USys" was case-sensitive, while one wasn't. I can't remember now
which was which, but that is the reason for the use of the LCase$ function
in the code I posted. Basically, it is designed to treat as a system table
any table that would by treated as such by the Access user interface.

Strictly speaking, we should be testing for "MSys" or "USys" and ignoring
"ASys", "BSys", "AnythingButMOrUSys", I've just never bothered to do that as
it has never been a problem for me - I've never had tables with names
falling into that pattern.

Other than that, though, you are of course correct that a simple test for
"MSys" will work in most databases.
 
Top