Appending Excel data to Access table - mismatched Excel row headers and Access field names

Question

Hi everyone,

I will start off by saying I do not know any programming language such as VBA. If there is a solution to my issue, and it involves programming, I will need some heavy, ultra beginner-level, step-by-step assistance and would greatly, greatly appreciate it.

I am collecting form data on my work's website, and I need to get it into an Access database, and then automate Access with another data platform. The main issue is with the headers. They ultimately need to have no spaces in between them.

When I export the form data from my work's website, the Excel row headers are simply the form questions/fields, with spaces and any kind of special characters included. I am not able to use internal field names on our website to change how the headers are downloaded. Below is how the data turns out in Excel.

When I initially import this Excel into Access, everything imports fine. However, I need to change the Field Names in Access, because the data automation platform that needs to connect with Access will NOT accept spaces. I also don't think it accepts any special character besides the underscore. Below is what I need the Field Names to be.

Changing the Field Names causes me to not be able to append data, because the Excel headers and the Access "Field Names" are supposed to match. Is there a way around this? 

Thank you for any help.

Answer 1

Ok, I think you misread the instructions. Let me be specific.

1. On the Create ribbon, select Query Design
2. In the Dialog box, select the linked Excel table and press Add, Close the dialog.
3. Add each field to the grid by doubling clicking on each field in turn.
4. Click Append from the Design ribbon, in the dialog select the Access table you want to Append to. Press OK, this will add an Append To row to the grid.
5. In each column select the field in the destination table that you want to append that field to.
6. When you have all the fields you want append matched up, press Run from the ribbon.

That should add the records in the linked table to the Access table.

Answer 2

Hi Stephanie, I'm an independent adviser and will try to help.

The way to do this doesn't really involve VBA. What you need to do is LINK to the spreadsheet that comes from your website. Then you create an Append query that Appends the data from the linked Excel sheet into the Access table. Assuming the Excel headers are the same all the time, you only need to create this query once. When you link to the Excel sheet give it the same name and just run the query.

As an aside, Your Access table may not be roughly the same as the Excel sheet. You may need to normalize the data properly. Without seeing all the data points its hard to tell.

If you need further clarification on this feel free to ask.

Answer 3

Thank you Scott. I am pretty new to Access. I tried running an Append query as you recommended, using these instructions from Microsoft Support, but it is giving me an error:

To me it sounds like it's because the Field Names are mismatched between the source data table, and the new data table that has the correct Field Names I need. 

Source data table, which records need to be appended to destination table:

Destination table:

Answer 4

Hi Scott,

Thanks for such prompt replies. I think it worked.

I had initially followed this part of the support article: "To quickly add all the fields in a table, double-click the asterisk (*) at the top of the list of table fields." I did this for the destination table as well, because all data and Field Names are the same. It didn't work. 

Doing it one-by-one worked. Thank you so much.

Answer 5

I think what you did was try to add both tables to the query. You just needed to add the source, then match the fields for each column.

Glad the problem is solved.