Access VBA Runtime Error 3211

Question

Good Morning and thank you for your time!

I am having an issue changing the name of a field in a table using VBA.  I know this is not common.  However, I work at a command in the Navy where commands change and therefore require the table fields be renamed.

I am receiving Runtime Error 3211 (The database engine could not lock table 'tblInformationTable' because it is already in use by another person or process) on the following code

Private Sub UpdateType_Click()

Dim db As DAO.Database

Dim tbl As DAO.TableDef

Dim fld As DAO.Field

Dim x As String

Dim y As String

Set db = CurrentDb

Set tbl = db.TableDefs![tblInformationTable]

If IsNull(OldCommand) = True Or IsNull(NewCommand) = True Or OldCommand = "" Or NewCommand = "" Then

MsgBox "Notification Type and Replacement Type cannot be blank"

Exit Sub

End If

x = Left(OldCommand, Len(OldCommand))

y = Left(NewCommand, Len(NewCommand))

For Each fld In tbl.Fields

If Left(fld.Name, Len(fld.Name)) = x Then

fld.Name = y 'This is where i receive the error

End If

Next fld

DoCmd.Close

DoCmd.OpenForm "frmNotificationType"

MsgBox x & vbNewLine & vbNewLine & "has been changed to " & vbNewLine & vbNewLine & y

Set fld = Nothing

Set tbl = Nothing

Set db = Nothing

End Sub

Answer 1

A query is a SQL statement. The query you see in Query Design mode that shows the tables and columns has a SQL Statement behind it. But SQL statements come in 2 flavors. DML and DDL. DML is what the Query Design mode generate. It stands for Data Manipulation Language. DDL which stands for Data Definition Language. This flavor of SQL can be used to create and alter tables.

OK, so the table you are dealing has name fields, a phone # field and (I assume) an e-mail field.

Based on what you are saying you change the name of the Yes/No field to indicate the type of Notification. This as Ken and I have said is bad design. You should have one field for Notification Type and another field for Notification date. You should then create a record for each notification with the date and type.

Answer 2

I am no expert with access and the VBA that i do know is self taught. So, my knowledge is very limited in best practices. I kind of just do what I can get to work. Funny enough, I am the most knowledgeable with it where i work haha

That being said I am not sure what an SQL statement is.

This database isn’t dedicated to holding much information. Rather, it is there to send out mass communication texts via outlook. The only table that stores data has a phone number for each person and what type of notification they are to receive, via a yes/no field.

With my limited working knowledge this has been the easiest method for me to accomplish changing the type of notification without deleting data in the table or having to input data after creating a new field.

If writing an SQL statement is the best method I would love to learn.

 Thank you for the input!

Answer 3

Scott has hit the nail on the head by pointing out that 'defining data using field names is very bad design'.  In the jargon of the database relational model it is known as 'encoding data as column headings.'  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

Without knowing more details of your current table definition I can't comment further, but the salient point is that if data is stored correctly as values in rows in tables, it is then merely a  matter of editing the data in the usual way via appropriate forms.

Answer 4

Hi. Stupid question: You're not trying to modify a linked table, are you? Just checking...

Answer 5

First defining data using field names is very bad design.

Second, I would be more inclined to use an ALTER TABLE SQL statement.