Searching for a value in Access Table VBA

Question

I have written code that imports excel files into a access table. As each file is imported the file name is recorded and saved to a separate table named 'FilesDownloaded'.

I would like to add vba code that prior to importing the file it will check to see if the name of the file (myfile) is already saved on the 'FilesDownloaded' table. This will prevent the same file from being imported twice.

Code:

Function Impo_allExcel()
Dim myfile
Dim mypath
Dim que As Byte
Dim rs As DAO.Recordset


que = MsgBox("This proces will import all excel items with the .xls in the folder C:\MasterCard. Please make sure that only the files you want imported are located in this folder. Do you wish to proceed?", vbYesNo + vbQuestion)
If que = 7 Then
    Exit Function
Else
    'do nothing and proceed with code
End If

DoCmd.SetWarnings (False)

DoCmd.RunSQL "DELETE * FROM tblMaster_Import;"


MsgBox "Please WAIT while we process this request"



mypath = "C:\Master\"
ChDir (mypath)
myfile = Dir(mypath & "*.xls")



Do While myfile <> ""
  If myfile Like "*.xls" Then
     'this will import ALL the excel files
     '(one at a time, but automatically) in this folder.
     ' Make sure that's what you want.
    'DoCmd.TransferSpreadsheet acImport, 8, "tblMasterCard_Import", mypath & myfile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblMaster_Import", mypath & myfile, 1

    Set rs = CurrentDb.OpenRecordset("FilesDownloaded")
    rs.AddNew
    rs.Fields("Filename").Value = myfile
    rs.Update
    rs.Close
    Set rs = Nothing

  End If
  myfile = Dir()
Loop




'append data to tblAll (risk of duplicates at this point)
DoCmd.RunSQL "INSERT INTO tblAll SELECT tblMaster_Import.* FROM tblMaster_Import;"

DoCmd.OpenQuery "qryUpdateDateField", acViewNormal
''this code will apend to an existing table and runs the risk of doubling data.
DoCmd.SetWarnings (True)

MsgBox "Your upload is complete"

End Function

Does the file you are importing have an ID? Perhaps you can set a primary key on the table to avoid duplicates? — Ryan Wildry, Jan 20 '17 at 14:44

score 0 · Answer 1 · answered Jan 20 '17 at 16:16

0

A possible solution is to make a query, which gives you the result of lines in the table FilesDownloaded, with condition that the name is equal to your file. Something like this:

countString = "SELECT COUNT(*) FROM [FilesDownloaded] WHERE [NAMEOFCOL] = " & myFile

Then run the query, and if the result is more than 1, you obviously have it.

answered Jan 20 '17 at 16:16

Vityata

42,633
8
55
100

I entered code as : Do While myfile <> "" countString = "Select Count(*) From [FilesDownloaded] Where [Filename] = myfile" If myfile Like "*.xls" And countString = 0 Then Im getting a mismatch error message. Help? – Mwes Jan 20 '17 at 16:46
You should run the query and get the result. Take a look http://stackoverflow.com/questions/23992226/how-to-save-the-result-of-a-sql-query-into-a-variable-in-vba and https://msdn.microsoft.com/en-us/library/office/ff194626.aspx – Vityata Jan 20 '17 at 17:13

Searching for a value in Access Table VBA

1 Answers1