Automating a table update?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

curtis.schmitt@gmail.com - 13 Jan 2007 03:39 GMT

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.

Reply to this Message

Steve Schapel - 13 Jan 2007 18:15 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> 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
[quoted text clipped - 4 lines]
> or has any guidance how to do this, I would greatly appreciate the
> help. Thanks Curtis.

Reply to this Message

curtis.schmitt@gmail.com - 19 Jan 2007 02:39 GMT

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

> Curtis,
>
[quoted text clipped - 13 lines]
> > or has any guidance how to do this, I would greatly appreciate the
> > help. Thanks Curtis.

Reply to this Message

Steve Schapel - 21 Jan 2007 08:11 GMT

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"!

Signature

Steve Schapel, Microsoft Access MVP

> Steve,
>
[quoted text clipped - 6 lines]
> the same file name. Please let me know if you have any other
> questions.

Reply to this Message