Working on a Cross Comparison Database

[Mrdude1020]

So I'm working on making a cross comparison database with 3 different food vendors we use when ordering or food/ingredients for the restaurant. I am able to get up-to-date prices by exporting all their catalogs through excel then importing that into access. However I've made a button for each of the tables that should automatically update the tables when I export the newest file but I cant seem to get them to update. Also I have three buttons with basically the same code for each, just a different name of the file. The code im using for the command button is:

Dim strFile As String

DoCmd.SetWarnings False

' Set file directory for files to be imported
strPath = "C:Shares\Public\Staff Public Files\Brandon Penland\US Food Product Prices Newest\"
' Tell it to import all Excel files from the file directory
strFile = Dir(strPath & "*.xls*")

' Start loop
Do While strFile <> ""
' Import file
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="US Food Product Prices Newest", FileName:=strPath & strFile, HasFieldNames:=True, Range:="default"
' Loop to next file in directory
strFile = Dir
Loop



MsgBox "All data has been imported.", vbOKOnly
End Sub

Also im trying to have a seach feature that allows for searches for keywords to search across the three tables, but do they all have to be unifrom in the column titles for that to work? And what would be the best way to show the results, a report? Please help!

[June7]

Review this example for looping through files in a folder http://www.allenbrowne.com/ser-59.html. Note the condition to skip . and .. hidden files.

Missing a backslash in the filepath - C:\Shares