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.

[CODE=vb]Option Compare Database

Private Sub UpdateDatabase_ Click()
DoCmd.SetWarnin gs False

' Selects tbl_01_BKUK_Act ive&Withdrawn and deletes previous download
DoCmd.OpenTable "tbl_01_BKUK_Ac tive&Withdrawn" , acViewNormal, acEdit
DoCmd.RunComman d acCmdSelectAllR ecords
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.Close acTable, "tbl_01_BKUK_Ac tive&Withdrawn" , acSaveYes

' Imports the new data file downloaded from ADP
DoCmd.TransferT ext acImportDelim, "tbl_01_BKUK_Ac tive&Withdrawn_ Import Specification", "tbl_01_BKUK_Ac tive&Withdrawn" , "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \HC_RSCUK_" & Text3 & ".txt", False, ""

' Selects tbl_02_Terminat ions and deletes previous download
DoCmd.OpenTable "tbl_02_Termina tions", acViewNormal, acEdit
DoCmd.RunComman d acCmdSelectAllR ecords
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.Close acTable, "tbl_02_Termina tions", acSaveYes

' Imports the new data file downloaded from ADP
DoCmd.TransferT ext acImportDelim, "tbl_02_Termina tions_Import_Sp ecification", "tbl_02_Termina tions", "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \Terminations_" & Text3 & ".txt", False, ""

' Runs queries to update departments & extra details
DoCmd.OpenQuery "UQry_01_LastDa yOfWork", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_02_Termin ationMonth", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_03_Termin ationQtr", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_04_Termin ationYear", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_05_StartM onth", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_06_StartQ tr", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_07_StartY ear", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_08_Swiss_ Co_OR_UK", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_09_DV P", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_10_Compan y_Ops", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_11_Develo pment", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_12_Financ e", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_13_Franch ise_Operations" , acViewNormal, acEdit
DoCmd.OpenQuery "UQry_14_HR ", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_15_Legal" , acViewNormal, acEdit
DoCmd.OpenQuery "UQry_16_Market ing", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_17_MI S", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_18_Ops_Ex cellence", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_19_Ops_Tr aining", acViewNormal, acEdit
DoCmd.OpenQuery "UQry_20_SQ A", 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
[/CODE]

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.Setwarnin gs False line:
[code=vb]Dim Filename as string
Dim fs As Object
Set fs = CreateObject("S cripting.FileSy stemObject")
Filename = "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \HC_RS CUK_" & Text3 & ".txt"
If Not fs.FileExists(f ilename) Then
MsgBox "File " & Filename " does not exist"
Exit Sub
End If[/code]
You should also replace the DoCmd.TransferT ext line with one that uses the new filename variable:
[code=vb]DoCmd.TransferT ext acImportDelim, "tbl_01_BKUK_Ac tive&Withdrawn_ Import Specification", "tbl_01_BKUK_Ac tive&Withdrawn" , Filename, False, ""[/code]
-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.Setwarnin gs False line:
[code=vb]Dim Filename as string
Dim fs As Object
Set fs = CreateObject("S cripting.FileSy stemObject")
Filename = "R:\HR\HR_Syste m_Reports_Folde r\ADP_Downloads \HC_RS CUK_" & Text3 & ".txt"
If Not fs.FileExists(f ilename) Then
MsgBox "File " & Filename " does not exist"
Exit Sub
End If[/code]
You should also replace the DoCmd.TransferT ext line with one that uses the new filename variable:
[code=vb]DoCmd.TransferT ext acImportDelim, "tbl_01_BKUK_Ac tive&Withdrawn_ Import Specification", "tbl_01_BKUK_Ac tive&Withdrawn" , Filename, False, ""[/code]
-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?