Runtime Error 3828 - DELETE Query (1 Viewer)

Pienuts

Registered User.
Local time
Yesterday, 23:41
Joined
May 2, 2014
Messages
106
Hey, all!
I'm using Access 2013 and trying to run a simple DELETE query on a table in another database, but because of attachment fields, the query errors out (Cannot reference a table with a multi-valued field using an IN clause that refers to another database). I would really love to keep the attachment fields, although I wish there was a way to limit them to one attachment to avoid this problem.
So... is there a way to delete records in another database table with multivalued fields?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:41
Joined
May 7, 2009
Messages
19,169
if it can't be done through simple query, you can open the external db from code and delete the records from the table from there.
using vba code we can achieve this.
on vbe, insert new module.
add this code.

Public Function DeleteTableInExternalDB(ByVal strPathAndFile As String, _
ByVal strTableName As String, _
Optional ByVal strWhereCondition As String) As Boolean
'
' strPathAndFile = external db name plus the complete path
' strTableName = table name where to delete records
' strWhereCondition = criteria to delete. leave blank to delete all records
'
' example:
'
' to delete all records in Table1 in D:\myDir\Database2.accdb"
'
' debug.print DeleteTableInExternalDB("D:\myDir\Database2.accdb", "Table1")
'
' to delete record whose id=1
'
' debug.print DeleteTableInExternalDB("D:\myDir\Database2.accdb", "Table1", "id=20")
'
' SPECIAL NOTE:
'
' If your table or directory name has space between them, enclosed them in square bracket, ie:
'
' DeleteTableInExternalDB("[D:\my Dir\Database2.accdb]", "[Customer Table]", "[id Number]=20")
'
Dim objDb As DAO.Database
Dim strSQL As String

On Error GoTo err_handler
Set objDb = DBEngine(0).OpenDatabase(strPathAndFile, False, False)

strSQL = "Delete " & strTableName & ".* From " & strTableName
If strWhereCondition <> "" Then
strSQL = " Where " & strWhereCondition
End If
strSQL = strSQL & ";"

objDb.Execute strSQL
DeleteTableInExternalDB = True

graceful_exit:
Set objDb = Nothing
Exit Function

err_handler:
DeleteTableInExternalDB = False
Resume graceful_exit

End Function
 

Users who are viewing this thread

Top Bottom