Auto rename of Table FieldNames

[Nick Hampu]

Every Monday, I download an Excel spreadsheet from our customer's website. It details what the current demands are for the next year, divided up into months. The spreadsheet shows, for instance, a demand in October, November, etc. I currently import the spreadsheet into Access into a table called Default. This table is created off of the spreadsheet using Field Names from the Import Wizard (First Row Contains Column Heading) which are the names of the months. I then append the table into a more generic version that appends the field name OCTOBER into CURRENT MONTH, NOVEMBER into 2nd MONTH, etc. (I do it this way because there are several forms and reports that run off of it) This works fine for 3 weeks of the month, but at the beginning of each month, I have to change the Append Query as the destination looks for OCTOBER and now its NOVEMBER.

My question is, is it possible to have Field Names in the destination table that update themselves? For instance, having a Field Name with some kind of code such as DateAdd ("m",1,Date()).

 

[mdmilner]

This is probably a bit more than what you're asking for.
These functions are part of a much larger package I wrote as part of an attempt to automate xls report building. These segments grab the existing field names from a source and destination table and append one to the other.

Basically, what you'd end up doing is creating your destination table with the correct fieldnames and then make sure that your import gets the right NUMBER of fields.

The way I'd suggest making this work is to put the call to build the table into a form button that access a combo-box. The combo-box would have the names of your different destination tables (sounds like you have a differnt table name for each months data)

Only concern I'd have is, that's not really the best way to organize the data - it's a very *Excel* way to think about organizing the data though.
Technique I'd suggest is identify each months data within a single field - if you have a date field for example. You'd then append all data to a single table and then use a query to extract only information for the month you wish to pull.

You'd then use the query as a recordsource for any reports or if you needed to export the data.

Mike

Sub ShowCalls()

' This is how you call the functions
' This deletes the records from the destination table unless you wish to keep them
Call Assembler("delete", destTBLname)
' This appends a source table to the destination table

Call Assembler("append", destTBLname, srcTBLname)

' Technically this is cleanup - after appending, I didn't need the original table anymore

If ObjectExists("Table", srcTBLname) Then
       DoCmd.DeleteObject acTable, srcTBLname
End If

End Sub



Public Function Assembler(ByVal strMode As String, ByVal tblName As String, _
                        Optional ByVal tblSource As String)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()

Select Case strMode
  Case "delete"
    strSQL = "DELETE * FROM " & tblName
  Case "append"
    strSQL = "INSERT INTO " & tblName & " ( " & GetFlds(tblName) & " ) "
    strSQL = strSQL & "SELECT " & GetFlds(tblSource) & " FROM " & tblSource
  Case "make"
    strSQL = "SELECT " & GetFlds(tblSource) & " INTO " & tblName & " FROM " & tblSource
  Case Else:
End Select
DoCmd.RunSQL strSQL

Set rs = Nothing
Set dbs = Nothing
End Function

Public Function GetFlds(ByVal myTable As String, Optional ByVal myType As String) As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Integer

Set dbs = CurrentDb()
strSQL = "SELECT * FROM " & myTable

Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
  For x = 0 To .Fields.Count - 1
    Select Case .Fields(x).Name
      Case "Date", "Now", "Field"
        GetFlds = GetFlds & "[" & .Fields(x).Name & "], "
      Case Else:
        GetFlds = GetFlds & .Fields(x).Name & ", "
    End Select
  Next x
End With

' This cleans up the results removing the final comma and space
GetFlds = Trim(Left(GetFlds, Len(GetFlds) - 2))

End Function

Function ObjectExists(ByVal strObjectType As String, _
                ByVal strObjectName As String) As Boolean

     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
On Error GoTo HandleErr
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If

End Function