By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Access Run Time Error 3011

P: 34
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
Mar 12 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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
Mar 12 '08 #2

P: 34
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub UpdateDatabase_Click()
  4. DoCmd.SetWarnings False
  5.  
  6. ' Selects tbl_01_BKUK_Active&Withdrawn and deletes previous download
  7. DoCmd.OpenTable "tbl_01_BKUK_Active&Withdrawn", acViewNormal, acEdit
  8. DoCmd.RunCommand acCmdSelectAllRecords
  9. DoCmd.RunCommand acCmdDeleteRecord
  10. DoCmd.Close acTable, "tbl_01_BKUK_Active&Withdrawn", acSaveYes
  11.  
  12. ' Imports the new data file downloaded from ADP
  13. 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, ""
  14.  
  15. ' Selects tbl_02_Terminations and deletes previous download
  16. DoCmd.OpenTable "tbl_02_Terminations", acViewNormal, acEdit
  17. DoCmd.RunCommand acCmdSelectAllRecords
  18. DoCmd.RunCommand acCmdDeleteRecord
  19. DoCmd.Close acTable, "tbl_02_Terminations", acSaveYes
  20.  
  21. ' Imports the new data file downloaded from ADP
  22. DoCmd.TransferText acImportDelim, "tbl_02_Terminations_Import_Specification", "tbl_02_Terminations", "R:\HR\HR_System_Reports_Folder\ADP_Downloads\Terminations_" & Text3 & ".txt", False, ""
  23.  
  24. ' Runs queries to update departments & extra details
  25. DoCmd.OpenQuery "UQry_01_LastDayOfWork", acViewNormal, acEdit
  26. DoCmd.OpenQuery "UQry_02_TerminationMonth", acViewNormal, acEdit
  27. DoCmd.OpenQuery "UQry_03_TerminationQtr", acViewNormal, acEdit
  28. DoCmd.OpenQuery "UQry_04_TerminationYear", acViewNormal, acEdit
  29. DoCmd.OpenQuery "UQry_05_StartMonth", acViewNormal, acEdit
  30. DoCmd.OpenQuery "UQry_06_StartQtr", acViewNormal, acEdit
  31. DoCmd.OpenQuery "UQry_07_StartYear", acViewNormal, acEdit
  32. DoCmd.OpenQuery "UQry_08_Swiss_Co_OR_UK", acViewNormal, acEdit
  33. DoCmd.OpenQuery "UQry_09_DVP", acViewNormal, acEdit
  34. DoCmd.OpenQuery "UQry_10_Company_Ops", acViewNormal, acEdit
  35. DoCmd.OpenQuery "UQry_11_Development", acViewNormal, acEdit
  36. DoCmd.OpenQuery "UQry_12_Finance", acViewNormal, acEdit
  37. DoCmd.OpenQuery "UQry_13_Franchise_Operations", acViewNormal, acEdit
  38. DoCmd.OpenQuery "UQry_14_HR", acViewNormal, acEdit
  39. DoCmd.OpenQuery "UQry_15_Legal", acViewNormal, acEdit
  40. DoCmd.OpenQuery "UQry_16_Marketing", acViewNormal, acEdit
  41. DoCmd.OpenQuery "UQry_17_MIS", acViewNormal, acEdit
  42. DoCmd.OpenQuery "UQry_18_Ops_Excellence", acViewNormal, acEdit
  43. DoCmd.OpenQuery "UQry_19_Ops_Training", acViewNormal, acEdit
  44. DoCmd.OpenQuery "UQry_20_SQA", acViewNormal, acEdit
  45. DoCmd.OpenQuery "UQry_21_Supply_Chain_Director", acViewNormal, acEdit
  46. DoCmd.OpenQuery "UQry_22_Office_Mgt", acViewNormal, acEdit
  47.  
  48. ' Msg to inform you the above actions have been completed
  49. MsgBox ("Database updated with requested information")
  50. End Sub
  51.  
Cheers,

Forrest Gump
Mar 13 '08 #3

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. Dim Filename as string
  2. Dim fs As Object
  3. Set fs = CreateObject("Scripting.FileSystemObject")
  4. Filename = "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_RS CUK_" & Text3 & ".txt"
  5. If Not fs.FileExists(filename) Then
  6.     MsgBox "File " & Filename " does not exist"
  7.     Exit Sub
  8. End If
You should also replace the DoCmd.TransferText line with one that uses the new filename variable:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acImportDelim, "tbl_01_BKUK_Active&Withdrawn_ Import Specification", "tbl_01_BKUK_Active&Withdrawn", Filename, False, ""
-Stewart
Mar 13 '08 #4

P: 34
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:
Expand|Select|Wrap|Line Numbers
  1. Dim Filename as string
  2. Dim fs As Object
  3. Set fs = CreateObject("Scripting.FileSystemObject")
  4. Filename = "R:\HR\HR_System_Reports_Folder\ADP_Downloads\HC_RS CUK_" & Text3 & ".txt"
  5. If Not fs.FileExists(filename) Then
  6.     MsgBox "File " & Filename " does not exist"
  7.     Exit Sub
  8. End If
You should also replace the DoCmd.TransferText line with one that uses the new filename variable:
Expand|Select|Wrap|Line Numbers
  1. 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?
Mar 14 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.