How do I NOT overwrite files I am creating that have the same filename?

This program is pretty huge, so I wont go into too much detail, but basically the program takes a file, renames it based on a naming convention I have created, and sorts it into the appropriate folder. If the folder doesn't exist, it will be created, etc. Then the files are added to a database where I can search for them and pull them up.

Everything works great, except one thing - sometimes files need to have the same name, and when this happens, it will overwrite the old one and add the entry to the database. So I have to entries in the db (which is fine) but only one file. So if I try to open the first file from my db, the new one opens and the old one doesnt exist because it has been overwritten. Does this make sense?

What I want to be able to do is check if a file exists and if it does, just add a (1) or (2) to the end of it. How would I do this?

Code:

---

FindFileName 'naming convention that creates the filename
    Dim x As String
    Dim y As String
        x = Trim$(strImportPath)
        strFileExt = Right$(x, Len(x) - InStr(x, ".") + 1)
        y = strName & strFileExt
        lblName.Caption = y
If detailError = False Then
'''Move File
    MakeSureDirectoryPathExists strNameFolder
    FileCopy x, y
    Kill strImportPath
   
'''Save to Documents Database
    SaveToDB



Public Sub SaveToDB()
    Set Rs = New ADODB.Recordset
    Rs.Open "tbl_documents", Cn, adOpenKeyset, adLockPessimistic, adCmdTable
     
    With Rs
        .AddNew
        .Fields("Location") = strName & strFileExt
        .Fields("FileType") = strFileExt
        .Fields("Name") = cboName.Text
        .Update
    End With
     
     
    Rs.Close
    Set Rs = Nothing
End Sub

Here is my code for making the folder, copying the file and renaming it:

---

Something like

Code:

---

Private Sub Command1_Click()
If Dir$("c:\MyFile.Text") <> vbNullString Then
  'file exists so create a new one with a 1 appeneded to it
Else
  'file doesn't exist so create as normal
End If
End Sub

---

Well to get you started you can use this to check if a file already exist or not .
You will have to change it to use your names and variables ;)

'assuming already connected and
'recordset already Dimmed and set

Code:

---

        'check record exists
        strSQL = "SELECT Field_Name From tbl_Name"
        strSQL = strSQL & " WHERE Field_Name = " & OriginalFileName
        rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
       
        If Not (rs.EOF And rs.BOF) Then
                'There is record!
                'Your code to add number to end of file name and then save
          Else
                'There is no record!
                'Your code to add file
        End If
       
        rs.Close

---

Also, to get the number of records with the file name use this:

Code:

---

Dim lngNumRecs As Long

strSQL = "Select Count(*) From tbl_Name WHERE Field_Name = '" & YourFileName & "'"

rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly, adCmdText

If Not rs.BOF And Not rs.EOF Then
    lngNumRecs = rs.Fields(0).Value
End IF

---

Remember you will have to change the name of the file being searched for in case they are there and they would already have a number at the end, eg File1 or File 22

I didn't even think to check the database for existing files. haha, that would make sense! thanks!