Results 1 to 4 of 4
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Alter Table Statement Not Executing

    I am trying to alter table to add some fields to the table. The statement executes error free, but when I design view the table the fields do not exist. If I run this statement in a function on it's own it executes and design view the table has the fields added. What is preventing this alter table statement from executing and altering the table in my function grab?



    Code:
    Public Function Grab()
        Dim importPath As FileDialog
        Dim sPath As Variant
        Dim importFile As String
        Dim rowNum As Integer
        Dim excelapp As Object
        Dim wb As Object
        Dim countSQL As String
        Dim generaTest1oreNumbersSQL As String
        Dim qdf As QueryDef
        Dim maxFields As Integer
        Dim index As Integer
        Dim i As Integer
        Dim rs As Recordset
        Dim rst As Recordset
        Dim myArray As Variant
        Dim arr As Variant
        
        DoCmd.SetWarnings False
        DoCmd.Hourglass True
        
        On Error Resume Next
        DoCmd.DeleteObject acTable = acDefault, "Test11"
        DoCmd.DeleteObject acTable = acDefault, "StoreNumbers"
        DoCmd.DeleteObject acTable = acDefault, "ExportData"
        DoCmd.DeleteObject acQuery, "CountByStore"
        
        Set importPath = Application.FileDialog(msoFileDialogFilePicker)
    
    
        With importPath
            .AllowMultiSelect = False
            .Title = "Select the import file"
            .Filters.Add "All Files", "*.*"
            If .Show = -1 Then
                importFile = .SelectedItems(1)
                
                'Capture the range to import
                Set excelapp = CreateObject("excel.application")
                Set wb = excelapp.Workbooks.Open(importFile)
                rowNum = excelapp.Application.CountA(wb.worksheets("sheet1").Range("A5:A65500"))
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Test1", importFile, True, "A5:AT" & rowNum
                
                wb.Close
                Set wb = Nothing
                excelapp.Quit
                Set excelapp = Nothing
            Else
                MsgBox "You did not select a spreadsheet to import"
            End If
        End With
        
        'Create Table To Move Data To
        DoCmd.RunSQL ("Create Table [StoreNumbers] (FieldPK COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, StoreNumber Text);")
        
        'Append Unique Stores Into Table
        generaTest1oreNumbersSQL = "INSERT INTO StoreNumbers ( StoreNumber ) SELECT Test1.[Address 1] FROM Test1 GROUP BY Test1.[Address 1] ORDER BY Test1.[Address 1];"
        CurrentDb.Execute generaTest1oreNumbersSQL, dbFailOnError
    
    
        'Getting the items from the Description field
        Set rs = CurrentDb.OpenRecordset("SELECT Test1.Description FROM Test1 GROUP BY Test1.Description ORDER BY Test1.Description;")
        
        'Creating an array of the items
        With rs
            .MoveLast
            .MoveFirst
            myArray = rs.GetRows(300)
        End With
        
        rs.Close
    
    
        'Adding a column in the table for each item
        For Each arr In myArray
            DoCmd.RunSQL "Alter Table [StoreNumbers] Add [" & CStr(arr) & "] Text"
        Next
        
        'Dynamically Using DLookup to copy over the values from the imported table to the production table
        Set rst = CurrentDb.OpenRecordset("StoreNumbers")
     
        While Not rst.EOF
        For i = 2 To rst.Fields.Count - 1
            a = DLookup("[PO Qty]", "Test1", "[Address 1]='" & rst.Fields("StoreNumber") & "' and [Description]='" & CStr(rst.Fields(i).Name) & "'")
         
            rst.Edit
            If IsNull(a) = False Then
                rst.Fields(i) = a
            Else
                rst.Fields(i) = 0
            End If
            
                rst.Update
            Next i
             
        rst.MoveNext
        
        Wend
        
        'Altering table to add the needed fields for the export
        DoCmd.RunSQL "Alter Table [StoreNumbers] Add AttentionTo Text, Address1 Text, City Text, St Text, Zip Text, Phone Text, [SHIPPING REFERENCE # FOR FEDEX LABEL] Text"
    
    
        
        DoCmd.SetWarnings True
        DoCmd.Hourglass False
        
    End Function
    The line of code that is giving me trouble is this
    Code:
        DoCmd.RunSQL "Alter Table [StoreNumbers] Add AttentionTo Text, Address1 Text, City Text, St Text, Zip Text, Phone Text, [SHIPPING REFERENCE # FOR FEDEX LABEL] Text"
    Which as I said if executed in it's own function will alter the table but for some reason while run in the function Grab() will not. Why?!?!?!?!?!?!

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can't say definitively but maybe this will help - disable the setwarnings false line and step through the code and check variable values as you go. You might raise an error, which will certainly point to a problem. On that note, if you turn them off and don't have a properly constructed error handling block you can find that they're turned off for the rest of the session, which might be bad. The only other thing I notice that is suspect is that AFAIK, getrecords is only for a 2 (or more?) dimensional array and your array only has one dimension. You might want to take a different approach for that, especially if you find your array has no values because of it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    @Micron - thanks for the tip on the get records - ill def look into that.

    I commented out the SetWarnings lines and executed the code and no issue came to light.

    Next stepping through my code, I added a breakpoint to the DoCmd.RunSQL line and tried to design view the table which showed me that the table was locked and I could only open it for read-only. Which explained why the alter table statement was not executing.

    To resolve, after the Wend statement I added
    Code:
    rst.Close
    And now my table is no longer locked and I can edit it through VBA

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you solved it. I suggest you do something about how you turn warnings on and off.
    You probably could use CurrentDb.Execute method instead.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Alter Table Statement
    By jo15765 in forum Modules
    Replies: 5
    Last Post: 03-09-2017, 05:05 PM
  2. Replies: 10
    Last Post: 10-11-2016, 02:39 AM
  3. If statement to alter text box value
    By eskybel in forum Reports
    Replies: 13
    Last Post: 08-05-2013, 06:17 PM
  4. Trying to get an Alter table to work
    By itm in forum Programming
    Replies: 5
    Last Post: 07-31-2012, 06:32 AM
  5. Alter table Yes/No field
    By Cojack in forum Queries
    Replies: 7
    Last Post: 10-27-2010, 11:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums