The table has two columns data type is nvarchar(25) called import_MRN. The form is called Collect - MRN and has a subform that is bound to the SQL table that is linked to Access DB called MRN. Here is the code on the button that deletes old MRN and adds new ones:
Private Sub btnClear_Click()
Dim LResponse As Integer
Dim msg1 As String
Dim msg2 As String
Dim cdb As DAO.Database
Dim qdef As DAO.QueryDef
msg1 = "You have choosen to continue..."
msg2 = "Exiting... User has canceled."
LResponse = MsgBox("Are you sure you want to clear old MRN's and add new ones?", vbYesNo, "Continue")
If LResponse = vbYes Then
Set cdb = CurrentDb
Set qdef = cdb.CreateQueryDef("")
qdef.Connect = cdb.TableDefs("dbo_Import_MRN").Connect
qdef.SQL = "EXEC dbo.sp_MRN_CleanUp" 'Simple runs a truncate table
qdef.ReturnsRecords = False
Set qdef = Nothing
Set cdb = Nothing
MsgBox ("Old MRN's deleted.")
MsgBox ("Update Complete...!")
The error, I get when it gets to DoCmd.RunCommand acCmdPasteAppend is Run-time error '2046' The command or action 'PasteAppend' isn't available now.