For some reason when l have unchecked (i.e not ticked) the
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Removal of confirmation message (2003)
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Removal of confirmation message (2003)
- This topic has 21 replies, 5 voices, and was last updated 18 years, 6 months ago.
Viewing 0 reply threadsAuthorReplies-
WSjohnhutchison
AskWoody Lounger -
WSjj1972
AskWoody Lounger -
WSjohnhutchison
AskWoody LoungerSeptember 5, 2006 at 7:02 am #1027338I have just tested this and see what the problem is.
You are running a make table query, which wants to recreate a table that already exists.
The only way I could get rid of the message was to explicitly delete the table myself before running the query.
DoCmd.DeleteObject acTable, strtablename
DoCmd.OpenQuery “strQueryName”, acViewNormal -
WSjj1972
AskWoody LoungerSeptember 5, 2006 at 7:33 am #1027341I have modified you coding with the querties and tables l am using, see below:
table is: tbl AVTS_final_output
query is: qry create tbl AVTS final outputDoCmd.DeleteObject acTable strtbl AVTS_final_output DoCmd.OpenQuery “str qry create tbl AVTS final output”, acviewNormal
I now have a few questions:
1. Will the above line of coding work, (i.e is it valid?)
2. Is this coding called from a macro , if so how do l put it in the macro?
Or do l call it from a command button, if l call it from a command button where does it go in the coding? -
WSjohnhutchison
AskWoody LoungerSeptember 5, 2006 at 7:51 am #1027343[indent]
DoCmd.DeleteObject acTable strtbl AVTS_final_output DoCmd.OpenQuery “str qry create tbl AVTS final output”, acviewNormal
[/indent]
This needs to be two separate lines
For the names of the two things referred to (table and query) you either assign those names to variables, or include the name directly in the commnad with ” ” marks around them. So I suggest:DoCmd.DeleteObject acTable “tbl AVTS_final_output”
DoCmd.OpenQuery “qry create tbl AVTS final output”, acviewNormalThis looks OK to me.
To use this code, create a command button. (if the command button wizard starts to run click cancel.)
Display the properties sheet and give the button a caption (the word you see on the button) and find the Click Event.On the properties sheet click the three dots that appear when you click your mouse in the On Click line.
this will take you to the VBA Editor.Put the after the the line that starts
Private Sub….
put it hereEnd Sub
-
WSjj1972
AskWoody Lounger -
WSDouglas Martin
AskWoody Lounger -
WSjj1972
AskWoody Lounger -
WSpatt
AskWoody Lounger -
WSjj1972
AskWoody Lounger -
WSpatt
AskWoody Lounger -
WSjohnhutchison
AskWoody LoungerSeptember 7, 2006 at 9:24 pm #1027658You can convert the existing macro to VBA , then paste the new code in before the existing two lines.
.At the database window, go to Macros, select your macro then go to Tools..Macros..convert Macros to VB
When it is finished you will find a mOdule called “converted Macro..”Copy this code from it:
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJRennDesktopAccess stuffAVTS.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry remove duplicates tbl AVTS”
DoCmd.OpenQuery “qry create tbl AVTS no duplicates”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry create tbl AVTS no duplicates”and paste it into your existing code where you want it. I thinjk it goes before the existing lines, but I am not sure.
-
WSjj1972
AskWoody LoungerSeptember 8, 2006 at 12:36 pm #1027731The code appears to be working fine , but for some reason the message is still appearing which l want to remove by using this code, see attachment.
It therefore appears there is something wrong with the coding, any help would be appreciated.Private Sub Command40_Click()
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJBloggsDesktopAccess stufftestdata.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry remove duplicates tbl AVTS”
DoCmd.OpenQuery “qry create tbl AVTS_data_no_duplicates”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry create tbl AVTS_data_no_duplicates”
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
End Sub -
WSpatt
AskWoody LoungerSeptember 8, 2006 at 8:45 pm #1027844Enclose the code you have like:
DoCmd.SetWarnings FalseDoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry remove duplicates tbl AVTS”
DoCmd.OpenQuery “qry create tbl AVTS_data_no_duplicates”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry create tbl AVTS_data_no_duplicates”
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormalDoCmd.SetWarnings True
-
WSjohnhutchison
AskWoody LoungerSeptember 8, 2006 at 9:50 pm #1027848I did not really read the code I gave you before, but I now see that you are creating the new table twice, by running this line two times:
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
The second time we have a line to delete the table first. The first time you run it you don’t have this line in front of it.
Also you don’t need to close action queries. When you open them they just run. So I think you can simplify this to:
Private Sub Command40_Click()
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJBloggsDesktopAccess stufftestdata.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
End Sub -
WSjj1972
AskWoody LoungerSeptember 18, 2006 at 8:59 am #1029024I still appear to be having problems with the coding.
Result 1 is the output from running the query for the first time. (Is it possible to close the datasheet view in VBA?) and return to the main menu automatically?
Result 2 is the output from running the query for the second time.
I still am getting an error when l try and run the coding for a seconf time
I fully understand why the error is being caused as there is no table, but l need to run the create tbl query in order to create it first!
Is there any way to resolve this issue?See attachments for results.
-
WSjohnhutchison
AskWoody LoungerSeptember 18, 2006 at 9:28 am #1029028I am not sure that I understand.
If the table does not already exist when you try to delete it you will get an error.
You could just put in: “On Error Resume Next ” as a line before the delete table line.
Or you create a proper error handler, and use
if err. number = 7874 then
Resume Next
else
continue with normal error handling.If your queries are action queries, then they should not open in datasheet view. what is causing the datasheet view to open?
-
WSjj1972
AskWoody LoungerSeptember 18, 2006 at 12:25 pm #1029058 -
WScharlotte
AskWoody LoungerSeptember 18, 2006 at 8:44 pm #1029151The help files that come with Access are full of examples that you should study. It is unreasonable to expect others to write each line of code for you, particularly when we are all volunteers. We’re all glad to help, but you have to take the initiative yourself to learn the product. If you search this forum, you’ll find lots of recommendations on books and other sites that can help you learn the basics of Access. You can make good use of those resources and of all the examples of code already in the Lounge database.
-
WSjohnhutchison
AskWoody LoungerSeptember 18, 2006 at 9:35 pm #1029158You just write in the words I gave you.
Private Sub Command40_Click()
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJBloggsDesktopAccess stufftestdata.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
On Error Resume Next
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
End Sub -
WSjj1972
AskWoody Lounger
-
-
-
-
Viewing 0 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Windows 11 Insider Preview build 26200.5518 released to DEV
by
joep517
5 hours, 13 minutes ago -
Windows 11 Insider Preview build 26120.3671 (24H2) released to BETA
by
joep517
5 hours, 15 minutes ago -
Forcing(or trying to) save Local Documents to OneDrive
by
PateWilliam
14 hours, 7 minutes ago -
Hotpatch for Windows client now available (Enterprise)
by
Alex5723
2 hours, 3 minutes ago -
MS-DEFCON 2: Seven months and counting
by
Susan Bradley
1 hour, 12 minutes ago -
My 3 monitors go black & then the Taskbar is moved to center monitor
by
saturn2233
23 hours, 4 minutes ago -
Apple backports fixes
by
Susan Bradley
5 hours, 37 minutes ago -
Win 11 24H2 will not install
by
Michael1950
1 minute ago -
Advice to convert MBR to GPT and install Windows 11 Pro on unsupported PC
by
Andy M
1 hour, 38 minutes ago -
Photos from iPhone to Win 10 duplicating/reformatting to .mov
by
J9438
10 hours, 27 minutes ago -
Thunderbird in trouble. Here comes Thundermail
by
Alex5723
1 day, 1 hour ago -
Get back ” Open With” in context menus
by
CWBillow
1 day, 13 hours ago -
Many AMD Ryzen 9800X3D on ASRock have died
by
Alex5723
5 hours, 56 minutes ago -
simple general stupid question
by
WSaltamirano
1 day, 11 hours ago -
April 2025 Office non-Security updates
by
PKCano
2 days, 4 hours ago -
Microsoft wants to hear from you
by
Will Fastie
1 day, 4 hours ago -
Windows 11 Insider Preview Build 22635.5160 (23H2) released to BETA
by
joep517
2 days, 8 hours ago -
Europe Seeks Alternatives to U.S. Cloud Providers
by
Alex5723
2 days, 13 hours ago -
Test post
by
Susan Bradley
2 days, 16 hours ago -
Used Systems to delete Temp files Gone WRONG what does this mean?
by
Deo
2 days, 17 hours ago -
SSD shuts down on its own
by
CWBillow
2 days, 9 hours ago -
OneDrive File Sharing Changes
by
David Clark
3 days, 1 hour ago -
OneDrive File Sharing Changes
by
David Clark
3 days, 3 hours ago -
Win 10 Pro 22H2 to Win 11 Pro 23H2 Conversion Guide
by
doneager
2 days, 3 hours ago -
Today is world backup day
by
Alex5723
2 days, 19 hours ago -
Windows .exe on Mint
by
Slowpoke47
5 hours, 55 minutes ago -
Reviewing your licensing options
by
Susan Bradley
13 hours, 52 minutes ago -
Apple has been analyzing your photos since September 2024
by
B. Livingston
3 hours, 43 minutes ago -
What Windows 11 24H2 offers beyond bugs
by
Lance Whitney
1 day, 20 hours ago -
Making sense of Settings in Windows 11
by
Simon Bisson
1 day, 21 hours ago
Recent blog posts
- MS-DEFCON 2: Seven months and counting
- Apple backports fixes
- April 2025 Office non-Security updates
- Microsoft wants to hear from you
- Reviewing your licensing options
- Apple has been analyzing your photos since September 2024
- What Windows 11 24H2 offers beyond bugs
- Making sense of Settings in Windows 11
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.