Error 3211 delete table with multi-valued lookup field

[Joyce]

I have VBA code in an Access 2007 db that copies a table from another
database, views some of the field properties, and then deletes it. This VBA
code worked fine in Access 2003. However in Access 2007, if the table has a
multi-valued field I get error 3211 "The database engine could not lock the
table because it is already in use by another person or process" when I
execute the SQL delete statement.

Any ideas how I can get around this error and delete the table?

 

[Allen Browne]

Joyce, how did you go about this?

This works:
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"c:\data\Testbed2007.accdb", acTable, "A2007mv", "A2007mv", False
DBEngine(0)(0).Execute "DROP TABLE A2007mv;", dbFailOnError

My usual approach for import would have been:
SELECT * FROM A2007mv in 'c:\data\Testbed2007.accdb';
but this fails with the message:
Cannot reference a table with a multi-valued field
in an IN clause that refers to another database.

 

[Joyce]

Hi Allen,

Here is the command that imports the table and names it "X":
DoCmd.TransferDatabase acImport, "Microsoft Access", filename_C, acTable,
object, "X"

Here is the command that deletes the "X" table:
DoCmd.DeleteObject acTable, "X"

The delete command causes the error condition. If I stop VBA at this point,
I won't even be able to manually delete the "X" table. I will get an error
that the table is locked. The only solution is to close the program, open
it, and then manually delete the table.

I cannot use the "Select" statement as I must import the table so that I can
check field properties, not data.

Perhaps it will help if I explain what the program does. I teach Access and
my students submit Access files for homework and exams. I write grading
programs to grade their Access files. Most field properties can be graded
using the Select statement and then I inspect the property settings.
However, I can't find a property to check whether a student has defined a
particular field to be a primary key. My solution was to import the table
and insert two records into it. If the primary key was correctly defined
then an error condition would occur. After this check, I delete the table.
This has worked satisfactorily with previous versions of Access.
However, in Access 2007 if any field in the table is mult-valued then I
can't delete the table.

 

[Allen Browne]

Okay: I didn't try DeleteObject, but executing the DROP query should solve
your problem.

An alternative approach would be to OpenDatabase() on filename_C. You can
then open the TableDef, and loop through its Indexes collection seeing if
there is one that is primary.

 

[Joyce]

Hi Allen,

Thank you for your suggestions.

I tried the DROP query and got the same error 3211. I also cannot manually
delete the table unless I close the program and open it again.

I like your alternative idea, but can't quite get it figured out. I have
been able to open the TableDef in the student table and loop through the
indexes. There happen to be 6 indexes in this case. However, only one
should be the primary key. I can't figure out how to determine which index
is the primary key.

One alternative:
Since I know what the primary key (let's call it "pkeyname") should be, I
can open the index for the primary key using

x = student_db.TableDef("tablename").Indexes("pkeyname").Name
What do I check to determine that this is the primary key? It has one item
and 8 properties, none of which indicate that this is the primary key.

A second alternative is to open the Index called "PrimaryKey" using
x = student_db.TableDef("tablename").Indexes("PrimaryKey").Name
What do I check to determine that "pkeyname" is the primary key? This also
has one item and 8 properties, none of which indicate that "pkeyname" is the
primary key.

Where do I go from here?

 

[Allen Browne]

Okay, Joyce, since I have no problem deleting the table and you do, I have
to assume that something else in the code you have is not releasing the
table, and hence it can't be dropped. If you OpenRecordset, make sure you
Close and set it to Nothing.

You are almost there with identifying the indexes! You succeeded in looping
through the Indexes collection, and an index has a property named Primary.
It's true if the index is the primary key. So:

If ind.Primary Then
Debug.Print "The primary key index is named " & ind.Name
End If

If you are certain the index is named pkeyname, you could test it like this:

Debug.Print CurrentDb.TableDefs("Table1").Indexes("pkeyname").Primary

 

[Allen Browne]

Joyce, here's some more examples of manipulating indexes.

This one creates indexes, and sets one as primary key:
http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

There's a function in this utility that tests if a table lacks a primary
key:
http://allenbrowne.com/AppIssueChecker.html
If you download the sample database, the function is named HasNoPK()

 

[Joyce]

Allen,
I got it to work. Thank you soooooooo much!! I also figured out how to
check for composite keys. Your utility is awesome!! I can also check for no
keys.

You are fabulous!!!!