Archives

SA! Why’d it have to be SA?

Admin Note: Please review the comments section for corrections for this blog post.

Indiana Jones

“Snakes! Why’d it have to be snakes?”- Indiana Jones

The Made2Manage ERP system accesses its database with one login. I suspect the reason they do so is because it makes programming easier. So, while all of your users log into M2M with the user name assigned by the System Administrator, every database command is sent to SQL via the SA (System Administrator) single login.

In my opinion, this has several drawbacks.

  • Tracking database changes by user is difficult to impossible. M2M provides ECM auditing, but this has issues as well. I’ll follow up with a post on that at a later date.
  • By default, SA has all rights to all databases on the entire server. Using this is inherently insecure.
  • Changing the SA password is a bit more complicated in this situation and you should do this periodically.

As a user, you don’t have a choice in this, but many administrators I’ve spoken with perpetuate the problem by using the SA login themselves. For example, I recently had a company send me an Excel spreadsheet with an ODBC connection which pulled data from their M2M Sales tables. They wanted my help adding a link to another table in the SQL query. However, when the sent it to me they used the SA login and the password was plainly visible for anyone to see. Not only can the user see any data in the tables that they want, but they could easily write to the database, truncate or drop tables, etc. You could argue that I would need access to their network to harm them, but any one of their own employees could. A few years ago, I warned another Admin against this practice and he told me “it’s alright, none of my users would know how to do that.” To me, that’s as unacceptable as having a pet cobra because it’s gentle and rarely bites.

Therefore, if you use an outside reporting vehicle such as Crystal Reports, ODBC Spreadsheets, or SQL Reporting Services, you should create an account with read only rights. If you’d like, you can create report logins with read access for specific tables and a login for managers with access to all tables.

Do you really want to risk someone getting access to your payroll tables?

This makes me see red.

Red Box Error

If you haven’t seen this error, then you haven’t been working with Made2Manage long enough. In case anyone reading this hasn’t seen it, the error indicates that the expected printer name wasn’t found. The most common cause is when a user saves their printer of choice in the printer setup of a Made2Manage report. This would be convenient if it wasn’t a global setting thereafter for every user who runs the report. Any user who doesn’t have “Daves Printer” installed on his machine gets this error, which is fairly useless because it tells the user basically nothing. However, to Made2Manage’s credit, the answer is found in their knowledge base.

Can’t these preferences be kept per user, or per machine, to avoid these hassles?

Typically, people save their printer preferences inside a report because they always print a particular report on that printer. Perhaps the printer is their local printer (which often causes 1957) or a specialized printer such as dot matrix. You can get almost the same functionality by always printing to the “Default” printer.

To quickly change your default printer, try the following:

  • Click Start, then Printers and Faxes (in Windows XP) to show your current printers.
  • Right click and drag create a shortcut to your printers on the desktop.
  • Right click on the desktop shortcuts, and choose “Set as Default.”

Putting them on the desktop gives you quicker access to them while running reports, and allows you more flexibility without running into the annoying red box error.

What we got here is… failure to communicate.

Cool Hand Luke

“What we got here is… failure to communicate.”- Cool Hand Luke

In a previous post, I described my basic process for creating customizations. For many programmers, writing code is much easier than communicating with users, so I’d like to expound upon that process.

Essentially, the problem is this. The user should have a good idea of what their problem is, but they often have no idea how to fix it. They don’t understand what is possible so they don’t really know what to ask you to do. On the other hand, you as the programmer obviously know what you can do, but you don’t do their job and you don’t really understand their problems. If you are not diligent, you can miss vital points in this process and you will waste time in the long run.

Yesterday, I attended a meeting with Made2Manage users and the management at my current employer over a relatively simple customization. They want to be able to track when certain events occur in the process of a specific type of order. As they explained their problem, my mind was racing ahead weighing my alternatives for solving it. I was so busy thinking of what could be done that I wasn’t really hearing what they wanted. While my mind was planning a set of tables with required fields in an external database and thinking about the .Net front end I would write, they became frustrated because they actually wanted something very simple. What they actually wanted was a FastForms customization to add an extra tab to a form and to tie those fields to the master table. In my mind, I was envisioning building Halo, and what they really wanted was Pong.

As I left the meeting, I realized that I had forgotten the first step in my process. Listen to the user and repeat what they say back to them in your own words to make sure you understand them. Never assume that you know what the user wants. Listening is not easy, it is a skill to be learned. An example, of this method of parroting can be found in the attached video which shows one of MAD Tv’s best skits.

An Excerpt

Candidate One: What people need to understand is that the rules we live by during peacetime no longer apply. America is at war.
Candidate Two: No. No. What folks have to comprehend is that the laws we abide by are no longer applicable. America is in a non-peacetime period.
Candidate One: No. No. The government must be allowed to bend the laws in order to protect it’s people.
Candidate Two: I disagree, the administration should be allowed to take liberties with the rules to defend it’s citizens.
Candidate One: You don’t know what you’re talking about.
Candidate Two: And you sir, are unaware of the words that are coming out of your mouth….

Yes, this video is a parody of political debates and double talk, but it illustrates the process of parroting very well.

After your user feels that they have thoroughly described their problem and you have confirmed that you understood them, you can begin to plan your customization. Assuming you have the skills, you should offer the user several customization options that will address their problems. Explains the pros and cons of each and let them choose (with management approval of course) which customization option to pursue. If they explain the problem and you tell them how you’re going to solve it, they will feel as if they have no input over the process and chances are greater that they will be disappointed with the final product.

In my opinion, the key to being a good programmer is not that you’re fluent in the myriad of programming languages available, but rather how fluently you communicate with your users.

First learn stand, then learn fly. Nature rule, Daniel-san, not mine. (VBA)

Miyagi

Typically, “Hello World” is the first bit of programming we all do. It is the simplest, and most basic way to interact with the computer, essentially Marco Polo for adults.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

For the first VBA snippet I’m going to post, we’ll do something slightly more involved. Let’s suppose that your superior wants you to verify that the “Ref Name” text box on the Sales Order (SO) screen is filled out. They intend to use it to document a project name for each sales order and they don’t want the users to have the option of leaving it blank.

As I mentioned in my last post, I approach this with small steps and achieve success on each step before continuing. So, begin by opening the SO screen, right click on the mnemonic, choose components, and BEFORESAVE to get to the VBA environment.

The first small step is essentially the same as “Hello World.”

Public Function BEFORESAVE() As Boolean
 If Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value) = "" Then
    MsgBox ("Please specify a project name.")
 End If
 BEFORESAVE = True
End Function

You may ask, “How do I know the name of the text box?” The simplest way is to click on it and hit Ctrl-Shift-R. This will copy the “path” of the control to the clipboard. Incidentally, you have to add the Trim function because the field can contain blank spaces and that isn’t the same thing as “” to a computer. This code simply notifies the user that they have made a mistake, but doesn’t prevent them from doing so. To prevent them from continuing we have to stop the BEFORESAVE event. Also, it would be nice that when we did so, the original screen would come back up with the text box focused so the user could immediately fix their mistake.

Public Function BEFORESAVE() As Boolean
 If Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value) = "" Then
    MsgBox ("Please specify a project name.")
    BEFORESAVE = False
    ThisForm.m2mpageframe1.page1.txtfordername.SetFocus
    Exit Function
 End If
 BEFORESAVE = True
End Function

That bit of code will force the user to enter information into the text box or it will not let them save. Yes, I know this code isn’t exactly Halo, but this is our first project after all.

What if management only wants numbers entered into this field? Well, you could use the following.

Public Function BEFORESAVE() As Boolean
 If Not IsNumeric(Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value)) Then
    MsgBox ("The Ref Name field can only contain numbers.")
    BEFORESAVE = False
    ThisForm.m2mpageframe1.page1.txtfordername.SetFocus
    Exit Function
 End If
 BEFORESAVE = True
End Function

As you can see, the code is not difficult to read and understand. In a previous post, I mentioned that one of the negatives of using VBA in Made2Manage is that it doesn’t handle error checking very well. That isn’t entirely accurate. Most of the code I’ve seen written by others does not include error handling code. If you want your code to show any errors it encounters, modify your code as below.

Public Function BEFORESAVE() As Boolean
On Error GoTo errCode
 If Not IsNumeric(Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value)) Then
    MsgBox ("The Ref Name field can only contain numbers.")
    BEFORESAVE = False
    ThisForm.m2mpageframe1.page1.txtfordername.SetFocus
    Exit Function
 End If
 BEFORESAVE = True
errCode:
    MsgBox "ERROR: " & Err.Number & " " & Err.Description
End Function

As I mentioned above, I'll be including VBA, SQL, and VFP code which you are free to use and modify. Feel free to comment if there are questions or suggestions for improvement.

VBA Tips and Why Dalton Should be a Beta Tester

Now that I’m sure you’re all fired up about learning VBA the next question has to be….

How do I learn VBA?

I’m glad you asked. Have you taken class “M2M Foundations and VBAe” or watched the pre-recorded version? If not, stop reading now and go do that first. After you’ve done that the best way to learn to code is to code, so start a simple customization project. As your skills increase, take on projects that get progressively more complex.

The Process

If your project is something that will actually be used in production, the first thing you need to do is thoroughly interview the people requesting the customization. Ask probing questions about the project and be as specific as possible. The more thorough you are during this phase, the fewer revisions you’ll have to do later.

Next, break your customization up into small, basic steps and build on little successes. Do not begin a VBA project for Made2Manage by writing 20-50 lines of code. Write just enough code to prove the basic concept. Test it to make sure it works. After that piece works, add the next layer of complexity on top of it. If you code this way, you will only need to debug one concept or procedure at a time. Continue this basic process until your code does what you think it should.

At this point, I will often show the prototype to one of the users to make sure I understood their needs and that this basic design will fulfill them. Make any necessary changes to the basic product before continuing.

Testing

Road House

“Never underestimate your opponent. Expect the unexpected.” – Road House

I cannot emphasize this enough, test everything you write. Test it thoroughly yourself, have others test it in a practice environment, and monitor it for quite some time in production. I subscribe to the philosophy that programmers are typically the worst software testers. Programmers do not think like regular users and are likely to miss a whole slew of “creative” ways users will respond to our programs. Therefore, when you are testing your own work, you have to be paranoid and envision your user as someone who deliberately tries to break your program. For example, lets say you create a text box which the user will use to enter only numeric data. You must address the possibility that the user will leave it blank or accidentally enter letters and incorrect characters in it.

That’s my basic process for creating VBA customizations. In a previous article, I listed some of the advantages of using VBA, so lets discuss some of the disadvantages.

Disadvantages of using VBA

First, you can’t accomplish everything in the VBA environment. Made2Manage’s own development team has indicated this to me several times over the years. Some of the customizations they do require modifications to the source code, which you and I can’t access.

Second, implementation of the VBA can be very inconsistent. Those who are already accomplished programmers with Visual Basic with other programs will realize that some Visual Basic commands simply will not work in the M2M VBA environment. Also, some of the events like BEFORESAVE behave inconsistently on different Made2Manage forms. I’ll point out specific examples in a future post.

Third, VBA must be installed on every workstation that needs to run the customizations. While the new VBA code can be retrieved from the server each time a user logs in, to install the product you must literally touch each machine. In addition, you may need to update the VBA program file each time you migrate your M2M Version. This can be very time consuming if you support 50 or more users.

Fourth, VBA for Made2Manage tends to hide mistakes in your code. The program will attempt to go past code with syntax and other errors in an attempt to hide your mistakes from the user. This may sound like a great feature, but let me assure you that it is in fact one of VBA’s biggest problems. Since VBA masks many errors, they can be exceedingly difficult to find and correct.

Finally, you have to be very careful when performing customizations as you could possibly damage your data. Obviously, Made2Manage does not support customizations that they haven’t written, and it will cost you dearly if you need to hire them to fix your database. Until you are very confident of your skills in VBA and SQL, do not write directly to your database. Restrict yourself to screen and pre-save data validation and leave database access to Made2Manage itself.

In my next article I will demonstrate a relatively simple VBA customization and how I step through it.

Code you write is twice as sweet as code purchased.

Color Of Money

“Money won is twice as sweet as money earned.” – Paul Newman

At this point you may be asking yourself, “David, why should I bother learning all of this stuff? I don’t have the slightest idea how to write VBA (Visual Basic for Applications) code.”

Why Learn VBA?

There are many reasons to learn VBA.

First, if you are a geek like me, you actually enjoy learning and using code. I first learned the term Homo Logicus while reading Jeff Atwood’s excellent blog Coding Horror. I’ll paraphrase and describe it as someone with an insatiable need to understand how things work. Sometimes I can’t believe people pay me to work on puzzles all day long.

Second, VBA is ubiquitous and applies to many functions of an administrator’s job. Microsoft Office is a completely different world when you incorporate VBA programming. You’d be amazed at what you can do with it in Excel.

Third, unlike many other add on products for Made2Manage, VBA is absolutely free. You can install it from your Made2Manage install CD and assuming you’re on support you can download the latest version from M2M Expert .

Finally, let’s assume that you’re not one of the tribe of Homo Logicus and you don’t find coding fun. You should still learn to do your own customizations or hire someone on staff to do so. The reason is accountability. Many years ago, before I had VBA experience, my employer required a customization. It should have been fairly simple, but rather than working through the problem myself, I deferred to an “expert.” I want to make it clear that this person was not a Made2Manage employee, but they were a certified solutions provider. Essentially, the customization was supposed to update all line items of the sales order with the correct ship to address and ship date at a single click. Anyway, when we received our VBA customization, it worked but it took 30 seconds for a process that I felt should have taken less than 5. I reviewed the code and found it riddled with problems. After being shown his mistakes, the contractor fixed them and sent us another bill for the corrections. Even after these corrections we were left with code we couldn’t use and were given a bill twice the size of the original quote.

Moral of the story, if you’re a Made2Manage Administrator take the online classes, post in the forums, ask for sample code, and view later posts to this blog for help.

Paying for code is not nearly as satisfying as creating it yourself and seeing it work. Also, if you created the code yourself and a problem occurs in testing or even in the production environment, you are in a much better position to fix it if you wrote it yourself.

What if I don’t have the resources to hire a Homo Logicus?

Then if you must customize Made2Manage you will have to contract with someone to do it. I’ll give some pointers as to how to pick someone, how to draft a statement of work document, and proper testing procedures in a future post.

Also, I will provide the code I wrote to accomplish the customization I listed above as well as explanations for how the VBA works in a future post as well.

Are you willing to wear the white belt?

Kano

As I mentioned in my first post, I am an Aikido Instructor and I am fond of sharing notable quotes with my students. One of my favorites is the story attributed to Jigoro Kano , the founder of Judo. As it was told to me, on his death bed Kano, the founder and therefore best Judo practitioner of his time, called together his top students and asked to be buried in his white belt. Initially it was assumed that this was his last act of humility, but later scholars came to see it for what it really was.

Kano was dying and knew that his next adventure was entirely new to him as it is to all of us. He was prepared wearing the white belt of the beginner.

As I related this story to my class, one of the students asked me why I was not living up to this ideal. I was in the same position with the same company for 10 years. I had mastered that position and was stagnating due to lack of challenges. Through the assistance of some Made2Manage friends I located another employer near Dallas with an entirely new set of challenges and I put on my white belt. This blog itself is another opportunity to wear the white belt.

Anyway, my point is that all of us should be willing to risk, to try new things, to tackle new challenges especially if there is a good chance we will fail. If you haven’t tried VBA (Visual Basic for Applications) before and you want to modify the way Made2Manage works, give it a try. If you’ve never created and changed Made2Manage reports because it’s confusing, ask for help and then try.

Are you going to stagnate and do business as usual, or are you willing to wear the white belt?

What can Made2Mentor do for You?

PASS Summit 2012 Smaller 2

Who are you?

I am currently an Independent Consultant, and I specialize in Data Warehousing, Dimensional Modeling, SQL Server Integration Services (SSIS), Azure Data Factory, Business Intelligence Markup Language (Biml), and technical training. I’ve been working with SQL Server for well over 10 years gaining a mastery of the full Microsoft Business Intelligence Stack. This, bolstered by my teaching experience and Bachelors Degree in Education, is why I enjoy sharing my passion for Data Warehousing so much.

I’m highly active in the PASS Community on all levels as I frequently present at SQL Saturdays and SQL Server User Group Meetings. I’m very active as a PASS volunteer as well.

Why do you blog?

As I mentioned earlier, I have a passion for Data Warehousing and Analytics. However, I enjoy connecting with people even more so. I’d like to invite you to read my posts, watch my videos, and learn with me. Note that I said “With” because I love learning and I probably learn more from the blogging process than my readers.

I also use this blog to document the process of my work.  I find that if I don’t write notes about what I’m doing, then I have to re-invent the wheel every time a similar challenge presents itself in the future.

What can I do for you?

I solve problems. If you have some, I offer a wide array consulting services with an emphasis in the following:

  • Data Warehouse Design/Dimensional Modeling
  • SQL Server Integration Services
  • Biml Scripting
  • SQL Server Development
  • SQL Server Administration and Disaster Recovery

If you need someone with experience to help you learn any of the above skills, I’d be glad to help. I also have an extensive professional network which includes the very best practitioners in every field of data science, so I can always find the help you need.

I also enjoy connecting with people and introducing them to the PASS Community.

How can I contact you?

Feel free to contact me through any one of the following: