X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Make-table Queries : Bad ?, Access 2013    
post Dec 19 2017, 07:19 PM

Posts: 5,326
Joined: 2-November 04
From: Downey, CA

Jerry Seinfeld here: What's the DEAL with Make-Table queries ?

... huh ? I've been using Access for decades - far from an expert - FAR from an expert, but very experienced, and I just did some research, and what I found was all this hating on Make-Table queries - one guy said: "You should never use make-table queries..."

Back-story: I have a procedure I run using Access where I run saved imports, a make-table query, etc. etc. - It's been running like a finely-tuned Swiss watch since I streamlined it - had some HUGE help from someone on UA who wrote a killer function for me that is the bread-and-butter of the whole thing.

So I've been struggling to get it to run for some reason - troubleshooting, troubleshooting, and MORE troubleshooting... after hours of this, I honed in on the problem : It was my make-table query - I rewrote it as a SELECT, and I guess now I'll use that as a source for an APPEND query.

Has anybody ever heard of hating on Make-Table queries ? What's the DEAL ?


Go to the top of the page
post Dec 19 2017, 07:25 PM

UtterAccess VIP
Posts: 6,328
Joined: 30-June 11

FWIW, you can put me in the hating column. I have never seen a reason to create a temp table in such a manner. This leads to bloating. Even if I do need temp tables, then I use a secondary db for this and blow it away at my db's startup. In this manner, I never dirty my main FE or BE with such working tables.

At the same time that I say that, if it works for you... who am I to tell you how to organize yourself! Sometimes the solution is more important than the approach used! (and yes, purests will be very mad with me for making such a statement)

You may also like to review: http://thedbguy.blogspot.ca/2016/02/should...pend-query.html
Go to the top of the page
post Dec 19 2017, 08:44 PM

UA Admin
Posts: 34,299
Joined: 20-June 02
From: Newcastle, WA

In a word: BLOAT

On the other hand, if you know how to deal with it, meh.
Go to the top of the page
post Dec 19 2017, 11:42 PM

Posts: 5,326
Joined: 2-November 04
From: Downey, CA

Daniel and George: Thank YOU !!!! Wow, this is something I never thought of - I've long been amazed at the relatively small amount of space access uses - but bloat is Bloat
(like George says)

- it's like someone who packs on the pounds, and loses it all in terms of the numbers on the scale, but there's all that 'baggage' you can't get rid of - that just accumulates and is there forever... and gets worse, and worse.. and more gunk - MS Access is odd like that - very quirky - great conceptually, but odd stuff happens.

So I guess the "genius" idea would be to recreate everything from scratch - well, not necessarily everything, but Job 1 is a fresh db, and more importantly, a new table with the same layout as my table that was the result of the make-table query... I can't believe I never knew all this ... Funny thing is, I used to break my arm patting myself on the back over my batch files that made daily copies of my db's, and yes, that was a great idea as far as not losing historical data, but I'm just copying bloat - one thing I didn't mention is that I was surprised that when I ran my routine on copies, it didn't work....

Thanks again! I love UA! uarulez2.gif

This post has been edited by bakersburg9: Dec 19 2017, 11:47 PM
Go to the top of the page
post Dec 20 2017, 03:50 AM

Posts: 4,023
Joined: 19-October 10

I'm also in the camp of hater.
I can say there are one off times where it is useful when first making a table but thats a very narrow scope.

I say if you need a temp table, which I sometimes find a cause to, then keep the empty table somewhere and populate it and empty as appropriate and manage the bloat as described above.

Shame your research didnt highlight this, sloppy articles maybe.
Go to the top of the page
post Dec 20 2017, 06:27 AM

Posts: 1,669
Joined: 5-February 06
From: Ohio, USA

My two cents...

I use the 'make table' query for one thing... setting up my 'temp table'. It's a quick way to get all the field names and sizes correct, then, when complete, I can modify the temp table if need be, and then change the 'make table' into an 'append query'.

Basically it's more of a developing tool than something used all the time. I do something similar to Daniel's post... I have a BE database that contains my temp tables, and is copied to the same folder as the FE whenever someone opens the FE, so they get a clean empty copy every time.
Go to the top of the page
post Dec 20 2017, 04:21 PM

UtterAccess Editor
Posts: 9,949
Joined: 7-December 09
From: Staten Island, NY, USA

>> My two cents... <<

I'll raise ya two!

Make-tables are great to come up with a schema without having to type it all out smile.gif That's about it though (and naturally, in development only: I'm definitely in the same bloat-is-a-major-issue-thus-never-in-production camp)
Go to the top of the page
John Vinson
post Dec 21 2017, 01:45 AM

UtterAccess VIP
Posts: 4,277
Joined: 6-January 07
From: Parma, Idaho, US

I don't even much like MakeTable queries in development; they're very limited. You can't define field sizes for Text fields, formats for anything, default values, primary keys, relationships - you have to go back afterward and set all of these. DDL queries like CREATE TABLE foo (FooID Long, FooBar Text(25), ... ) are accepted but rarely seen in an Access context; in SQL or Oracle shops they're standard practice.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th January 2019 - 07:22 PM