VBA Rubberducking

Mat's Mug

00:04

@Duga :'(

RubberDuck

00:42

yesterday, by Duga

RELOAD!

Simon will be back from his uber awesome trip to the Ukraine soon.

Ya know, so long as that whole Russia occupying it thing is still kosher....

WTF was he thinking?!

lol

Mat's Mug

01:06

I missed the 200 mark by 5 points :( ...and got a checkmark today...

I need to post more answers, gotta catch up on simon while he's away!

My next tag badge is a silver vba btw ;)

Oh well, +195 on a day where you pay a 50pts bounty is not too bad after all

Mat's Mug

02:30

1

Q: Late Binding VBIDE.VBE in excel

Is it possible to late bind the VBIDE.VBE object in Excel? For example: Dim VBAEditor As VBIDE.VBE Instead becomes something similar to this (late-binded): Dim VBAEditor As Object: set VBAEditor = CreateObject ("VBIDE.VBE") My goal is to avoid having to manually go in an select the checkbox...

interesting:

7

Q: Excel forms: identify unused code

I am updating an excel app written by someone else (of course :) I found lots of unused Sub CommandButtonXX_Click() subs, and I am not always sure if the button still exists. Is there a way (program, VBE interface, external tool) to do some cleanup while avoiding to delete the still in use code ...

excel excel-vba

Stack Exchange

03:50

0

Q: Speeding up Access query with a calculated field calling a VBA function

At work I have a Microsoft Access database of employees with fields such as employee's manager, employees email, and employee ID. My duties include updating this database and validating entries generated. Biweekly when I run the VBA script to validate records, I am faced with between 25K and 30K ...

vba ms-access

skiwi

09:34

hey

Stack Exchange

10:05

1

Q: Working with group of checkboxes in MS Excel

I have the following checkbox as depicted by the image below: The checkboxes are in 3 groups. Confidentiality, Integrity ad Availability. When the first in is clicked, the Confidentiality turns Red for High, Orange for Medium and Green for Low. This is a lot of work to be done as you need to ...

vba excel form

RubberDuck

12:51

@StackExchange That was fun.

Ohhh there's another.

RubberDuck

13:03

@StackExchange Fastest accept in the west too.

Mat's Mug

13:21

@RubberDuck Well done!

skiwi

Wow @RubberDuck, you were feeling generous today ;)

Mat's Mug

Well that code deserved it! ;)

RubberDuck

Thanks @Mat'sMug.

@skiwi How so? If anything, I'm just tired of my day job here... lol

skiwi

@RubberDuck It's quite a bug answer ;)

2

RubberDuck

Ha!

> It's quite a bug answer.

skiwi

13:33

Well big, close enough

RubberDuck

=)

It was okay code that has the potential to be great code. It deserved it much more than other Qs I've spent more time on.

I kinda hope he shows up here in chat. OP has some potential.

FreeMan

@Mat'sMug Ta ta for now! Ho-ho-ho-hooo!!! You're too young to have kids, but too old to remember your Winnie the Pooh, aren't you.

Mat's Mug

@FreeMan I have 3 yo twins ;)

RubberDuck

> The wonderful thing about Tiggers, is Tiggers are wonderful things.
Their tops are made out of rubber.
Their bottoms are made out of springs.

Mat's Mug

but I didn't grow up with Winnie the Poo ;)

RubberDuck

13:38

Anyone want to guess how Christopher J. McClellan got his first name?

Yeah... seriously. Wish I was kidding.

Mat's Mug

...

is the J. the same as in Omer J. Simpson?

@RubberDuck wait, was that the name of the kid in Winnie?

RubberDuck

Yes it was.

Christopher Robin

Mat's Mug

lol

RubberDuck

And you are all now officially my friends, because only my friends know that.

Mat's Mug

:)

<beer/>

RubberDuck

13:43

Ohhhhhh sooo looking forward to one of those.

Mat's Mug

that post+answer made me realize that we do need to reference and use the host application - we need to locate everything that can have handler procedures on a worksheet. otherwise handler procedures for worksheet controls will show up as never called / false positives. I'm still going to ship it with 1.3, it works well in most cases anyway.

FreeMan

@Mat'sMug And you don't know TTFN??? You or your children (or both) are deprived!!!

Mat's Mug

I know cowa-bunga ;)

RubberDuck

My youngest daughter loves the turtles. =)

FreeMan

@RubberDuck Parenting: You're doin' it right!

RubberDuck

13:58

And the oldest is hooked on anime and Doctor Who.... I'm so ruining these children.

FreeMan

bow ties are cool. So are Fez!

RubberDuck

I'm so sorry. So, so sorry, but they are not.

It's a home divided over who's the better doctor..

Mat's Mug

@RubberDuck eh, that's what parents are for! ;)

(aren't we all messed-up?)

RubberDuck

Word.

Mat's Mug

Excel.

RubberDuck

14:05

Powerpoint

> ewwwwwwwwwwwwwwwww

Mat's Mug

lol

Access

> noooooooooooooooo

FreeMan

And... those last the posts have described my life for the last 6 months...

sigh...

RubberDuck

Access isn't so bad, ya know.... so long as you don't use it as a database...

2

FreeMan

Ain't that the truth!

sᴉɔuɐɹɥԀ

@RubberDuck Access isn't so bad, ya know.... so long as you don't use it ~~as a database~~...

3

Mat's Mug

14:26

burn

Mat's Mug

14:37

FWIW, I've later found a number of issues with this code, notably with scoping. If there's a procedure called DoSomething in 5 different modules, this code assigns all references to DoSomething to whichever module is returned by FirstOrDefault in GetClosestScope. I've modified it quite a bit to address this issue, but the most pressing problem is that this code needs a major refactoring, and some implementation of a ResolveMemberCall method. — Mat's Mug 5 secs ago

FreeMan

@Mat'sMug I'll put essentially that same comment on about 10 posts a day, then get bored of it and leave 'em alone for a few days...

Mat's Mug

IKR! ..but someone has to comment to explain the down votes and close votes to this poor new clueless user no? ;)

I'm starting to seriously consider a strategy pattern for accurately, reliably and most importantly cleanly resolving identifier references.. boy that code is a mess.

RubberDuck

1

Q: VBA - Set Property in Subroutine to Automatically Step Over While Debugging

This question is specifically regarding the property get/let code in a class module but I think that the solution will be applicable to any subroutine. There must be a property that tells vba to step over a function in break mode automatically (even when pressing the step into key/F8). Obviously...

vba udf

Poor guy

FreeMan

@RubberDuck Can this be made so?

RubberDuck

Not unless M$ spends some time upgrading the VBE.

Mat's Mug

14:52

0

A: VBA - Set Property in Subroutine to Automatically Step Over While Debugging

If I understand correctly what you're asking, you need to step over instead of step into. Try Shift+F8 instead of just F8.

Or I don't understand the question

RubberDuck

Okay... so wtf is wrong with my VBE then?

I can't drag the executing line. The step over button does nothing, but Shift + F8 works fine....

FreeMan

Pull over to the side of the road, roll up all the windows, shut off the car, wait 2 minutes, then start it back up and re-open all the windows.

3

Mat's Mug

..

FreeMan

it's M$, what do you expect?

sᴉɔuɐɹɥԀ

@FreeMan LOL

FreeMan

15:01

That's what would happen if M$ made cars...

Mat's Mug

"Subs and functions" are one thing, and no they're not the same in a class module vs. Std. Module - your question is confusing at best. also can you add a screenshot (or link to one) of your VBE options settings? — Mat's Mug 1 min ago

"...exists for subs functions and classes" -- wtf

RubberDuck

> One last note, the reason the debugger doesn't step into the "built in" classes is because that code is not written in VBA. It is hidden behind the COM Interop wall and written in an entirely different language all together.

Mat's Mug

OH!! THAT'S what he meant!! ....what frakkin' dummy would even expect to be able to step into the VBA runtime library anyway???

RubberDuck

That guy

Obviously.

Mat's Mug

that's a lazy ass hand-drawn red circle BTW

RubberDuck

15:15

Those icons are small man!

You can also drag the yellow arrow in the left margin within the procedure you're in, instead of "Set next statement" — Mat's Mug 35 secs ago

The hell I can..

That is the single most frustrating thing I've come across and I'm relatively certain it's not RD causing it.

I know I should be able to though.

Mat's Mug

that question should be titled "VBA Debugging 101"

3

RubberDuck

I'm familiar with the step into, step over and step out functionality of the debugger. If your answer to my question is "no it can't be done" then please state that and I will mark this as the best answer. — Richard Foxton 2 mins ago

Wait..... wtf? That's not what it said in my browswer.

Mat's Mug

Good luck with Rubberduck - look forward to seeing it — brettdj 38 secs ago

FreeMan

@Mat'sMug Oooh... we haz a release date?!?!?!?

Mat's Mug

15:30

I'm trying for Saturday

FreeMan

I'm looking forward to Monday!

said for the first time in the history of teh interwebz

RubberDuck

@Mat'sMug 32x build might be Sunday. I'm getting drunk on Saturday.

Mat's Mug

lol

RubberDuck

You don't want me building an installer three sheets to the wind. =)

Like, sloppy, falling down, my wife is not going to speak to me for a week drunk.

Yeah. Given that, you can't @RichardFoxton. Not unless you want to build your own IDE from scratch. Even the Mug and I aren't that crazy. — RubberDuck 9 secs ago

And some idiot gave me a 0 on my homework because I used two code files and he couldn't get it to compile.....

Jesus wept.....

> PEER 3
Peer's assessment:
Might be a little bit too complicated for the task.

What? Generics are complicated? Huh? LOL

> PEER 1
Code will not run. Has two Module 3 namespaces. Maybe copy/paste errorjQuery172043888242542743683_1429276827268

That ^ pisses me off though

Mat's Mug

16:06

> Even the Mug and I aren't that crazy.

you sure about that one?

;)

sᴉɔuɐɹɥԀ

2

Well that was fun...

Mat's Mug

LOL

FreeMan

oopsie!

RubberDuck

@sᴉɔuɐɹɥԀ OMG! I'd pin it if the image showed up in the sidebar!

sᴉɔuɐɹɥԀ

16:26

Beautiful collage of SNAP error messages isn't it! ;)

FreeMan

Oh SNAP!

skiwi

@sᴉɔuɐɹɥԀ I think you did something wrong!

Stack Exchange

He had a hard time staying awake last night while we were finishing our code… (via @giphy) http://t.co/vyMbuJ2OGR

Tweeted by msdev on April 17, 2015 at 2:00 PM

sᴉɔuɐɹɥԀ

@skiwi Yeah, I tried to actually use the program for something it's supposed to do, apparently that was a mistake.

2

FreeMan

16:43

so.... what is SNAP, exactly? Google spits out lots of options...

sᴉɔuɐɹɥԀ

@FreeMan It's an internal administration program at the company I work at. Good old buggy VB6 that needs to be killed with fire.

FreeMan

ahhh... that asplains a lot.

Mat's Mug

> Michel Bretschneider starred us

> Gowon Patterson starred us

wow I didn't know this post existed!

29

Q: Hidden features of VBA

Which features of the VBA language are either poorly documented, or simply not often used?

vba hidden-features

> VBA itself seems to be a hidden feature. Folks I know who've used Office products for years have no idea it's even a part of the suite.

To any who say that the OO in VBA is not OO, get over it. Interface inheritance is also inheritance - it's just a different kind. — John Saunders Jul 1 '09 at 19:33

> Crash it by adding: enum foo: me=0: end enum to the top of a module containing any other code.

#387 msdn.microsoft.com/en-us/library/aa263421(v=vs.60).aspx

RubberDuck

17:05

@Mat'sMug Really? That's a good one.

@Mat'sMug wtf is that?!

I am so happy people don't know about that.

Mat's Mug

me too!

#388 DefTypes are evil.

> daFreeMan starred us

FreeMan

@Mat'sMug You can make VB act just like FORTRAN???!?!?!!?!?!

God is real, unless declared integer.

2

RubberDuck

in The 2nd Monitor, 2 mins ago, by RubberDuck

The Story of Mel: A Real Programmer

Speaking of FORTRAN.....

skiwi

17:37

@FreeMan public int God, ha!

Where's your power now?

FreeMan

@skiwi That's an ancient joke from when my dad was writing FORTRAN on some mainframe system when I was about 4...

anyone else having issues with @users.noreply.github.com emails?

I'm getting a bounce saying

#< #5.0.0 smtp; 5.1.2 - Bad destination host 'DNS Hard Error looking up users.noreply.github.com (MX): NXDomain' (delivery attempts: 0)>

how do I get the block quote formatting like this: :21157264, and how do I put the user name link in the middle?

@Mat'sMug like this

Mat's Mug

18:10

@skiwi God can't possibly be a public field...

He'd be properly global, static - a singleton

You know, thread safety matters

FreeMan

but if there's an exception, He can handle it.

Mat's Mug

True that

cheezsteak

18:35

> real programmers write mysterious black-magic unportable code that only they can understand.

2

RubberDuck

Damn right they do, but hey do it with style.

cheezsteak

portable code probably didn't exist at that time anyways.

RubberDuck

Well, the story was first told in 1983 and the events seem to pre-date that by quite a bit. Rotary drum memory was a thing in the 50's.

I'm so far down the rabbit hole at this point.

The 500 mile email

@Mat'sMug did you just get a whitelist email form Symatec?

Mat's Mug

18:53

did you??

(no)

sᴉɔuɐɹɥԀ

I just love troubleshooting no-repro website bugs with clients over the phone.

Now when she calls me back I'll have to teach the little accounting lady in their office how to turn on the network profiler in their browser. That's gonna be fun.

RubberDuck

@Mat'sMug Just forwarded.

Perhaps my snark about harming the community did some good? idk...

Mat's Mug

oh, the email explicitly states that we're not allowed to disclose the decision

(I was about to tweet it lol)

or they looked at the repo and saw that we had 1.3 coming up and they said "ok, let's white-list 1.22"

hey @PetLahev

PetLahev

hey @Mat'sMug

Mat's Mug

I borked all the unit tests for the RenamePresenter!

PetLahev

19:00

I saw! :)

thank you :)

Mat's Mug

lol I didn't mean to..

PetLahev

np, I like your comment in one of the commit you sent

Mat's Mug

point is, with the TokenStreamRewriter being passed in, I have no idea how to test it now

PetLahev

something like "I can't look at the code without crying"

Mat's Mug

lol totally.. it's a shitty pile of stinky crap

PetLahev

19:02

welcome in my world

Mat's Mug

needs to be refactored.. badly

I'm thinking of a strategy pattern

PetLahev

wanted to ask you guys

@Mat'sMug one of my favourite

I've been working on quite huge project, something like 200 components

and when I go to VBE, it takes about 1 minute (50 sec to be precise) to refresh all data for ducky

is this normal?

Mat's Mug

I have a pretty large project and noticed that lag too

RubberDuck

200 components? That doesn't sound too ridiculously unheard of considering what's going on under the hood.

Mat's Mug

if you performance-profile it during that lag, you should see that it's the tree walkers and symbol table-loading at work

I want to make it a completely background process eventually

but yeah as of yet, opening a very large project will lag a bit

which sucks

RubberDuck

19:06

And I've hit the jackpot of @ducky ain't getting crud done today.... gull.us/humor

PetLahev

@Mat'sMug ok guys, just wanted to know u r aware about it. Unfortunately I had to unload the addin :(

Mat's Mug

I feel your pain

PetLahev

Anyway I presenting your tool wherever I go. I'm going to use Ducky unit test in that project, it's on the project's wiki, so it's official :)

Mat's Mug

oh nice!

PetLahev

@Mat'sMug I'll check that. Is that something what can be mocked? @RubberDuck Have you had chance to check the PR with the one test? The Cast issue. Isaw you're pretty busy with your class so not rush yourself

RubberDuck

19:15

@PetLahev I did pull in the test, but I've not had a chance to muck with the mocks.

Mat's Mug

@PetLahev probably.. and then all tests should work again, except if there's any involving the rename of a procedure parameter

(I'm only using the rewriter for parameters ... for now)

BTW, #389 Worksheet controls have handlers, too

RubberDuck

> 8am: Your pager goes off and wakes you up. The message says it's the office, and it's a crisis. You roll out of bed moaning.

8:15am: You are now sufficiently awake to phone the office. Your pager has gone off three times already. You get through to the office and the receptionist is frantic. She says nobody in the entire office can print and they have a major proposal that has to be faxed out before 9am and if it isn't the company could lose a million dollars in new business. You try to get her to explain what's wrong, but she's incoherent.

Jolt Cola

> Midnight: Your blood turns to coca-cola.

Mat's Mug

> When we process the parse tree, we also need to grab the names of all Shapes we can find, and store their OnAction value as a reference to the corresponding procedure declaration.

PetLahev

@Mat'sMug OK, I need to make some progress on that project I mentioned so I'll check this later but seeing it as cool and important feature so we should make the tests working again and add others

Mat's Mug

@PetLahev cool. assume a TokenStreamRewriter will end up being used for all rename operations though.

RubberDuck

19:25

So, IT decided to unauthorize all of the Google Apps Scripts on the domain today. Google Apps Script runs the ticket system for my depeartment. I just go this ticket. (My co-worker submitted it)

PetLahev

#332 github.com/retailcoder/Rubberduck/issues/332 I've been thinking about it a bit. What is the plan with it?

FreeMan

Woot! +200 points on SO today. A first!

2

FreeMan

19:57

Yes, I've been busy doing everyone's work but my own today, why do you ask?

Mat's Mug

@PetLahev snippets are on the roadmap, we just don't know which version we want to start implementing them for yet ;)

I guess once we have gathered a certain number of desirable snippet issues, we'll go for it

PetLahev

@Mat'sMug understand, are you planning it something like MS does? msdn.microsoft.com/en-us/library/ms165392.aspx

Mat's Mug

I've been concentrating on inspections and refactorings, and getting the symbol table to support all that

cheezsteak

Moving to a shared workbook with separate tabs for each user as an interim solution until I figure out a solution in Access. Opinions?

Mat's Mug

@PetLahev I'm thinking of an "Insert..." code pane context menu and a page in the config/settings dialog; the design is still pretty vague.

@cheezsteak using Excel as a database? heck, everybody does it...

cheezsteak

20:04

@Mat'sMug That's basically what Excel is; A WYSIWYG mutable database program.

Mat's Mug

that's not what I use it for... and I use it extensively ;)

cheezsteak

What do you use Excel for?

Mat's Mug

analysis! I'm a business analyst, remember ;)

RubberDuck

@cheezsteak Good interim plan.

Mat's Mug

^^ yeah

cheezsteak

20:05

Ahh, charts and math.

RubberDuck

@PetLahev There's really no plan yet. It's just on the roadmap.

cheezsteak

More like Mathematica or Matlab or Maple

RubberDuck

I would like it be kind of like intellisense, probably via a context menu.

Mat's Mug

yeah. and then I have a Planning & Forecasting workbook that would hold all the planning & forecasting data... if I hadn't implemented a bit of VBA code to extract all the data and import it into a SQL Server db..

#ConfusingChat

...

#SilentChat

PetLahev

@RubberDuck sure. no problem

FreeMan

20:09

MakingA#OfChat

Mat's Mug

@PetLahev feel free to implement a prototype, we can pull it into a dev branch ;)

Like, source control has been in its dev branch for ages! </poke>

@RubberDuck I'll FedEx you a beer when that one gets merged ;)

RubberDuck

Yeah... dude.. idk. Ready to lose it if I don't get to sit the f down and finish that feature soon.

Mat's Mug

no worries, it's officially pushed to 2.0 now

PetLahev

@Mat'sMug hehe then you'd see bad code. No really, I can see a huge potential with this feature. All the queries about - progress bar, download data, send email from excel, database connection/pulling => all sorted :)

Mat's Mug

perhaps we could have some kind of a plugin architecture for that one, so that snippets can be easily shared between RD users

PetLahev

20:21

ups pulling =pooling

exactly!

Mat's Mug

I'd need to get some hosting on the website for that though

PetLahev

hey that was my idea :)

FreeMan

@PetLahev oohh.. UPS pooling? that sounds like fun! Just don't jump in for at least 2 hours after eating.

PetLahev

@FreeMan lol

RubberDuck

@Mat'sMug @PetLahev simplest implementation would be to store a list in the config with pointer to a subdirectory containing text files of the actual snippets.

Mat's Mug

20:29

it needs a bit of metadata to name the snippet

RubberDuck

@Mat'sMug Yup. That gets stored in the config.

Sorry. NO idea why I just pinged you...

Mat's Mug

lol

RubberDuck

But, honestly, the only data in the config would be a name of the file and a "friendly" name.

The directory could be a known (hardcoded) subdirectory Like

./Roaming/AppData/Rubberduck/Snippets

FreeMan

Store 'em in an .INI file, just for old time's sake!

RubberDuck

NOooooo. We run an XML config.

FreeMan

20:31

:(

PetLahev

@RubberDuck well you can do a bit more if you'd implement it like MS but I agree that for example for "Progress bar" snippet, which will probably include a form and at least one class, it's not possible. I mean the MS way. At least I don't have idea how

RubberDuck

Then again.... VBA users would probably be more comfortable with an INI.. lol

Mat's Mug

@RubberDuck I like that

I mean, that's probably going to happen, whatever the format is

RubberDuck

Yeah. Portable that way too. Your snippets go with your profile.

FreeMan

Ta! enjoy your weekends

Mat's Mug

20:33

TTFN!

RubberDuck

@PetLahev I'm not sure we can hi-jack everything quite enough to do the replacements just like MS did in VS, but it's just a "rename" refactor away, no?

Mat's Mug

"just" a rename refactor??

RubberDuck

=)

You know what I meant.

Mat's Mug

actually I don't. What's "the MS way"? (I... don't use snippets at all)

I've only ever used ReSharper's "wrap with... try/catch"

@PetLahev oh you mean templates - that's a lot different from snippets!

PetLahev

@Mat'sMug me neither, I meant the way how they create a snippet setting - msdn.microsoft.com/en-us/library/ms165394.aspx

Mat's Mug

20:36

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets
    xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title></Title>
        </Header>
        <Snippet>
            <Code Language="">
                <![CDATA[]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

makes sense

reading...

and pretty damn flexible too

PetLahev

but guys, don't spend too much time now. I just wanted to know what is your thinking about it. It's really a feature for RD 3.0 or so

Mat's Mug

@PetLahev yeah

I really want to get the symbol table as close as possible to 100% of being completely reliable & accurate as soon as possible. Inspections & refactorings build on that.

and then I need to make it as fast as possible so that large projects aren't an incentive to unload the add-in ;)

> 27+51 downloads BTW

RubberDuck

@PetLahev I can't encourage you enough to just start working on it. Create a branch in your fork and go to town. Who cares if the code is bad? My code is bad.

Bad code can always be cleaned, but code that is never written results in vaporware.

Mat's Mug

my code is beyond bad. I have no words for how bad it is.

78% of traffic to rubberduck-vba.com is coming from SO/SE

PetLahev

jey NYMBURK, CZ, it's me!

Mat's Mug

20:44

and 5% from vba4all.com :)

RubberDuck

@Mat'sMug Sure you do. It makes you cry, remember?

Mat's Mug

:'(

cheezsteak

21:03

How the hell do I just type out a query in Access? Without using any Wizard?

Got it

Right Click SQL view

sᴉɔuɐɹɥԀ

@cheezsteak Also, brace yourself ;)

cheezsteak

Or that tiny little button at the bottom that Says SQL

RubberDuck

@cheezsteak don't!

cheezsteak

@sᴉɔuɐɹɥԀ for what? No Syntax Highlighting?

RubberDuck

Staup!

Noooooooo!

cheezsteak

21:05

WHAAAAAT?

sᴉɔuɐɹɥԀ

@cheezsteak Um. It'd be quicker to tell you what is has than what it is lacking ;D

RubberDuck

Just.... don't do it.

Mat's Mug

lol

RubberDuck

Don't try to write SQL for Access.

Use the GUI

Mat's Mug

funniest Alt+Tab in a while

sᴉɔuɐɹɥԀ

21:06

But! If you need any help, I've had the misfortune of having to write straight SQL in Access so I kind of know the quirks

RubberDuck

You'll never hear me advocate a GUI unless we're talking about Access SQL.

It's nothing but pain and misery and every time you open the query in design view it will frak your code up.

Mat's Mug

oh I remember that... ~~good ol' times~~

sᴉɔuɐɹɥԀ

@RubberDuck Sometimes though, some things just can't be done with the GUI

RubberDuck

Not to mention the god awful non-standard syntax.

cheezsteak

@sᴉɔuɐɹɥԀ THAT is upsetting.

RubberDuck

21:08

@sᴉɔuɐɹɥԀ Yeah yeah. Joining on a cast and such.... but then you should ask

> Why am I joining on a cast?

sᴉɔuɐɹɥԀ

Or SWITCH statements

String manipulations, etc.

Mat's Mug

[etc] union [everything-else]

sᴉɔuɐɹɥԀ

Wait... a cast in Access is not the same as a cast in standard SQL... is it? (please say no...)

RubberDuck

Best part, is if you do something the GUI can't handle in straight SQL, it will remove it when you open the designer.

@sᴉɔuɐɹɥԀ [someField]*1

Cast to int....

cheezsteak

So it sounds like any SQL code I write is subject to be edited at the whims of the GUI and the Syntax is so non-standard that it will teach me bad SQL?

Mat's Mug

21:12

omg

RubberDuck

@cheezsteak Yes sir.

cheezsteak

So that SQL feature that I finally found is really just a read only feature?

sᴉɔuɐɹɥԀ

If you don't open it back in the GUI it should be fine. But yeah, it's severely non-standard SQL

RubberDuck

@Mat'sMug Right? Now, I actually have to do that in that Space Auto language I use right now. There are exactly zero cast functions in that language.

@cheezsteak No, but pretend it is.

Anyway. Beer time.

Have a good weekend fellas.

Mat's Mug

Beer O'Clock already?

cheezsteak

21:14

Is Access SQL it's on separate deal or is it MSSQL and that is horrible non-standard?

Mat's Mug

you too @RubberDuck

sᴉɔuɐɹɥԀ

SELECT DISTINCT
  '01-Jan' AS [Month]
, (SELECT SUM(Contract_Count13) - (SELECT SUM(Contract_Count13) FROM Entry_Type_Raw_Data WHERE (Program_Name4 = 'Lennox' OR Program_Name4 = 'LennoxCAN') AND Creation_Date_Month3 = 'January' AND Sale_Type1 = 'Dealer Sale')
    FROM Entry_Type_Raw_data
    WHERE Creation_Date_Month3 = 'January'
    AND Sale_Type1 = 'Dealer Sale'
  ) AS [Portal Sales]
, (SELECT SUM(Contract_Count13) - (SELECT SUM(Contract_Count13) FROM Entry_Type_Raw_Data WHERE (Program_Name4 = 'Lennox' OR Program_Name4 = 'LennoxCAN') AND Creation_Date_Month3 = 'January' AND Sale_Type1 = '…

^^ Access SQL I wrote ;)

RubberDuck

@cheezsteak It's its own deal. It's not TSQL.

Mat's Mug

I thank my life every day for bringing T-SQL to me

sᴉɔuɐɹɥԀ

Had to Copy&Paste and edit that block like 5 more times using UNION to make it do something useful

Mat's Mug

21:16

blessed be the day Access-SQL went bye-bye

cheezsteak

How the hell can I learn SQL if Access won't let me write SQL? This killed my motivation on this project. Will access at least allow me to learn proper database structure?

RubberDuck

Yes. Absolutely.

And you can actually learn a lot about sql from the GUI. You just won't know you are exactly.

cheezsteak

Well at least I learn something.

RubberDuck

Just remember, "Normalize till it hurts. De-Normalize till it works."

3

cheezsteak

Basically DRY?

sᴉɔuɐɹɥԀ

21:32

@cheezsteak Just play around with it. If you want to use raw SQL in Access, you can; and you can learn the principles of SQL that way, if you feel so inclined. It's mostly the syntax that's a bit odd

Mat's Mug

"a bit odd" should be in quotes

sᴉɔuɐɹɥԀ

-- regular SQL
SELECT *
FROM Table1
JOIN Table2
ON Table1.x = Table2.y
WHERE Table1.z LIKE 'Foo%'

-- Access SQL (except this line, because it doesn't support comments
SELECT *
(FROM [Table1]
(JOIN [Table2]
ON [Table1].[x] = [Table2].[y]))
WHERE [Table1].[z] LIKE 'Foo*'

Mat's Mug

there's no comment syntax in Access SQL?

oh wow

Select
   ...
From
   ...
Where
   ....
   And "Comment: FYI, Access doesn't support normal comments!"<>""

1

A: How do you comment an MS-access Query?

I know this question is very old, but I would like to add a few points, strangely omitted: you can right-click the query in the container, and click properties, and fill that with your description. The text you input that way is also accessible in design view, in the Descrption property Each f...

cheezsteak

21:48

So I should perfer many tables with few fields?

sᴉɔuɐɹɥԀ

^Yes

Most of the time, anyways

It's OK to have, for instance, a Customer table that's 50 columns wide or whatever, as long as the only information contained relates strictly to that customer record

cheezsteak

like address phone etc.

sᴉɔuɐɹɥԀ

It really depends on the scope of your database. I suggest to start with Excel and draw out your data fields in a table

@cheezsteak Yeah

cheezsteak

Basically try to make tables look like a dictionary object as much as possible...

sᴉɔuɐɹɥԀ

Um yeah, I think so.

(not that familiar with dictionaries

cheezsteak

21:56

Associative Arrays in PHP

sᴉɔuɐɹɥԀ

^^ Kinda like that

cheezsteak

Map == Dictionary == Associative-Arrays == {Key -> value}

sᴉɔuɐɹɥԀ

Then you can tie those together nicely with other tables like...

RubberDuck

Yes! Yes yes. Only your Value might be a single value, or a collection of properties.

sᴉɔuɐɹɥԀ

RubberDuck

22:01

If you have too many properties, you probably need to "extract" a new table.

sᴉɔuɐɹɥԀ

GTG, back later. Judging for Battle of the Bands tonight :D

RubberDuck

Database normalization

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. Normalization involves refactoring a table into smaller (and less redundant) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys. Edgar F. Codd, the inventor of the relational model (RM...

Cya @Phrancis! Have fun!

Mat's Mug

23:50

<habs>

Transcript for