1 - This sample demonstrates transfer of data from excel workbook to access db,
accompanied by updating of data in local table of access db.
2 - Both techniques, i.e. Link as well as Import are covered.
3 - Data blocks can be located anywhere on any of the worksheets, so long as it is
ensured that each such block comprises of specified number of columns (as per
global constant) and first row represents the column headings.
4 - For transferring any particular data block to access db, select the worksheet on
which it is located. Shortcuts for execution are:
(a) Via Link to access: Ctrl + k
(b) Via Import into access: Ctrl + t
5 - As soon as any of the above shortcuts is applied, data block on active
worksheet gets identified automatically, accompanied by assignment of dynamic
range name to it (as per specified global constant). In case of (b) above, the cell
denoting heading of first column of data block should be in selected state, before
using the short cut. In case of (a) above, even this is not necessary. First column
heading, wherever it is, gets identified automatically.
6 - Based upon linked or imported data, updating of local table in access db takes
place on the following lines:
(a) If PartNum already exists in the local table, the date & stock get updated as
per fresh data.
(b) If fresh data has records with PartNum values not yet existing in local table,
corresponding fresh records get appended to the local table.
7 - After each execution of data transfer, the access db gets displayed adjacent to
excel workbook. Records freshly appended get highlighted in light blue, while
those freshly updated are shown in dark yellow. Simultaneously, in excel workbook
too, the data rows responsible for causing changes in access local table
(update/append) get correspondingly highlighted.
8 - There is complete flexibility regarding the worksheets. Any worksheet (even a
freshly inserted one) can be used, provided it is the active sheet.
9 - Even the name of workbook or the range name assigned dynamically to the
data block on active sheet, do not matter, so long as it is ensured that the
workbook is housed in the same folder that contains the access db.
10 - It is desirable that the same computer should not have multiple versions of
Excel installed. Moving amongst different versions of excel on same computer in
the same session can lead to unexpected results.
11 - The sample has been tested with Excel 2000 workbook in conjunction with
Access Db in Access 2000 file format in various installations of access, i.e. 2K, XP
and 2003.
Note - All user action is via excel workbook. Access Db need not be opened directly.
References:
(a) Excel Workbook - Access 9.0 Object Library & DAO 3.6
(b) Access Db - DAO 3.6 Download Excel 2K & Access 2K: http://www.rogersaccesslibrary.com/forum/uploads/37/ExcelToAccess.zip - uploads/37/ExcelToAccess.zip
------------- --
Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com
|