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].

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.