Visual Basic, DAO TableDef problem

marty mcmahon
#1 / 3

DAO TableDef problem

I decided to post this here as well as in clbvd since some folks don't seem
to read both.

I have what I thought was a simple little routine to convert a databases'
tables. The original databases were created with tables that are numbers,
such as 12345. We are wanting to convert the name of the database to
something like this Daily12345. Seems easy enough. Here's my code:

For Each tdCurrentTable In dbTemp.TableDefs
If IsNumeric(tdCurrentTable.Name) Then
tdCurrentTable.Name = "Daily" & tdCurrentTable.Name
End If
Next

Should just loop through the tables and change the names. And it almost
works, but with every database, I get one -- and only one -- table that
gives an error. It says that the table is locked by another user and can't
be changed.

Anyone know what might be happening here? Or know a simple way to convince
DAO to just release the lock?

Any help would be appreciated,

Marty

Wed, 08 Sep 2004 01:14:05 GMT

Mike
#2 / 3

DAO TableDef problem

Marty:

Check table.attributes to make sure you're not trying to "convert" a system
table. Basically, if table.attributes = 0, then convert it, else skip it.

Mike T

Quote:

> I decided to post this here as well as in clbvd since some folks don't
seem
> to read both.

> I have what I thought was a simple little routine to convert a databases'
> tables. The original databases were created with tables that are numbers,
> such as 12345. We are wanting to convert the name of the database to
> something like this Daily12345. Seems easy enough. Here's my code:

> For Each tdCurrentTable In dbTemp.TableDefs
> If IsNumeric(tdCurrentTable.Name) Then
> tdCurrentTable.Name = "Daily" & tdCurrentTable.Name
> End If
> Next

> Should just loop through the tables and change the names. And it almost
> works, but with every database, I get one -- and only one -- table that
> gives an error. It says that the table is locked by another user and
can't
> be changed.

> Anyone know what might be happening here? Or know a simple way to
convince
> DAO to just release the lock?

> Any help would be appreciated,

> Marty

Wed, 08 Sep 2004 03:57:48 GMT

Johan Bechthu
#3 / 3

DAO TableDef problem

Assuming you're sure the database is not opened by anyone, do you open it
exclusive, and don't you try to change a system table?
Try the following additions to you code:

'Open the database in exclusive mode
Set dbTemp = OpenDatabase("MyDatabase.mdb", True)

For Each tdCurrentTable In dbTemp.TableDefs
'Check if it's not a system table
If (tdCurrentTable.Attributes And dbSystemObject) = 0 Then
If IsNumeric(tdCurrentTable.Name) Then
tdCurrentTable.Name = "Daily" & tdCurrentTable.Name
End If
End If
Next

Hope this helps,
Johan.

Quote:

> I decided to post this here as well as in clbvd since some folks don't
seem
> to read both.

> I have what I thought was a simple little routine to convert a databases'
> tables. The original databases were created with tables that are numbers,
> such as 12345. We are wanting to convert the name of the database to
> something like this Daily12345. Seems easy enough. Here's my code:

> For Each tdCurrentTable In dbTemp.TableDefs
> If IsNumeric(tdCurrentTable.Name) Then
> tdCurrentTable.Name = "Daily" & tdCurrentTable.Name
> End If
> Next

> Should just loop through the tables and change the names. And it almost
> works, but with every database, I get one -- and only one -- table that
> gives an error. It says that the table is locked by another user and
can't
> be changed.

> Anyone know what might be happening here? Or know a simple way to
convince
> DAO to just release the lock?

> Any help would be appreciated,

> Marty

Wed, 08 Sep 2004 04:16:35 GMT