Access Run Time Error 3011

[forrestgump]

In my access database I have created vba which deletes information in a table and then uploads a .txt file based on it's naming convention.

However if you specific an incorrect naming convention by mistake the vba still deletes all the information in the table so the table is blank and nothing is uploaded.

The next time I try and run the code i get run time error 3011 because there is nothing to delete in the table. What is the best why to resolve this issue?

Regards,

Forrest Gump

[Stewart Ross]

In my access database I have created vba which deletes information in a table and then uploads a .txt file based on it's naming convention.

However if you specific an incorrect naming convention by mistake the vba still deletes all the information in the table so the table is blank and nothing is uploaded.

The next time I try and run the code i get run time error 3011 because there is nothing to delete in the table. What is the best why to resolve this issue?

Regards,

Forrest Gump

Hi Forrest. It would seem from what you have mentioned that you need to test to make sure that the specified text file exists and only delete the contents of the table if it does.

It would be of help if you could post your VB code to see how you are handling the deletion and text file import.

-Stewart

[forrestgump]

Hi Forrest. It would seem from what you have mentioned that you need to test to make sure that the specified text file exists and only delete the contents of the table if it does.

It would be of help if you could post your VB code to see how you are handling the deletion and text file import.

-Stewart

You are exactly right in what you say. I don't know the code though. Here is the full code I have at present.

Option Compare Database
 
Private Sub UpdateDatabase_Click()
DoCmd.SetWarnings False
 
' Selects tbl_01_BKUK_Active&Withdrawn and deletes previous download
DoCmd.OpenTable "tbl_01_BKUK_Active&Withdrawn", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acTable, "tbl_01_BKUK_Active&Withdrawn", acSaveYes
 
' Imports the new data file downloaded from ADP
DoCmd.TransferText acImportDelim, "tbl_01_BKUK_Active&Withdrawn_ Import Specification", "tbl_01_BKUK_Active&Withdrawn", "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_RSCUK_" & Text3 & ".txt", False, ""
 
' Selects tbl_02_Terminations and deletes previous download
DoCmd.OpenTable "tbl_02_Terminations", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acTable, "tbl_02_Terminations", acSaveYes
 
' Imports the new data file downloaded from ADP
DoCmd.TransferText acImportDelim, "tbl_02_Terminations_Import_Specification", "tbl_02_Terminations", "R:\HR\HR_System_Reports_Folder\ADP_Downloads\Terminations_" & Text3 & ".txt", False, ""
 
' Runs queries to update departments & extra details
DoCmd.OpenQuery "UQry_01_LastDayOfWork", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_02_TerminationMonth", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_03_TerminationQtr", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_04_TerminationYear", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_05_StartMonth", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_06_StartQtr", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_07_StartYear", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_08_Swiss_Co_OR_UK", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_09_DVP", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_10_Company_Ops", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_11_Development", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_12_Finance", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_13_Franchise_Operations", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_14_HR", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_15_Legal", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_16_Marketing", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_17_MIS", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_18_Ops_Excellence", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_19_Ops_Training", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_20_SQA", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_21_Supply_Chain_Director", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_22_Office_Mgt", acViewNormal, acEdit
 
' Msg to inform you the above actions have been completed
MsgBox ("Database updated with requested information")
End Sub
 

Cheers,

Forrest Gump

[Stewart Ross]

Hi Forrest. That is some set of DoCmds in the code - and no error trapping at all! I have found a simple means to check for the existence of a file, tested it in code, and it works for me in my Access 2003 testbed system. Try this in the top part of your code, before the DoCmd.Setwarnings False line:

Dim Filename as string
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Filename = "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_RS CUK_" & Text3 & ".txt"
If Not fs.FileExists(filename) Then
    MsgBox "File " & Filename " does not exist"
    Exit Sub
End If

You should also replace the DoCmd.TransferText line with one that uses the new filename variable:

DoCmd.TransferText acImportDelim, "tbl_01_BKUK_Active&Withdrawn_ Import Specification", "tbl_01_BKUK_Active&Withdrawn", Filename, False, ""

-Stewart

[forrestgump]

Hi Forrest. That is some set of DoCmds in the code - and no error trapping at all! I have found a simple means to check for the existence of a file, tested it in code, and it works for me in my Access 2003 testbed system. Try this in the top part of your code, before the DoCmd.Setwarnings False line:

Dim Filename as string
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Filename = "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_RS CUK_" & Text3 & ".txt"
If Not fs.FileExists(filename) Then
    MsgBox "File " & Filename " does not exist"
    Exit Sub
End If

You should also replace the DoCmd.TransferText line with one that uses the new filename variable:

DoCmd.TransferText acImportDelim, "tbl_01_BKUK_Active&Withdrawn_ Import Specification", "tbl_01_BKUK_Active&Withdrawn", Filename, False, ""

-Stewart

Fantastic! it works! thanks for your help on this. You can probably tell I am a begineer on this VBA stuff. I wish I could get better at it but there doesn't seem to be much text on the subject can you recommend anything?