Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

append excel data to access table

Status
Not open for further replies.

cmz21

Programmer
May 5, 2005
110
US
hello,
I have an excel spreadsheet with data that I have to append to an access table. This is going to happen every month so I would like to automate as much as possible. Could somebody recommend the best way to accomplish this?

Thanks
 
There are many approaches possible. Do you want to control the process from Access or from Excel? Do you create the spreadsheet yourself or do you get it from somewhere else? Do you have control over the format? What does the data look like - how many fields are you talking about?
 
Its a simple excel sheet which I create. It has 2 columns, not exactly sure how many rows (I would guess 1500 at most).
It has no formatting - just a straight data dump.

Not too much data - just a pain to have to do manually.
 
How are you doing it now, and do you want to control it from Excel or Access?
Why not just a docmd.transferspreadsheet in Access?
 
I am not doing it all now.....just wondering what the best way would be to attack it.
I will try the docmd.transferspreadsheet.
 
Have you considered what your database design should be? Is one of your 2 columns a unique identifier, or do you have another plan for a primary key in your table? Is there other information you should be storing in addition to the 2 columns?
If you don't consider things like that before you start collecting data you might have problems retrieving what you want later.
 
Is it possible to do this from within the excel code?
I am using an excel vba macro to create the spreadsheet with the data to be added to the access table. Is there a way to add more code to this excel sheet to open the access table and automatically append the data?
 
I am planning on using an autonumber field as the primary key in the table. It is a list of cellular phone numbers with the charge for each number. Each month, I will append the new months charges for each number. I will be using this to show trending for each number.
 
In vba help, look at 'openrecordset' and the 'edit method example' for details of how to use DAO to manipulate Access tables.
(I know ADO is newer, but I started with DAO and it still works)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top