Import Excel into Access

I came across this site, and program, to import Excel into Access.
You have to pay for it, of course.

 

Why pay when you get do it for free?

 

Fair enough, but a link would be handy. Bazza

===

 

I don;t think theefool means through a piece of free software Bazza. He means use Access yourself and import your own Excel data, as it was intended for this use by the user.

 

Ok, sorry. I did find one link, that should still work with 2003. But, who knows I only have 2k7.

http://databases.about.com/od/tutorials/ss/exceltoaccess.htm

 

You obviously have Excel and Access.

You are not importing Excel per say into Access you are simply importing Excel spreadsheets/files into Access.

Simply open Access go to File,Get External Data, Import etc.

That simple...No sites to go to No programs to purchase.

 

Thanks jewlzs, and others. :major
I should have said "Import an Excel spreadsheet" into Access.
If it is as easy as that to import a spreadsheet, why do third parties write programs to do it?

Does the MS Access import function work well? Are third party programs better at it?
I haven't tried it yet, as I am only a beginner in using Access. Bazza

===

 

I use this function all the time at work. You use it to bring data from your spreadsheet into Access. Usually you'll have a number of columns of data in the spreadsheet and each column is given a name corresponding to the field name in the Access database table you are importing to. As long as the headers are correctly named it works just fine.

 

Thanks mate, for the explanation. :cool :major Bazza

===

 

You only need to configure excel column headers to match access table headers if you are importing from excel to an existing access table.

There is also a shortcut to this issue copy your excel column one at a time and paste append to the access table.

Access is only quirky about symbols and if you used the alt enter in any of your excel cells this occurs most commonly in headers only.

Do make sure once you import that you go to the table design to check data properties are set correctly for each field - this will save you hours of query, property joins and/or formula issues.

 

Yep, it's a breeze once you get the hang of importing. Set yourself up a database you can stuff up and make a backup of it. Play around with all the various options and you'll soon figure it out. The backup can be copied multiple times to see what the difference is in the different options you chose.

And you can actually import more than one column at a time by copy and paste is my recollection, as long as the columns layout in Excel matches the ones you're going to in Access.

By the way, have you noticed that every time you open an Access database, the file date changes? This means you can't rely on date to know which is the most recently edited one. If you want the date to stay the same, either make a copy and open that to look at and then delete it when finished, or make the file read-only. The main disadvantage of making it read-only is that it will stop you from being able to access certain features that you might like to try out.

Also, make sure you use "Compact and Repair" regularly - I generally use it every few times I open or change the file, or if I make a lot of changes in one session. You'll notice the size of the file grows like Topsy - I compacted a file that was just over 1.4mb in the hopes it would then fit on my floppy and ended up with a file of 330k! Also, not doing this often enough makes it much more likely that your file will become corrupt. You can find "Compact and Repair" under the Tools menu: "Tools - Database Utilities - Compact and Repair" I think but as my laptop is currently refusing to talk to me in Win 98SE instead of Win 3.0 clone, I can't check. Anyway, that's close enough for you to be able to find it.

 

When you import Excel data into Access, you'll see a screen where you choose which Excel sheet to import from.

 

Thanks guys for the advice. :major
I'll have to hasten slowly on this one as I am a beginner in Access.
I will use a very simple, small, Excel spreadsheet to start. Bazza