• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

How do I deal with changing field names?

I'm building an Access database. I receive via email an excel table everyday. I've automated the extraction process and I have a table linked to the excel file in my database. Within this excel file are results from the pasts seven days, in seven fields. The field names are the dates on which the result occurred. I.e. 8/2/2007, 8/3/2007, 8/4/2007, and so forth, all as field names. These field names obviously change everyday as the first dated field rolls off as the latest date's results are included. I cannot make any modifications to the table I receive. I would like to be able to include some of this data in my reports, But how do I reference field names that change constantly?
0
sdholden28
Asked:
sdholden28
  • 15
  • 7
  • 6
  • +3
5 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
You can enter the data in your table under fields:

Sun Mon ... Fir Sat  WeekEnding
                                 8/4/2007
                                 8/11/2007
                                 8/18/2007

When you repot it, you can change Sun (8/11/2007) back to 8/5/2007 via some code.

Mike

                               
0
 
jmoss111Commented:
Only column 7 is new daily, and the previous days remain unchanged? How many rows per days column?
0
 
sdholden28Author Commented:
I don't quite understand. Even If i create a query to make a new table, I still have to reference the fields in linked table, whose field names change everyday. Are you telling me that I have to find a way to modify the table I receive via email? I cannot modify it. I must find a way to reference the changing field names.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aesmikeCommented:
give us a little more info on your table an what you want to do with it.
Are there always 7 fields, one per day or is it variable?  
0
 
Rey Obrero (Capricorn1)Commented:
sdholden28,
import the excel file to an access table.
0
 
sdholden28Author Commented:
There are many fields in the table which don't change. For the fields in question, there are always seven fields. Like todays table for example, column N-T are the days results. column N is named 8/2/2007 and column T is named 8/8/2007. Tomorrows table will be the same except column N will be 8/3/2007, and column T will be 8/9/2007. Is there a way to reference using the excel column letter?
0
 
jmoss111Commented:
Maybe you don't have to link to it. How about importing it and if the new day is always column 7 then the first row is date, second row is some data. How many rows of data are there? You are going to have to reshape the data to get an automated solution.
0
 
sdholden28Author Commented:
Capricorn -

I already have the table linked in my access database. The file is overwritten everyday with the new one, so of course the link allows the table to always have the most up to date information. That doesn't solve the fact that these field names change.
0
 
jmoss111Commented:
Import the excel file to a table. Create a recordset off the table and update another table if there are multiple facts per day.
0
 
sdholden28Author Commented:
jmoss -

That sounds like a possible solution. That way I could add the field names that I need to the data. How do I go about automating the import process so I don't have to do it manually everyday?
0
 
jmoss111Commented:
If you import then the day isn't the column name, T is.
0
 
aesmikeCommented:
1. keep your linked table.
2. Create a second table with the fields: Date, Value
3. When you get a new file, run some code like this:

dim rs as recordset
dim db as database
dim x as int
0
 
jmoss111Commented:
DoCmd.TransferSpreadsheet acImport, 8, "CumulativeEDIRejects_Import", "S:\SiteData\HSV2\Public\EDI\CurrentWeek\Current Week EDI Rejects.xls", False, ""

Something like the above will work for the import
0
 
Rey Obrero (Capricorn1)Commented:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\MyXl.XLS", True, "sheet1!"
0
 
Rey Obrero (Capricorn1)Commented:
here is a sub that will create always create a new table with new field names

Sub ImpXl()
Dim tbl As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "testTable" Then
    db.Execute "drop table testtable"
    Exit For
    End If
Next
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\myxl.XLS", True, "sheet1!"

End Sub
0
 
jmoss111Commented:
The sub below will read the imported excel file and update another table

public sub MyFileBuild
dim db as dao.database
dim rs1 as dao.recordset
dim rs2 as dao.recordset
dim MyDay AS Date
dim MyVal AS integer

Set rs = Db.OpenRecordset("SELECT Field6 FROM tblMyImported")
    rs1.MoveFirst
        Do While Not rs1.EOF
            MyDay = rs1!Field6  
    rs1.MoveNext
          MyVal = rs1!Field6
      
           
                   
            Set rs2 = Db.OpenRecordset("tblMyVals")
            With rs2
                .AddNew
                !Day = myDay
                !myDayVal = MyVal
               
                .Update
                .Close
           
            End With
0
 
sdholden28Author Commented:
Okay the import did the trick. All of my queries and reports have been modified and are working correctly also. Now for the code to update it. I guess the best way is to include this code in the on open event in one of my reports, that way the report will always have the most recent information. So my excel file is in C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls. Can someone write the code for this import for me? The name of the table to updae in Access is "Cross Tabs".
0
 
sdholden28Author Commented:
All fields and all records should be updated when the code is run.
0
 
harfangCommented:
This is a problem called "field mapping".

You get daily variable fields, and you want to translate them to something static like "Today", "Day-1", "Day-2", etc.

We know that these fields are the Excel columns N through T, or 14 to 20 (seven columns). When you import or link those, they will become the fields 13 to 19 (the fields are zero-based, hence the shift). You can create a query that will perform the mapping:

Sub RefreshQuery()

    Const FirstColumn = 13
    Const LastColum = 19

    Dim strSQL As String
    Dim intF As Integer
   
    With CurrentDb
       
        ' using the linked table from Excel
        With .TableDefs("TableYouHave")
            ' loop through all fields
            For intF = 0 To .Fields.Count - 1
           
                ' select field
                strSQL = strSQL & ", [" & .Fields(intF).Name & "]"
               
                If FirstColum <= intF And intF <= LastColum Then
                    ' "date field": rename
                    If intF = LastColum Then
                        strSQL = strSQL & " As Today"
                    Else
                        strSQL = strSQL & " As [Day" & intF - LastColumn & "]"
                    End If
                End If
               
            Next intF
        End With
       
        ' skip first comma:
        strSQL = Mid(strSQL, 3)
        ' build full SQL
        strSQL = "SELECT " & strSQL & " FROM TableYouHave"
       
        ' write to query
        .QueryDefs("RemappedQuery").Sql = strSQL
       
    End With
   
End Sub

As you might have guessed, this required the table "TableYouHave" and the query "RemappedQuery", ready to be rewritten each day.

You can then use the query to build any forms or reports you need: [Today] will always contain the latest values, and [Day-1] yesterday's.

Good luck!
(°v°)
0
 
sdholden28Author Commented:
Thanks for the code Harfang. I'm not sure this is exactly what I'm looking for however at this point in the game. I'm not an SQL guru but it looks like this code only refreshes the seven dated columns. All the data in the table that I receive daily is subject to change. These seven dated columns are however the only columns whos field name changes. What I have right now is a table in access that I created via import, changing the field names from the days date to Day 1, Day 2, Day 3 etc. The database works perfectly at the moment, but it obviously requires a manual import daily to refresh my data. So my goal is to write some code to simply automate that import process. I assumed this could be done since the attachment file I receive in the email always has the same filename and is overwritten in the same location every time a new one is received. Again, I'm not an SQL guru, but it doesn't look like this code is designed to do that. I could be totally wrong, and if I am, please correct me. Thank you however, very much for the time spent writing this code.
0
 
sdholden28Author Commented:
Capricorn1 -
Your code is perfect for the import, works just like it should. It does, like you said, create a new table with new field names. So now, how do I modify this code to replace the field names for the dated columns with Day 1, Day 2, Day 3, etc? I assume that this will probably be some conglomeration of the code you (capricorn) have provide and the code that Harfang has provided.
0
 
harfangCommented:
What my code does is write a query. That query remaps the column names.

Table's column names (in "TableYouHave"):

AAA BBB CCC 8/2/7 8/3/7 8/4/7 8/5/7 8/6/7 8/7/7 8/8/7

Query:

SELECT [AAA], [BBB], [CCC], [8/2/7] AS [Day-6], [8/3/7] AS [Day-5], [8/4/7] AS [Day-4], [8/5/7] AS [Day-3], [8/6/7] AS [Day-2], [8/7/7] As [Day-1], [8/8/7] AS [Today]
FROM TableYouHave

When you open that query, you will see these column names:

AAA BBB CCC Day-6 Day-5 Day-4 Day-3 Day-2 Day-1 Today

You can base other queries on that one, forms, reports, etc. Every time you get a new file, you rerun the function to write the new query. In effect, you map the variable column names to static column names.

That is really a very lightweight solution. You only write one string: the SQL query. This leaves the linked table entirely alone, but allows you to build your application as if you were getting a spreadsheet with the same column names every day.

Cheers!
(°v°)
0
 
harfangCommented:
The equivalent of "renaming field names in the table definition" through code could be:

    With CurrentDb
        With TableDefs("YourTransferedTable")
            .Fields(13) = "Day 1"
            .Fields(14) = "Day 2"
            ' ...
            .Fields(19) = "Day 7"
        End With
    End With

Perhaps that's easier to put in place that the query. A matter of taste, in the end.
Cheers!
(°v°)
0
 
sdholden28Author Commented:
Okay, progress progress progress. Harfang you hit it on the head, this I think is my preferred route. I have the following coding in my module as we speak, the import works, the renaming does not, apparently my syntax for the field renaming is a lttle off. I'll continue to work on it, but in the mean time here it is.

Sub ImpXl()
Dim tbl As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "testTable" Then
    db.Execute "drop table testtable"
    Exit For
    End If
Next
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls", True, "New_CrossTab"
  With CurrentDb
        With db.TableDefs("Cross Tabs")
            .Fields(13) = "Day 1"
            .Fields(14) = "Day 2"
            .Fields(15) = "Day 3"
            .Fields(16) = "Day 4"
            .Fields(17) = "Day 5"
            .Fields(18) = "Day 6"
            .Fields(19) = "Day 7"
        End With
    End With



End Sub

0
 
Rey Obrero (Capricorn1)Commented:
try this

Sub ImpXl()
Dim tbl As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "testTable" Then
    db.Execute "drop table testtable"
    Exit For
    End If
Next
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls", True, "New_CrossTab"
 
        With db.TableDefs("Cross Tabs")
            .Fields(13).name = "Day 1"
            .Fields(14).name = "Day 2"
            .Fields(15).name = "Day 3"
            .Fields(16).name = "Day 4"
            .Fields(17).name = "Day 5"
            .Fields(18).name = "Day 6"
            .Fields(19).name = "Day 7"
        End With




End Sub

0
 
Rey Obrero (Capricorn1)Commented:
just make sure that fields you want to change starts at the 14th column of your table, if not just change the numbering
0
 
harfangCommented:
Cap is right: you don't need the "with currentdb" if you already have the db variable. I also believe you want to change the fields in the table "TestTable" right after import, not the table "Cross Tabs". So that would be:

    With db.TableDefs("TestTable")   ' <-- check table name!
        .Fields(13).name = "Day 1"
        .Fields(14).name = "Day 2"
        ....

Good luck!
(°v°)
0
 
sdholden28Author Commented:
Okay I've been working on the code and this is what I have so far.

Sub ImpXl()
Dim tbl As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "CrossTabs" Then
    db.Execute "drop table CrossTabs"
    Exit For
    End If
Next
   
        With db.TableDefs("CrossTabs")
            .Fields(13).Name = "Day 1"
            .Fields(14).Name = "Day 2"
            .Fields(15).Name = "Day 3"
            .Fields(16).Name = "Day 4"
            .Fields(17).Name = "Day 5"
            .Fields(18).Name = "Day 6"
            .Fields(19).Name = "Day 7"
        End With

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "CrossTabs", "C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls", True, "New_CrossTab"
 


End Sub

As it stands I get a "Run-time Error '3625': Item not found in this collection" on the .Fields(13).Name = Day 1 line.

To answer your question Harfang, I honestly never understood exactly what testtable was since when used capricorn's code strictly for the import it worked even though testtable was not in my database. When I get this working I want one table, CrossTabs is fine, that gets updated everytime this code is run.
I'm going to see if I can add some more points for this question. There has been much code written, many responses, and a lot of effort put forth so I think it warrants it. Thanks so much everyone in advance.
0
 
sdholden28Author Commented:
Apparently 500 is the maximum.
0
 
harfangCommented:
You are doing this:

1) delete table CrossTabs
2) try to rename fields from the (now inexistent) table CrossTabs
3) (re-)create the table by importing from Excel

You need to switch 2) and 3).

Cheers!
(°v°)
0
 
sdholden28Author Commented:
Okay I've switched 2) and 3). Now I get error 3011, could not find object 'CrossTabs'. Seems the code is trying to place new data into the table rather than creating a new table all together.


Sub ImpXl()
Dim tbl As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "CrossTabs" Then
    db.Execute "drop table CrossTabs"
    Exit For
    End If
Next
   
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "CrossTabs", "C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls", True, "CrossTabs"
       
        With db.TableDefs("CrossTabs")
            .Fields(13).Name = "Day 1"
            .Fields(14).Name = "Day 2"
            .Fields(15).Name = "Day 3"
            .Fields(16).Name = "Day 4"
            .Fields(17).Name = "Day 5"
            .Fields(18).Name = "Day 6"
            .Fields(19).Name = "Day 7"
        End With



End Sub

0
 
sdholden28Author Commented:
The table CrossTabs is being deleted before the transfer, which is fine, but the transferspreadsheet command is looking for it, rather than creating it.
0
 
Rey Obrero (Capricorn1)Commented:
what are the name of the sheets ?

do you have sheet named Crosstabs?
0
 
sdholden28Author Commented:
Yes, but that code deletes it first so the new one can be created. Does the old table have to be deleted? Is there a "create table" command rather than simply a transfer?
0
 
Rey Obrero (Capricorn1)Commented:
sdholden28,
i am asking about your excel file Cross Tabs.xls, does it have a sheet name Crosstabs?

if that is the sheet name you want to get the data from you have to use
  "Crosstabs!"  notice the bang { ! } sign at the end

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "CrossTabs", "C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls", True, "CrossTabs!"
0
 
sdholden28Author Commented:
EUREKA! Thanks capricorn1! You have secured points for this question, however, Harfang will likely get a few as well for his help and effort. Okay so now I have the coding solution. I have a macro that will execute the module. I plan on simply running this macro in the on open event of my report of choice, that way, each time the report is opened it will guarantee that the latest information is available. Is there a better way? Or a reason I can't do it this way?

Working code:

Sub ImpXl()
Dim tbl As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each tbl In db.TableDefs
    If tbl.Name = "CrossTabs" Then
    db.Execute "drop table CrossTabs"
    Exit For
    End If
Next
   
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "CrossTabs", "C:\Documents and Settings\HoldenSD\My Documents\My Email Attachments\Cross Tabs.xls", True, "New_CrossTab!"
       
        With db.TableDefs("CrossTabs")
            .Fields(13).Name = "Day 1"
            .Fields(14).Name = "Day 2"
            .Fields(15).Name = "Day 3"
            .Fields(16).Name = "Day 4"
            .Fields(17).Name = "Day 5"
            .Fields(18).Name = "Day 6"
            .Fields(19).Name = "Day 7"
        End With



End Sub
0
 
harfangCommented:
You are using a db object, from which you delete a table. That object doesn't know anything about DoCmd, and will think the table is still deleted... simple as that. Add this line:

        db.TableDefs.Refresh   ' so it finds the new table
        With db.TableDefs("CrossTabs")
            ......

Cheers!
(°v°)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 15
  • 7
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now