Exporting data from excel to access

  • Thread starter Thread starter Chris B.
  • Start date Start date
C

Chris B.

I am posting this on both excel & access boards because I
am not sure where it should go, please forgive the
duplication.

I am using excel as a data input, multiple files, same
format, with 2 pages that contain data formatted to be
exported to a single access file for reporting. What I
want is a macro in excel that a user can just click to
export the two sheets to their respected access tables in
the same database.

The second part of the problem, is I then want to delete
any duplicated records in access to leave be with only the
records that have been added or changed since last
export. Please remeber that the data needs to come from
many excel files.

Thanks in advance, Chris Beardsley
 
Start by using Automation from Excel to open the Access database and run a
Delete query against each of the tables you need cleaned. Then transfer the
new data.

HTH
 
First, if your data is in the same workbook as your code i.e. you want
to query an open workbook, you should be aware of the memory leak bug:

Microsoft Knowledge Base Article - 319998
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998

However, this isn't a show stopper. Save a copy of the workbook, in a
temp folder perhaps, and query the copy (i.e. closed) workbook.

General approach: DELETE the duplicate rows from your MS Access table
and INSERT the new rows. To identify the duplicates you do a JOIN. You
can't create a join between tables in different databases, so you will
have to create a temp table in the MS Access database.

Here are some example queries to execute against the MS Access
database.

1. Ensure the table 'NewTempTable' does not already exist (Jet raises
an ADO error if your try to drop a non-existent table but disregard
the error):

DROP TABLE NewTempTable

2. Create a new table using your Excel data (this uses the whole
worksheet; add a WHERE clause if necessary):

SELECT MyKeyColumn, MyDataColumn INTO NewTempTable FROM [Excel
8.0;Database=C:\Temp\CopyOfMyWorkbook.xls;].[MySheet$]

3. Delete the duplicates from the main table:

DELETE T1.* FROM MyTable T1 INNER JOIN NewTempTable T2 ON
T1.MyKeyColumn = T2.MyKeyColumn

4. Insert the new rows into the main table:

INSERT INTO MyTable (MyKeyColumn, MyDataColumn) SELECT MyKeyColumn,
MyDataColumn FROM NewTempTable

5. Drop the temp table:

DROP TABLE NewTempTable

Other considerations: two Excel workbooks could potentially try to
update the MS Access table simultaneously, therefore you need to
decide whether to get exclusive locks on the rows.
 
Back
Top