add/alter columns in code

Topic

New Reply

I have some VBA that should allow me to cycle thru a lot of tables in an Access db and do things like this:

strSQL = “ALTER TABLE [” & tdf.Name & “] ADD [Confirmed How? (Code)] TEXT, [OES Notified Y/N] TEXT;”
dbs.Execute strSQL

using DAO. when i run it the code fails and says the table cannot be altered because it is in use by someone else (presumably the function itself). if i debug.print the SQL and run it in the Query window, i do not get this error.

I am not sure what i can do here – is there something i need to do to ‘unlock’ the table? my function is, annoyingly, blocking itself from executing.

TIA!

Reply | Quote

Replies

Sorry if sounds obvious, but are you opening the database only once?

Reply | Quote

i believe so. the function opens with

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

‘ Fill collection
Set dbs = CurrentDb

then i create a table def so i can inspect the tables and only look at ones with a specific naming convention; if found instantiate a recordset to begin iterating thru the collection on the found table:

For Each tdf In dbs.TableDefs
If IsNumeric(Left(tdf.Name, 1)) = True Then
Set rst = dbs.OpenRecordset(tdf.Name, dbOpenTable)

Since i am adding new columns, i set a flag and inspect the column names so i don’t try to add the same columns twice:

If Not rst.RecordCount = 0 Then
rst.Edit
i = 0

For Each fld In tdf.Fields
If fld.Name = “OES Notified Y/N” Or fld.Name = “Confirmed How? (Code)” Then
i = 1
End If
Next fld

The fun is occurring when i try to add columns in those cases where the flag remains 0:

If i = 0 Then
strSQL = “ALTER TABLE [” & tdf.Name & “] ADD [Confirmed How? (Code)] TEXT, [OES Notified Y/N] TEXT;”
dbs.Execute strSQL
End If

rst.MoveNext

in effect, the loop appears to be locking the tables automatically. i set up some debug.prints to check to make sure i’m not just hitting the same table over and over again but that part seems fine. i think somehow the way i am opening the recordset might be the problem but nothing strikes me at this time. I’ve done this before (well not in a loop) and adding columns using SQL works fine.

Reply | Quote

I am not sure, because I have never done anything similar to what you are doing, but I would say the problem may lie with the fact that you have an open recordset, with records from the same table, while you are, at the same time, trying to change the table.

Two alternatives to fix it:

1. use getRows to get the recordset into an array, close the recordset and then process each element of the array, like you are doing now with each element of the recordset;

2. Instead of executing the SQL to change the table immediately, add it to an array of SQL statements to execute. Once you are done navigating the recordset and after closing it, go through the SQL statements array and execute them.

Probably 2 is easier to do, as it will require less changes to your code.

If you need help with any of the alternatives, let me know and i may try to code something.

Reply | Quote

i have thougth about option 2 but i am not sure how i would write a single SQL statement that adds rows to several tables, and i’d prefer not to write the SQL to some other table and loop thru that. but i agree that it’s probably the case that DAO doesn’t want me to manipulate a table while i have it open. i was hoping there was some way to open a recordset such that it doesn’t lock the table when i operate on the table with a tabledef.

the preferred option would be to create an in-memory array of the SQL statements and then loop thru that; i can do that in .NET but i’ve never tried it in VBA/Access. but that sounds like a useful technique in any case… i also have a routine that does a UNION select on all these puppies (the tables i mean) and i can imagine doing that in VBA as well….

well, i will explore memory array options…

Reply | Quote

It’s simple to do it in VBA. I can do it easily. Just to have redimensioning arrays and such, do you know how many tables you have there? If you tell me I will rewrite your code for option 2.

Reply | Quote

at present, there are 23 tables that need to be massaged. i am somewhat familiar with arrays but it’s been a while. currently i am trying to figure out how to insert the SQL into an array. It should be in the code block

If i = 0 Then
strSQL = “ALTER TABLE [” & tdf.Name & “] ADD [Confirmed How? (Code)] TEXT, [OES Notified Y/N] TEXT;”
INSERT SQL COMMAND INTO ARRAY
End If

Reply | Quote

Ok, here is an attempt to solve it. I cannot post the complete code because when I tried, several of your if statements were not ending and I didn’t where they should end, so here is my suggestion.

Code:

'after your initial code:

 Dim arrSQL(25) As String   'set the value 25 to a bigger value if you have more than 25 tables.
 Dim sqlCount As Integer
 
 sqlCount = 0

(…)

Code:

'now, when you are executing the code, do this instead:

'The fun is occurring when i try to add columns in those cases where the flag remains 0:
        
 If i = 0 Then
   arrSQL(sqlCount) = "ALTER TABLE [" & tdf.Name & "] ADD [Confirmed How? (Code)] TEXT, [OES Notified Y/N] TEXT;"
   sqlCount = sqlCount + 1
 End If

‘THis is the final part. The recordset needs to be closed before you get into the loop. This also comes after you have processed all your recordset.

Code:

rst.Close
set rst = nothing

If sqlCount > 0 Then
    For intI = 0 To sqlCount - 1
           dbs.Execute arrSQL(intI)
    Next
End If

dbs.Close

This should do it. Not sure if dbs.Close is needed at the end.

This shows the most relevant changes to your code. Hope it helps.

Reply | Quote

yeah that did it – thanks!

Reply | Quote

Great, glad it’s sorted :).

Reply | Quote