How to programmatically update Access from an Excel Spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone, please excuse the newbie question. I need to know the basics of
how to programmatically update and Access database from an Excel spread
sheet. I'm thinking of having a "watch directory" and running some automatic
cron job to update the Access database from an Excel spread sheet. I'm brand
new to this, but not databases or programming. I'm just not sure where to get
started. Any general pointers to articles, or technologies (VBA?) to use
would be much appreciated!

Thanks!

Brian Menke
 
Brian,

If the Excel file will always have the same name, but is periodically
updated, then one way to do it would be to:

1. Link the spreadsheet in Access
2. Use an append query (saved or in VBA) to append the data in the
spreadsheet into the target table
3. Use another table (single date/time field) to keep track of imports:
every time one takes place, the Excel file's timestamp is logged in the
table
4. The append query can be fired manually, on databse open, through a
form's timer event, or even by a macro through a scheduled job
overnight; each time, some code checks the Excel file's timestamp, and
continues only if it is found to be newer than the last import.

The above approach involves some risk, in that if someone opens and
saves the Excel file without actually renewing the data, the timestamp
changes and the same data will be imported again. A wise precaution
would be to have the code delete or rename the Excel file immediately
after an import. If you do that, you could skip the import log, if not
required for other reasons.

HTH,
Nikos
 
Hi Brian,

Since you talk confidently about watch directories and cron jobs, I
assume that you aren't deterred by scripting.

In general it's easier to do this without involving Access. Instead,
just use an OLE-aware scripting language and use the DAO or ADODB
library to execute a SQL query.

Here's a skeleton VBScript procedure:


Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")

strSQL = "INSERT INTO MyTable " _
& "SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=D:\Fdr\File.xls;].[Sheet1$]"
& ";"

oDB.Execute strSQL, 128 'dbFailOnError

oDB.Close
'End

If no header row, use HDR=No; individual fields can then be accessed as
F1, F2 etc. For a named range, pass the name (without the $). To specify
a range, use [Sheet1$A1:E99].
 
Back
Top