Automating a table update?

[curtis.schmitt]

I am trying to develop a macro that runs when the database starts up.
The goal of this macro is to go out and search a specified server
location and compare the files in that location to the tables in the
database. If it finds that there is a new file relative to the tables
in the database, I would like for the database to automatically pull
this into a table in the database. If anyone has ever done this before
or has any guidance how to do this, I would greatly appreciate the
help. Thanks Curtis.

 

[Steve Schapel]

Curtis,

In this case, a macro is not a good choice. I would recommend using a
VBA procedure for this purpose. The specifics of the code would depend
on what "file relative to the tables" means, and what type of file it is.

 

[curtis.schmitt]

Steve,

I have a set of tables in the database that all have a standard name
for the first half of the table name and then the second half is a
date. I then have a server location that holds a set of excel
spreadsheets with the exact same names. What i would like to do is
have the database compare the filenames to the table names. If there
is a new file name, have it imported into the database as a table with
the same file name. Please let me know if you have any other
questions.

Thanks
Curtis

 

[Steve Schapel]

Curtis,

I think the skeleton structure of your code will be like this:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strFile As String
Dim strTable as String
Dim TableExists As Boolean
set dbs = DBEngine(0)(0)
strFile = Dir("C:\YourFolder\*.xls")
Do While Len(strFile) > 0
TableExists = False
strTable = Left(strFile, Len(strFile) - 4)
For Each tdf in dbs.TableDefs
If tdf.Name = strTable Then
TableExists = True
Exit For
End If
Next tdf
If TableExists Then
' do nothing
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
strTable, strFile
End If
Loop
Set dbs = Nothing

Caution: untested "air code"!