Print Page | Close Window

ExcelToAccess.mdb

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=121
Printed Date: 17 Oct 2017 at 10:56am


Topic: ExcelToAccess.mdb
Posted By: A.D. Tejpal
Subject: ExcelToAccess.mdb
Date Posted: 09 Jul 2008 at 10:54pm
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



Print Page | Close Window