Appending Excel Data into Access Database

ToNWoN · Mar 28th, 2002, 02:09 PM

I'm stumped on a problem and hoping someone out there can give me some pointers. It deals with both MS Access and Excel. Here goes:

I have an Access database with 20,000 rows of Item Numbers, and an Excel spreadsheet of 5,000 rows with Item Numbers. I have to search through the rows of Item Numbers in Access and see if they match with an item number in Excel. If the item number from Access matches with an item number in Excel, I have to append the entire row of data from Excel into the corresponding row in Access.

On top of this, I'm supposed to flag the row with a matching Item Number in Excel if a match occurs.

Any help would be greatly appreciated.

Tony

Casey · Mar 28th, 2002, 02:16 PM

You can import the excel file into your access database as a new table. this can be done by going to "get external data" in the file menu at the top left of the access screen and "import" not link. this will take you through a wizard that will make your excel workbook a table. then you can link the tables and do a query to bring together the information. Really shouldnt be too hard. write back if you need more info.
Casey

Ziggy · Mar 29th, 2002, 02:01 PM

Check out this site it is similar to this one but for Access,

http://www.access-programmers.co.uk/...assCookie=true

Yogi Anand · Mar 29th, 2002, 02:41 PM

Quote:

On 2002-03-28 13:16, Casey wrote:
You can import the excel file into your access database as a new table. this can be done by going to "get external data" in the file menu at the top left of the access screen and "import" not link. this will take you through a wizard that will make your excel workbook a table. then you can link the tables and do a query to bring together the information. Really shouldnt be too hard. write back if you need more info.
Casey

Hi Casey:
In reference to your statement of importing a table from excel and then linking it! Technically you can link the excel(list or table) directly into Access without importing it.

daleyman · Mar 29th, 2002, 10:17 PM

A simple piece of ADO, DAO would handle this quite easily, if your intention is to automate the process, for example if you need to do it very often. If you want me to elaborate then give me some more precise details (especially the access table fields and data types).

If you do not need to do it very often then the manual processes would work fine.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 02:09 PM	#1
ToNWoN New Member Join Date: Mar 2002 Location: Toronto, ON Posts: 1	I'm stumped on a problem and hoping someone out there can give me some pointers. It deals with both MS Access and Excel. Here goes: I have an Access database with 20,000 rows of Item Numbers, and an Excel spreadsheet of 5,000 rows with Item Numbers. I have to search through the rows of Item Numbers in Access and see if they match with an item number in Excel. If the item number from Access matches with an item number in Excel, I have to append the entire row of data from Excel into the corresponding row in Access. On top of this, I'm supposed to flag the row with a matching Item Number in Excel if a match occurs. Any help would be greatly appreciated. Tony

Mar 28th, 2002, 02:16 PM	#2
Casey Board Regular Join Date: Feb 2002 Posts: 82	You can import the excel file into your access database as a new table. this can be done by going to "get external data" in the file menu at the top left of the access screen and "import" not link. this will take you through a wizard that will make your excel workbook a table. then you can link the tables and do a query to bring together the information. Really shouldnt be too hard. write back if you need more info. Casey

Mar 29th, 2002, 02:01 PM	#3
Ziggy Board Regular Join Date: Feb 2002 Location: Ontario, Canada Posts: 326	Check out this site it is similar to this one but for Access, http://www.access-programmers.co.uk/...assCookie=true

Mar 29th, 2002, 10:17 PM	#5
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	A simple piece of ADO, DAO would handle this quite easily, if your intention is to automate the process, for example if you need to do it very often. If you want me to elaborate then give me some more precise details (especially the access table fields and data types). If you do not need to do it very often then the manual processes would work fine.