How to automate linking dbase file?

[Sming]

Hi,

I have dbase IV files that I link to ACCESS 2000 to create
reports. The problem is new files are created yearly
because of the contained data.

e.g. ???3999.dbf ---> 2003 year
???4999.dbf ---> 2004 year.
999 ---> site number.

So each year I have to link files thousand times for
different files for each site. U have about 25 new files
for 25 sites. Plus, I have setup different MDBs for
different import/export, and reports purpose so some files
repeatedly linked in most of the MDBs.

I would have greatly appreciated if anyone can shed some
lights into automating this process.

Thanks,
Sming

 

[Joe Fallon]

The general idea would be to write code to list all files in a folder and
then link to them if they are .dbf files that match your criteria.

I would build a form to capture all relevant criteria (folder name, xxx.dbf,
etc.)
Then click a button to loop through the folder and link the tables.

==============================================
How to Add Directory File Names to an Access Table:

Create a table named tblDirectory with 2 fields:
FileName (Text 250)
FileDate (Date/Time)

Call the code below by pressing Ctrl-G to open the debug window and type:
GetFiles("c:\windows\")

Paste this code into a regular module:

Sub GetFiles(strPath As String)
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
Set rs = Nothing
MsgBox ("Directory list is complete.")
End Sub