Ashamed non vb developer (1 Viewer)

[tinwolf]

I must say when I read stuff about access which is all in vb I get to thinking that I can`t call myself a database guy.

I create a lot of databases for my company which allow people to gain sales history data for customers, enter and view visits to agencies, enter data for phone calls, process product returns, access and display requested data from the main sql server, suffice to say there isn`t a department in the company which doesn`t use one of my databases.

However I have to admit that there is almost no vb code in these databases even though I`ve attended a vb course (I have forgotten all of it as I`ve not used it). It`s all done with queries and macros.

I like to think that these databases work well and fairly quickly. I find it fairly quick to work with queries and macros.

So what do people think is the big advantage of code?
Am I just a lazy chuffer who should go hang my head in shame

 

[CraigDolphin]

For what little it's worth, I'm envious. If you can get away without using code then more power to you, IMHO.

 

[boblarson]

I guess you really have to look at it like this - If you have never experienced the power of VBA then you have little as a reference point. But, I'll tell you this. I went for 4 years, and did well, before going on to learn how to code. I have to admit that, looking back now, if I could do then what I can now my database programs would have been tons better. The reason is because I can do almost anything I want with VBA. I am not constrained by the limits imposed by macros. Macros are not the evil thing that many state, and in fact Access 2007 has leveraged macros in a whole new, and refreshing way. Will I use them, probably not, but they do have more power, and even error handling, so it all depends on the person as to how they want to approach it.

I am constantly learning how much coding I do NOT know how to do. But, when there is something I want to do, there is usually a solution that can be reached via code.

So, you don't need to learn to code if you can do everything you want with macros. But, I'll tell you that you probably aren't aware of what the possibilities are, if you knew it.

 

[Mike375]

When I changed to Access 2003 EditGoTo would no longer work for my letters system of Bookmarks. I had to get someone out from and Access developer company. Microsoft gave me the name of a couple of places.

If I had the VB knowledge of someone like Bob and others I would have saved a bundle of money.

I let the bloke do it whatever way he wanted. Some of the code for some of the Word.docs had a pile of runmacros in it. Real simple ones of gotocontrol/copy for the paste to the bookmarks. What I found interesting was he did not change any macros to code. I mentioned this forum and he simply said......if you were a hobbyist then fine but changing the macros to code is a waste of time since nothing will change when you "click"

But the bottom line is you have to have code for some things. Although after about 12 years I have only needed a couple, apart from Word/Bookmarks and two of those were kindly supplied by dBForums and one from this forum. One was for locating the capital letter for left/right in JohnAndews, one for removing dupicates in memo fields and one for being able to round up or down numbers to the nearest 10, 1000 or whatever.

As a side note, can you have in code the conterpart of StopMacro and where you have a lot macro actions below the StopMacro. In other words can you park another version of the code below the code being used.

 

[Bat17]

As a side note, can you have in code the conterpart of StopMacro and where you have a lot macro actions below the StopMacro. In other words can you park another version of the code below the code being used.

I dont use macros, but I assume the you are looking for "Exit Sub" which will let you jump out of code. Normaly you would use 'IF' type statements to control the flow of the code.

I came from Excel so I was more used to VBA than macros any way, but I did miss being able to record macros like in Excel. You can 'Save As' a macro to create code, this could be a useful way for seeing how things work in code, but be aware that it is not always the best code for doing the job.

HTH

Peter

 

[stopher]

So what do people think is the big advantage of code?

I don’t know what macros are capable of these days as I haven’t used them for a long time. However, here are some things I’ve used VBA for:

• Creating custom functions
• Security
• Changing filters and record sources on the fly thus making reports and forms dynamic
• On the fly formatting of reports e.g. different colours for each section
• Changing the way a form looks based on input i.e. hiding elements of the form
• Manipulating objects e.g. making them locked or changing the parameters
• Performing complex validation
• Create looping tasks e.g. sending email to a list of people in a table
• Creating powerful search tools

By and large my databases will still work without implementing the above. But VBA allows me to add an extra degree of finesse, functionality and security.

Chris

 

[Ron_dK]

So what do people think is the big advantage of code?
Am I just a lazy chuffer who should go hang my head in shame

We sort of discussed this issue :

http://www.access-programmers.co.uk/forums/showthread.php?t=101057

Hth

 

[ahuvas]

Im not a database guy but in the three classes that I took in a database management subject as part of my masters that looked at access the teacher was very against macros (cant even remember why) and I have never touched them since.

 

[Mike375]

Chris

Most of what you have listed can be done with macros. Changing forms for colour, visible, locked, record source etc is all done with set value.

However, if macro use was limited to just using the drop down list on the action line you be extremely limited.

A lot of the actions are really the same. For example

Forms!LetterStoreRecord!Name = Forms![12ProspectT]![CL Surname]
Forms!LetterStoreRecord!NameNumber = Forms![12ProspectT]!NameNumber
Forms!LetterStoreRecord!Done = Now()

DoCmd.Close acForm, "LetterStoreRecord"

The top three are equivalent of SetValue and the only difference is that the two form references are not on the same line but one on top of the other.

If we were splitting hairs then the "close form" on the macro is easier because there is no typing required.

Running/opening queries and forms is good in macros because typos are eliminated

And advantage of code for filters/searches is not being limited to 255 characters. This "where" in a macro is just under the limit and if the field names were longer it would be over. In fact this one had to be split with some of being done in the query

([Da]<(Date()-[Days]) Or [Ringdate1] Is Null) And (IIf([ActivityDate] Is Null,([ActionDate]<Time() And [ActionDateNo]>([TimeExp]-([Hours]*3600))) Or [Actiondate] Is Null,[ActionDate]<Time() And [Activitydate]>Time())) And [S4] Like 10

 

[stopher]

Most of what you have listed can be done with macros. Changing forms for colour, visible, locked, record source etc is all done with set value.

I think "most" is ambitious. You only have to look at the threads on security and form searches to realise you can go beyond the dreams of macros. I've seen some very complex validation problems which ar a challenge even with VBA.

I agree most actions can be performed in macros. But it's not the action that's the problem. It's why you are performing the action. If it's just a simple condition then this is fine. But my conditions often go beyond that with nested IF's and CASE statements and maybe drawing on and comiling other data along the way. I know these can be put in queries but you just hit a point where you know this is not right (Pat Hartmans' comments in Rak's link was spot on imo). That's what happened to me. One day I knew I just couldn't carry on with macros. It was just too much effort getting them to do what I wanted. It's a bit like playing golf with only half the clubs. You can do it. But it aint right.

As for the typing, there is intellisense in VBA and you can force VBA to make you declare all variables so errors are easily tracked. You also get all the debuging tools.

Macros and VBA are just chalk and cheese imo. But I do think there is a place for both of them.

Chris

 

[tinwolf]

Thanks for the response guys.

I think what bob and some others have said is obviously true, I`ve never really seen the power of VBA in use.
The developers course I did in access was all code but I`ve never found myself thinking "I remember doing this sort of thing in code and it was much easier".
Perhaps my databases don`t do anything complex enough that only code could really handle it.
Most of my databases deal with collecting order/invoice data from a sql server using ODBC and then allow users to view the data in various ways e.g. what did this customer buy in this time period by product, product group, value and units compared to the same period last year. And also allow the user to search by various criteria.

Probably the stuff I do is laughably simple for the august presences I`ve seen in this forum which I think explains why I don`t use code cos it doesn`t really do anything I can`t do with queries and macros.

I`ll go sit back in the dark corner now and play with my simple little databases (insert very humble and deflated emoticon here)

 

[Darth Vodka]

my 2p:-

the VBA is much easier to learn in Excel as you can record the macros. you can write a macro and convert it to VBA, but often if does things a funny way (like through the toolbars)

for every new database you write, do a bit more in code, start off with the button wizards and then try to do a button that runs a query

don't be afraid of getting stuck or stealing code, that's what the internet's for

 

[Mike375]

I`ll go sit back in the dark corner now and play with my simple little databases (insert very humble and deflated emoticon here)

You disgusting creature

But there are some things that we macro/query blokes can do that the others can't. They have to stick to the rules and "procedure" overrides "results".....Adam....are you there sideways sorts etc, DSum bullshit

Actually, it is a lot of fun watching the Access purists handling progressive totals (or progressive minuses) and also a sideways sort You get a few, not many, and Adam (ajetrumpet) is an example that can see the fun.....and also take on the challenge

You just about always know you have an Access purist with a problem when the answer is......why would you want to do that

Mike

 

[Rich]

...why would you want to do that

Mike

Because it's the correct and accurate way

 

[Mike375]

A good example of "procedure" vs "results" is Roy-Vidar and his mate Rich.

Send Keys....how do we replace it....Vista causing a problem. Roys-Vidar's code is useless and he knows it...but he and Rich had to hang on to a procedure. In my case, I had to get something that worked......Sure, my fix with with Word/Bookmarks might be crude, but it works.....

If you are an Access hobbyist it does not matter.

Again, I invite Roy-Vidar (and Rich) to get the code going to replace SendKeys. I accept your criticism of my methods.......if yours will work

 

[Mike375]

Because it's the correct and accurate way

No suprise his code does not work.....I make a simple post......and he takes the bait

But, also notice how he is driven by "the procedure"........results do not matter.

 

[Mike375]

Because it's the correct and accurate way

Rich, how do you know. All you did was support Roy-Vidar. You have no code for the problem.

So are you saying that "correct and accurate" that does not work is the preferred choice.

No wonder you are a socialist and anti American. You just hate when "results" is the key issue.

Mike

 

[Darth Vodka]

No wonder you are a socialist and anti American. You just hate when "results" is the key issue.
Mike

I hope your databases are sounder than your reasoning

Or indeed, your grammar

 

[Mike375]

Or indeed, your grammar

Be careful

 

[Rich]

Because it's the correct and accurate way

Rich, how do you know. All you did was support Roy-Vidar. You have no code for the problem.

So are you saying that "correct and accurate" that does not work is the preferred choice.

No wonder you are a socialist and anti American. You just hate when "results" is the key issue.

Mike

I'm afraid you can't repair a bucket full of holes with one bandaid