Access Run Time Error 3011 | Member | | Join Date: Sep 2007
Posts: 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
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Access Run Time Error 3011
Quote:
Originally Posted by 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 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
| | Member | | Join Date: Sep 2007
Posts: 34
| | | re: Access Run Time Error 3011
Quote:
Originally Posted by Stewart Ross Inverness 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
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Access Run Time Error 3011
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
| | Member | | Join Date: Sep 2007
Posts: 34
| | | re: Access Run Time Error 3011
Quote:
Originally Posted by Stewart Ross Inverness 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?
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,467 network members.
|
