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
4 8205
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
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
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: - 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.TransferT ext 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
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: - 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.TransferT ext 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?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: dog |
last post by:
I've seen plenty of articles on this topic but none of them have been
able to solve my problem.
I am working with an Access 97 database on an NT4.0 machine, which has
many Access reports.
I want my users to be able to select a report, click on a command
button on a form, which will then automatically create the report as a
pdf file and save it to the user's machine.
I am using Adobe Acrobat (5.0 I think) and have Adobe Distiller as a
|
by: Polly |
last post by:
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.
The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0
|
by: Grasshopper |
last post by:
Hi,
I am automating Access reports to PDF using PDF Writer 6.0. I've
created a DTS package to run the reports and schedule a job to run this
DTS package. If I PC Anywhere into the server on where the job is
running, the job runs sucessfully, PDF files got generated, everything
is good. If I scheduled the job to run at the time that I am not
logged into the server, Access is not able to print to the printer.
The error is pretty...
|
by: JoeBobHankey |
last post by:
Background:
- I'm running MSDE 2000 (not client tools, stored procedure capability,
etc). This may change, but not in the first part of development.
- My Access file is an Access 2002 project (.adp project client
connecting directly to MSDE SQL database - no .mdb involved or local
file tables beyond the .dbfs to be imported).
- Using ODBC to connect to .dbf data sources without a problem (ODBC is
|
by: chippy |
last post by:
Hi,
I've a VB script that creates a Access object in a word doc.
Here is the full script.
It works for all but the Export. Which always fails with a 3011 error.
If I do the same in Access as a straight Macro or script it works.
Add it as an object and it won't work.
HELP.
| |
by: enough2Bdangerous |
last post by:
Access database (file format 2002-2003) generates reports with docmd.openreport but returns 3011 runtime error related to the printer. Switching Windows default printer is a work around but need to find out the cause. Help Desk updated printer driver on server and no runtime error for two months but we are receiving it again on three computers.
I think this might be specific to the actual mdb file and what occurs with references, dll's,...
|
by: enough2Bdangerous |
last post by:
access runtime error 3011 on docmd.openreport
--------------------------------------------------------------------------------
Access database (file format 2002-2003) generates reports with docmd.openreport but returns 3011 runtime error related to the printer. Switching Windows default printer is a work around but need to find out the cause. Help Desk updated printer driver on server and no runtime error for two months but we are receiving...
|
by: karrans |
last post by:
Hi,
I'm trying to debug some code where I'm trying to retrieve particular ranges in an excel sheet into an Access table, depending on the user selection of the range name in a combo box with a list of range names.
I have already named the ranges I need in the target Excel sheet as follows :
range1 (where range1 =$A$1:$E$10
range2 = $H$1:$L$10 and so on
|
by: Dirk.Emmermacher |
last post by:
Hello list.
I' ve got a problem with mde files from Office XP.
We have following situation: We are running a W2K Terminal server with
an Office XP.
There is a Access application running (mde file) who connects to
sql2000 database.
Each User has his own directory with a own copy of all necessary
files. Instead of reports the application use word for sereial
letters. The program should create a ~01tmp.tab file (word format) as
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |