Thread: Help With Appending My Table

Hello again Excel Forum,

Today I am trying to create an Access database to store some information, using Acces 2010. Let me give you some background info to help you understand my situation.

I am pulling reports from Oracle for data from 2010-2011. I have 3 Excel files for each year, with 4 months' worth of data per file, so 6 files total. As I add the first file, the second step in the wizard lets me change the Data Type for any of the fields. This is important because there are columns/fields that Access wants to read as numbers (Double), when they should be Text. I go through and check each field and change several to Text as needed. This part all works fine, and I import the first file as my new table.

Now here comes my trouble. As I want to append my new table with the second file (2 of 6), Access does not give me the option of changing the Data Type for any fields, so I am getting several Type Conversion Errors. One thing I notice is that after I imported the first table, I saved my import steps thinking I could use this to make sure all the fields' Data Types are brought in properly for the rest of my files. I am still unsure how to append my table and have my fields Data Types change to match the first table. Your thoughts/suggestions will be greatly appreciated.

Thanks in advance,

Keith

Is it possible to get your files into a text based file format? I'm thinking a .csv file. Excel files can easily be converted to .csv. Access allows you greater flexibility with setting the import specifications when you have a .csv file.

If you are able to do this, then when you start the import wizard, you will notice that there is an advanced button at the bottom of the window. Click on it and make your modifications. Then save them as Specification. Now each time you import your files, you can set the specification for each one based upon how you set them up.

Further to be sure that the data is brought in correctly, import each to a temporary table. Review the table for completeness and then run an append query to append all the files to the correct table.

Once you get comfortable with this, you can automate these steps with some simple VBA.

Alan