Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add/alter columns in code

    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!

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Sorry if sounds obvious, but are you opening the database only once?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Last edited by ruirib; 2011-07-25 at 17:29.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Last edited by ruirib; 2011-07-25 at 18:09.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yeah that did it - thanks!

  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great, glad it's sorted .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •