Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Do CMD set warnings

Posted on 2014-07-18
16
Medium Priority
?
198 Views
Last Modified: 2014-07-21
Hi,

Two questions

(1) how can i turn my warnings back on after code has turned them off
(2) This is my code and it is turning off warnings and not turning them back on, can anyone see why?


Thanks



Private Sub Import_Files_Click()



Dim strSQL As String

strSQL = "INSERT INTO All_Data_Static SELECT [Combined Data].* FROM [Combined Data];"


CurrentDb.Execute strSQL, dbFailOnError

MsgBox ("Files transferred to static tables")


End Sub
0
Comment
Question by:Seamus2626
  • 5
  • 4
  • 4
  • +1
16 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 40204249
You use SetWarnings to toggle that:

DoCmd.SetWarnings True

Setting to True turns them On, setting to False turns them Off:

http://msdn.microsoft.com/en-us/library/office/ff837275(v=office.15).aspx

However, the code you show does not interact with SetWarnings at all, so I'm not sure exactly what you mean.
0
 
LVL 85
ID: 40204250
By the way, when using INSERT INTO you really, really should provide column lists instead of relying on SELECT * ...

For example:

INSERT INTO MyTable(Col1, Col2, Col3) (SELECT Col1, Col2, Col3 FROM MyOtherTable)
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40204287
As Scott mentions, the Execute method does nothing to the SetWarnings.  

The advantage of using the Execute method is you don't have to setwarnings to False to suppress them, and then setwarnings to true to make them visible.  The other big advantage of using the Execute method is that you can add the dbFailOnError as an argument to the method which will cause an error to be raised if the SQL statement fails.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Seamus2626
ID: 40204391
Okay, my messages are being surpressed in the form and my warnings are back on :-)

Quick question Scott, why not use SELECT*

Doesnt it save time? Whats the risk?
0
 
LVL 85
ID: 40204716
The risk is the wrong data being moved into the the columns in the destination table. I realize it's quick way to get the data, but if you were to modify either of those tables down the road - or have to recreate them for some reason - the column order identified by Access may change, and you may end up putting FirstName data in the Address field.

Using SELECT * is the lazy way out - don't get me wrong, I have nothing at all against lazy - but there are times when it's okay to be lazy, and times when it's not. This is one of the "NOT" times.
0
 

Author Comment

by:Seamus2626
ID: 40204746
I went and manually dropped down the fields, this is the SQL view, is this the right way to go? I think i see what youre saying

INSERT INTO All_Data_Static ( [Mastergroup Name], [Mastergroup Flow Class Code], [Mastergroup Sector Name], [Master Sub Sector Name], [Mastergroup Industry Name], [PAO Name], [GAM Name], [GAO Name], [GAP Name], [Site Customer Number], [Site Customer Name], [GBM Business Line Name], [GBM Product Name], [HORIS Product Name], [Site Product Name], [MGP PAO Region], [Booking Region Name], [MGP PAO Location], [Booking Country Name], [Site Name], [Prior YTD Net Interest Income], [Prior YTD FX Income], [Prior YTD Non FX Income], [Prior YTD Operating Income], [Prior YTD NOPAT], [Prior YTD Economic Profit], [Prior YTD Average RWA], [Prior YTD Adjusted RWA], [Prior YTD Average Liability Balance], [Prior YTD RARR], [Net Interest Income], [FX Income], [Non FX Income], [Operating Income], NOPAT, [Economic Profit], [Average RWA], [Adjusted RWA], [Average Liabilty Balance], RARR, [Date], [Industry Sub Sector], [MasterGroup/Booking Country], [PCM Sales Manager], Origin, [Customer Group] )
SELECT [Combined Data].[Mastergroup Name], [Combined Data].[Mastergroup Flow Class Code], [Combined Data].[Mastergroup Sector Name], [Combined Data].[Master Sub Sector Name], [Combined Data].[Mastergroup Industry Name], [Combined Data].[PAO Name], [Combined Data].[GAM Name], [Combined Data].[GAO Name], [Combined Data].[GAP Name], [Combined Data].[Site Customer Number], [Combined Data].[Site Customer Name], [Combined Data].[GBM Business Line Name], [Combined Data].[GBM Product Name], [Combined Data].[HORIS Product Name], [Combined Data].[Site Product Name], [Combined Data].[MGP PAO Region], [Combined Data].[Booking Region Name], [Combined Data].[MGP PAO Location], [Combined Data].[Booking Country Name], [Combined Data].[Site Name], [Combined Data].[Prior YTD Net Interest Income], [Combined Data].[Prior YTD FX Income], [Combined Data].[Prior YTD Non FX Income], [Combined Data].[Prior YTD Operating Income], [Combined Data].[Prior YTD NOPAT], [Combined Data].[Prior YTD Economic Profit], [Combined Data].[Prior YTD Average RWA], [Combined Data].[Prior YTD Adjusted RWA], [Combined Data].[Prior YTD Average Liability Balance], [Combined Data].[Prior YTD RARR], [Combined Data].[Net Interest Income], [Combined Data].[FX Income], [Combined Data].[Non FX Income], [Combined Data].[Operating Income], [Combined Data].NOPAT, [Combined Data].[Economic Profit], [Combined Data].[Average RWA], [Combined Data].[Adjusted RWA], [Combined Data].[Average Liabilty Balance], [Combined Data].RARR, [Combined Data].Date, [Combined Data].[Industry Sub Sector], [Combined Data].[MasterGroup/Booking Country], [Combined Data].[PCM Sales Manager], [Combined Data].Origin, [Combined Data].[Customer Group]
FROM [Combined Data];
0
 

Author Comment

by:Seamus2626
ID: 40204750
Okay, does this mean i must put this into my form, like such?

strSQL = INSERT INTO All_Data_Static ( [Mastergroup Name], [Mastergroup Flow Class Code], [Mastergroup Sector Name], [Master Sub Sector Name], [Mastergroup Industry Name], [PAO Name], [GAM Name], [GAO Name], [GAP Name], [Site Customer Number], [Site Customer Name], [GBM Business Line Name], [GBM Product Name], [HORIS Product Name], [Site Product Name], [MGP PAO Region], [Booking Region Name], [MGP PAO Location], [Booking Country Name], [Site Name], [Prior YTD Net Interest Income], [Prior YTD FX Income], [Prior YTD Non FX Income], [Prior YTD Operating Income], [Prior YTD NOPAT], [Prior YTD Economic Profit], [Prior YTD Average RWA], [Prior YTD Adjusted RWA], [Prior YTD Average Liability Balance], [Prior YTD RARR], [Net Interest Income], [FX Income], [Non FX Income], [Operating Income], NOPAT, [Economic Profit], [Average RWA], [Adjusted RWA], [Average Liabilty Balance], RARR, [Date], [Industry Sub Sector], [MasterGroup/Booking Country], [PCM Sales Manager], Origin, [Customer Group] )
SELECT [Combined Data].[Mastergroup Name], [Combined Data].[Mastergroup Flow Class Code], [Combined Data].[Mastergroup Sector Name], [Combined Data].[Master Sub Sector Name], [Combined Data].[Mastergroup Industry Name], [Combined Data].[PAO Name], [Combined Data].[GAM Name], [Combined Data].[GAO Name], [Combined Data].[GAP Name], [Combined Data].[Site Customer Number], [Combined Data].[Site Customer Name], [Combined Data].[GBM Business Line Name], [Combined Data].[GBM Product Name], [Combined Data].[HORIS Product Name], [Combined Data].[Site Product Name], [Combined Data].[MGP PAO Region], [Combined Data].[Booking Region Name], [Combined Data].[MGP PAO Location], [Combined Data].[Booking Country Name], [Combined Data].[Site Name], [Combined Data].[Prior YTD Net Interest Income], [Combined Data].[Prior YTD FX Income], [Combined Data].[Prior YTD Non FX Income], [Combined Data].[Prior YTD Operating Income], [Combined Data].[Prior YTD NOPAT], [Combined Data].[Prior YTD Economic Profit], [Combined Data].[Prior YTD Average RWA], [Combined Data].[Prior YTD Adjusted RWA], [Combined Data].[Prior YTD Average Liability Balance], [Combined Data].[Prior YTD RARR], [Combined Data].[Net Interest Income], [Combined Data].[FX Income], [Combined Data].[Non FX Income], [Combined Data].[Operating Income], [Combined Data].NOPAT, [Combined Data].[Economic Profit], [Combined Data].[Average RWA], [Combined Data].[Adjusted RWA], [Combined Data].[Average Liabilty Balance], [Combined Data].RARR, [Combined Data].Date, [Combined Data].[Industry Sub Sector], [Combined Data].[MasterGroup/Booking Country], [Combined Data].[PCM Sales Manager], [Combined Data].Origin, [Combined Data].[Customer Group]
FROM [Combined Data];
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40204786
Rather than put all of that code in a string, I would simply save that as a stored query, and call the query.

currentdb.execute "savedQueryName", dbfailonerror
0
 

Author Comment

by:Seamus2626
ID: 40204854
Thanks fyed,

I went with

Dim strSQL As String


CurrentDb.Execute "Append_LinkedTable_To_StaticTable", dbFailOnError
CurrentDb.Execute "PCM SM Monthly Uplift", dbFailOnError

MsgBox ("Files transferred to static tables")



I got the error message "Table Monthly" already exists

I want this table to get overwritten on each run, how can insx access to do this?

Many thanks
0
 
LVL 75
ID: 40205046
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40205076
Don't confuse things , Joe

Use the deleteobject method to delete the table.
0
 
LVL 75
ID: 40205099
?
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 40205193
@Joe,

I was sitting at a traffic light and thought I'd screw with you on my iPhone.  I did not have time to take a look at the other link, or figure out which was the earliest version.

@Seamus,

As I stated above, you can use:

Docmd.DeleteObject acTable, "yourTableName"

to delete the table, but you will either need to make sure the table exists or add some error handling to that in case the table doesn't already exist.  I generally use a subroutine. for this:
Public Sub DropTable(TableName As String)

    On Error Resume Next
    DoCmd.DeleteObject acTable, TableName

End Sub

Open in new window

And then would use:
CurrentDb.Execute "Append_LinkedTable_To_StaticTable", dbFailOnError
droptable "Table Monthly"
CurrentDb.Execute "PCM SM Monthly Uplift", dbFailOnError

Open in new window

or something like that.  Are you deleting the older records from table "All_Data_Static " before you append those records described above.
0
 
LVL 75
ID: 40205238
I see what's going on.
The OP asked the same question in the other Q ...
0
 
LVL 85
ID: 40207202
The OP asked the same question in the other Q ...
http://www.experts-exchange.com/Database/MS_Access/Q_28476244.html

Seamus2626: Please don't waste our time by asking the same question twice. Miriam and Joe answered your question in the other one, so there was no point in continuing it here. We don't mind helping, but we don't like it when our efforts go unappreciated.
0
 

Author Closing Comment

by:Seamus2626
ID: 40208413
Thanks guys, sorry for the confusion, was getting panicked friday evening.

Thats all good
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question