Prevent table export if table name already exists

[kadoss]

Hi All - I hope someone can help. I found some code via the internet and modified it slightly and need some help with additional modifications. I've tried various internet searches and searching on this forum, but am appearently not searching the correct words/phrases to come up with what I am looking for.

I have 2 databases, db1 is my local database and db2 is my archive database.

db1 has a form with 4 combo boxes and a button with code that takes the info from the combo boxes and uses it to name a table that is exported from db1 into db2. The code then deletes the records from db1. The problem is that if people accidently name the tables the same, it will just copy over the existing archived copy.

So, I need the code to check to see if the table name already exists in db2 (the archive database). If that name already exists - it needs to pop up with a warning and NOT export the table. Below is the current code that I have. Any help would be greatly appreciated.
Sub New_Table()
Dim MBox As String, YBox As String, NBox AsString, IBox As String
On Error GoTo Err_ErrorHandler

strM = Forms("frm_Export").MBox.Value
strY = Forms("frm_Export").YBox.Value
strN = Forms("frm_Export").NBox.Value
strI = Forms("frm_Export").IBox.Value
strT = strM & "_" & strY &"_" & strN & "_" & strI

If IsNull(strM) Then
MsgBox"You must enter data in ALL fields to name the table you arearchiving!", , "Error"
Exit Sub
Else
DoCmd.TransferDatabase acExport, "Microsoft Access", "L:\Path\To\Archived\Database.accdb",acTable, "tbl_MyFindingsTable", "tbl_" & strT


DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [tbl_MyFindingsTable]"
DoCmd.SetWarnings True
MsgBox"The table has been archived."

End If

Exit_ErrorHandler:
strT =vbNullString
Exit Sub

Err_ErrorHandler:
MsgBoxErr.Description, vbExclamation, "Error #" & Err.Number
ResumeExit_ErrorHandler

End Sub

[Bulzie]

How about addi the datetime tag to the end of the file name so they are always unique?

strT = strM & "_" & strY &"_" & strN & "_" & strI & "_" & Format(Now(), "mmddyyyyhhmmss")

[andy49]

Any use to you?

https://access-programmers.co.uk/for...d.php?t=154842




Sent from my iPhone using Tapatalk

[kadoss]

How about addi the datetime tag to the end of the file name so they are always unique?

strT = strM & "_" & strY &"_" & strN & "_" & strI & "_" & Format(Now(), "mmddyyyyhhmmss")

Bulzie - Thanks for the quick response! This would definately keep the original table from being overwritten, and would be able to tell which was saved first. And having the date/time might be helpful. Might see if there are any other helpful suggestions, but this may be the way to go.

[kadoss]

Hey Andy49 - I appreciate the link. It probably would be of use if I was a little bit more code savy than I am.
I may just go with adding the date/time stamp option for simplicity's sake.

[andy49]

Heh always add the easy option.


Sent from my iPhone using Tapatalk