Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - ExcelToAccess.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

ExcelToAccess.mdb

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: ExcelToAccess.mdb
    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: uploads/37/ExcelToAccess.zip

--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links


Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down