Do CMD set warnings

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
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Dale FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Seamus2626Author Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Seamus2626Author Commented:
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
Seamus2626Author Commented:
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
Dale FyeCommented:
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
Seamus2626Author Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
0
Dale FyeCommented:
Don't confuse things , Joe

Use the deleteobject method to delete the table.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
?
0
Dale FyeCommented:
@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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I see what's going on.
The OP asked the same question in the other Q ...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Seamus2626Author Commented:
Thanks guys, sorry for the confusion, was getting panicked friday evening.

Thats all good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.