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?!?!?!?!?!?!