ExcelToAccess
File formats - 2K
References:
(a) Excel Workbook - Access 9.0 Object Library &
DAO 3.6
(b) Access Db - DAO 3.6
Note - All user action is via excel workbook. Access
Db need not be opened directly.
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 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.