MySQL – Yeah, I was wrong.

Years ago, I asked one of M2M’s senior technicians why they didn’t simply use a free database system such as MySQL. The reason being that Microsoft’s SQL licensing fees prohibit many of Made2Manage’s customers from migrating. Therefore I reasoned that if M2M had chosen MySQL instead, then by this time nearly everyone would be on a newer version and the support costs would be considerably less. Also, nearly any system must be less troublesome than Visual FoxPro.

The idea sounded plausible to me, and nobody at M2M could give me a reason why they chose MSSQL instead. Umm… yeah, I was wrong.

I came across this article regarding the latest and greatest release of MySQL.

If you take the time to read that article you will be amazed that they released a database platform in GA status which is littered with critical, crashing bugs.

Don’t expect that all critical bugs that you may have encountered in 5.0 to be fixed in 5.1. Even if we have fixed a big majority of the bugs from 5.0 some really critical ones still haven’t been addressed.
If you plan to use any of the new features of MySQL 5.1, regard these as if they would be of beta quality. Test any usage of these features extensively for in close-to-live scenarios before putting them onto a production server.

We still have 20 known and tagged crashing and wrong result bugs in 5.1 35 more if we add the known crashing bugs from 5.0 that are likely to also be present in 5.1.
# We still have more than 180 serious bugs (P2) in 5.1.

While I am often hard on the folks at M2M, they did at least call this one correctly. In terms of database systems, you obviously get what you pay for.

I feel like such a SAP…

As I’ve mentioned before, I really like Crystal Reports, but their support issues continue to be a problem since they were purchased by SAP.

I recently wiped my computer at work and set about re-installing all of the software. In doing so I lost access to the SAP support website. Unfortunately, there isn’t a phone number for support anymore, and I believe my sales representative has left the company. Therefore, I was reduced to calling random sales people until I found someone who could provide my user number again. SAP doesn’t use your e-mail address or an intelligent user name, but rather a number that looks like S68700812.

Anyway, after all of this, I try to log in and this is what I get:

SAP Error

This is a multi-billion dollar technical company and their error message appears to have 3 different fonts and color schemes. I just love how SAP is asking me, “What happened?”.

As I mentioned before, Crystal used to have the best technical support I had ever seen. I guess my question for them is “What happened?”

New Year’s Resolutions

Calvin Hobbes

Calvin and Hobbes

As this is primarily a professional blog, I will not blather on about resolutions to eat less and exercise more in the new year, though I certainly intend to do those things.

This article is about the professional resolutions for improvement that I intend to make in the new year and I will tag a couple other people to do the same.

Without further ado, here are my New Year’s resolutions:

  1. I will continue to pursue SQL mastery, and find more ways to utilize that knowledge in my current position.
  2. I’ve recently started writing for another online SQL site which I will name after my first article has been “published.”
  3. I will study SQL Reporting Services to compliment my skills with Crystal Reports.
  4. I will pursue some kind of real time dashboarding solution for my current employer and gain skills in that area.
  5. Lastly, I purchased Rosetta Stone and intend to learn Japanese. I don’t know if it will impact my career, but I’ve always wanted to learn it. Domo arigato, Mr. Roboto
  6. I am going to tag two SQL Server experts who I greatly respect, and hope that they will continue the chain.

    Brent Ozar
    John Massie

    Do any of my readers have any New Year’s Resolutions to share?

Changing the Ship To Address and Ship Dates via VBA.

Over the years, one of the issues I’ve repeatedly encountered is the wasted time in changing Ship To Addresses and Ship Dates for multiple line item orders. My last employer made a product commonly sold with new building construction projects. Therefore, they often did not have a Ship To Address because the building was still under construction and may not be able to accept deliveries.

When you have a multiple line item order, you must change every line item manually to match. When dealing with 50 line item orders, as my last employer regularly had, someone has to spend 15 minutes changing each line individually. The code provided does that automatically. Now would be a good time for the standard disclaimer.

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.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

First, you must initialize the form and create the control in the INIT Event of the SO Form.


Right Click on SO and choose components.


Choose the INIT Event

The code below creates a new connection string to the database if M2M has dropped it for some reason, and it draws a button on the form.

Public Function INIT() As Boolean

    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    With ThisForm.m2mpageframe1.page1
    .AddObject "cmdRollDate", "M2MButton"
    .cmdRollDate.Top = .txtFduedate.Top + 20
    .cmdRollDate.Left = 370
    .cmdRollDate.Width = 100
    .cmdRollDate.Caption = "Roll Date & Addr"
    .cmdRollDate.Visible = True
    End With
    INIT = True
End Function

What follows is the rest of the code necessary for this project. I’ll briefly explain it and if anyone has any questions feel free to ask in the comments section.

First, you must assign the code to an event, which in this case is the click event of your button. Please remember that when changing the button attributes you use something like M2mpageframe1.Page1.cmdRollDate.blahblah, however when creating the function for the event you replace those periods with underscores as seen in the code below. The next line is a simple error handler which will display any errors in a message box. Following this are the object declarations. A message box pops up verifying that the user really wants to change the shipping information because it’s entirely possible that the user clicked the button accidentally.

Next, variables are assigned values derived from the form. The code checks to make sure that there is a valid sales order number and due date before continuing. A database connection and a recordset object are then created. The recordset is set to use the database connection and the two update statements are performed. One updates the SOITEM table with the Due Date from the first tab of the Sales Order screen, and the second updates the Ship To Addresses in the SORELS to match the Sales Order default as well.

Afterward, the database connection is closed, and both the recordset and connection are released. The user is notified via message box that the process was successful and the screen is refreshed to show the changes.

Public Function M2mpageframe1_Page1_cmdRollDate_CLICK() As Boolean
    On Error GoTo errCode

    Dim Value As Single
    Dim DueDate As String
    Dim ShipAddress As String
    Dim SoNo As String
    Dim SoRev As String
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SelectStr As String

    If MsgBox("Roll Default Due Date & Addr to all items on this order?", vbYesNo) <> vbYes Then Exit Function
    DueDate = ThisForm.m2mpageframe1.page1.txtFduedate.Value
    ShipAddress = ThisForm.m2mpageframe1.page2.txtfshptoaddr.Value
    SoNo = ThisForm.txtFsono.Text
    SoRev = ThisForm.txtFsorev.Text

    If Trim(SoNo) = "" Then Exit Function
    If Trim(DueDate) <> "" Then

Set CN = New ADODB.Connection
        CN.Open connstr
        Set RS = New ADODB.Recordset
        Set RS.ActiveConnection = CN

        RS.Open ("UPDATE SoItem Set fDueDate = '" & DueDate & "' WHERE soitem.fsono = '" & _
                        SoNo & "'")

        RS.Open ("UPDATE SoRels Set fDueDate = '" & DueDate & "', fshptoaddr = '" & ShipAddress & "' WHERE sorels.fsono = '" & _
                        SoNo & "'")
        Set RS = Nothing
        Set CN = Nothing
    End If
    M2mpageframe1_Page1_cmdRollDate_CLICK = True
    MsgBox "Date Rolled"
    Exit Function
    MsgBox "ERROR: " & Err.Number & " " & Err.Description
End Function

This is a fairly simple example of M2M Database manipulation via VBA for you to use and customize to fit your needs. Please remember that whenever you are writing to your M2M Database directly, you risk data corruption should you make a mistake and this could cost you a great deal to have corrected.

I’m interested to read people’s opinions of my code and any improvement suggestions offered.

Accessing the M2M Database Using VBA.

A couple of weeks ago, someone sent me some VBA code which was giving them fits. I often receive requests like this and am glad to help when I can. I came across this bit of code:

Private Function fConn() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
    sCnStr ="uid=sa;pwd=HisPassword;" _
    & "driver={SQL Server};" _
    & "server=HisSQLServer;" _
    & "database=M2MData01;" _

The code above has the following obvious problems:

  1. This user is still using the SA login. This is a tremendous security risk.
  2. The password, database name, and server are directly identified in the code. The M2VEvents.Prj file can be read by text editors, and this is a recipe for disaster. I know there are other ways for an intruder to obtain this in the M2M environment, but I still feel it’s important to lessen those risks in any way we can.
  3. If you change your database login names or passwords, your VBA code will need to be changed as well.

I thought it would be a good idea to demonstrate the way I attach to the M2M Database, and I believe it is also the method currently used by the Technical Consultants at Made2Manage as well.

My current method involves creating a module in VB called “DefaultModule” and inside I place the following code:

Public Function GetCs1() As String
    Dim SQLUser As String
    Dim SQLPass As String
    Dim SQLServer As String
    Dim SQLDB As String
    SQLUser = Eval("FDecrypt(ALLTRIM(ocompany.fcsqluser), oSession.cEncryptKey)")
    SQLPass = Eval("FDecrypt(ALLTRIM(ocompany.fcsqlpass), oSession.cEncryptKey)")
    SQLServer = Trim(Eval("ocompany.fcsqlserver"))
    SQLDB = Trim(Eval("ocompany.fcsqldb"))
    GetCs1 = "DRIVER={SQL SERVER};DSN='';SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPass & ";DATABASE=" & SQLDB & ";Connection Timeout=120"

End Function

Notice that none of the information is hard coded, it is all obtained from the M2M environment. Therefore, it’s harder for an unauthorized person to extract it, and if you change your database login and password, the change is automatically handled by your code. However, for some inexplicable reason, M2M can drop the information between screens. To remedy this I include the following in the INIT event of each screen using VBA:

Public Function INIT() As Boolean

    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    INIT = True
End Function

This code is cleaner, more secure, and just plain easier to understand. In the next post, I will share a small project that shows how to draw controls on M2M forms as well as manipulate the database directly.

Anybody have a different method or anything else to add?

Merry Christmas Everyone

I just wanted to take a moment to wish you folks a Merry Christmas and thank you for reading my ramblings.

As an adult I have to buy my own presents, so this year I bought a few things that interested me.

I bought a couple of these:


Star Wars Force Effects Light Sabers

I bought the Obi Wan and the Darth Vader Light Sabers. I’ve always wanted them and found them for half price online.

I also bought this Nintendo Neon sign I found on Craigslist.

Nintendo Neon

It’s an official Neon sign that Nintendo placed in game stores in the 90’s. Yes, I am a geek. 🙂 That particular picture isn’t of mine, but it’s the same sign.

Finally, I took Anakin, my cockatoo, to see Santa Claus to tell him what he wanted for Christmas.

Anakin and Santa

Anakin whispers in Santa’s ear.

Anakin and Santa

Anakin promised not to have an accident on Santa’s head.

Anyway, Merry Christmas Everyone.

Choosing a Made2Manage Consultant

The Bobs

The Bobs – Office Space

As I mentioned in my first post I’ve been working as a Made2Manage Administrator for more than 10 years. However, even if you’ve been one of my regular readers, you may not be aware that I’ve also been an independent consultant for the last several years as well. Over the years I have worked with several Made2Manage employed consultants as well as independents.

Occasionally I get emails from clients asking me what to look for when hiring a consultant, so I’ve decided to discuss it here to help those of you who are facing what might be a daunting task. Below are some questions and issues you should address with possible candidates.

  1. Is the prospective consultant professional in his communication and manner? Computer geeks are notorious for lacking communication skills. You should be very aware of how your consultant communicates as the lack of these skills may hinder your projects. Communication skills are every bit as important as technical skills and harder to learn as well. The hilarious SNL skit, Nick Burns Your Company’s Computer Guy, illustrates how true this is.

    Nick Burns


  2. Is the prospect experienced? Do they have an impressive skill set? How well does that skill set fit your consulting requirements? Beware of hiring someone who specializes in only a couple of Made2Manage technologies as they will attempt to address your issues with that limited skill set. This can result in less than optimal solutions as they try to fill a round hole with a square peg. If your project requires coding and report customization, be sure to hire someone with VFP, SQL, VBA, and possibly FastForms experience. Additionally, if you’ve purchased an optional module such as M2M Advanced Reporting, you would want someone experienced with it as well.
  3. How quickly does the prospect return calls? Most consultants are not available 24/7 but the good consultants return calls within one day.
  4. Is your prospective consultant realistic in how he assesses your project? No consultant is perfect and I would warn you against anyone who promises the moon, or who seems too good to be true.
  5. What are the prospect’s rates, completion policies, and guarantees of work? Rates are not as important as a consultant’s policies on what is billable and what isn’t. For example, if quoted 4 hours of labor for a report customization, is that an estimate or is the cost not to exceed that amount? Upon delivery of code, are you billed additional hours for corrections required after testing on your system? Most of the consultants, as well as Made2Manage itself, bill for everything, including the time required to remove bugs from their code.
  6. Additionally, does this prospect offer free help such as with a blog or in the online forums? Can you call the consultant and ask a few quick questions without incurring consulting charges? Again, often the answer is no, and you will be charged in 15 minute increments.
  7. Does this prospect have examples of their work to show you prior to being retained by your firm? What have they actually done?
  8. Is the consultant willing to train your staff to perform some of these tasks in the future?

What questions/issues do you feel should be addressed with a prospective Made2Manage consultant?

Data Utilities – Convenience or Crutch?

Recently I’ve been assisting a company with discrepancies in inventory master and inventory on hand tables. One problem is negative quantities in their inspection which can’t be removed, and they also have problems with inconsistencies in their on hand quantities of various parts.

So, a user brings the Material Availability (RPMAVL) and the Inventory Evaluation (RPIVAL) Reports to me and we compare them with a search from the On Hand By Location (INVOH) Screen. None of them show consistent data.

I explained to the user that M2M has data utilities, which are the first step in troubleshooting these kinds of issues. They are found under Utilities, Maintenance, and Data Utilities.

Data Utilities

I’m sure many of you have used them, and I’d bet that everyone has had to run “Ship Fix” at one time or another. We take these steps for granted when we need to fix issues. However, this user asked, “Why doesn’t M2M just fix these issues so that we don’t have to run these utilities?” This set the hamster wheel in my head moving, and I thought I would share those thoughts with you.

Why are utilities necessary?

Well,the most obvious answer is that utilities compensate for bugs in the Made2Manage software. These bugs can be in the software directly, or they could simply be a case where the software is not protecting the user from making a mistake. For example, Made2Manage will allow you to receive obsolete parts or even part revisions that are not in the item master to begin with. There may be valid reasons for this, but most often these are mistakes that need to be corrected. When the user attempts to do so, they can cause problems like I described above. In my opinion, regardless of the cause of the error (M2M or User Error), the problem lies with M2M.

All of these problems can of course be fixed without the use of a Data Utility by directly manipulating the SQL database, which is exactly what these utilities do. Data Utilities offer a more convenient way for users to fix these issues rather than copying and pasting SQL scripts into Query Analyzer. Also, consistency is more certain when data utilities are used rather than scripts provided by support.

Why don’t they just fix M2M so the utilities are unnecessary?

  1. M2M is a complex system and it’s simply easier and less expensive to fix occasional data inconsistencies than to properly diagnose problems with the code or user behaviors which are problematic.
  2. These problems tend to be infrequent and are therefore difficult to catch. The issue is similar to intermittent problems with your car. Your car never makes the noise in front of the mechanic.
  3. Customers would be inconvenienced by the troubleshooting process. In all fairness, M2M can only do so much testing of their product. They cannot afford to employ a testing team the size of a typical M2M customer company to enter data for years on end in the hopes of finding these problems themselves. They would request volunteers from a group of customers which receive this error a lot and ask them to troubleshoot this problem using SQL Profiler or a similar tool. This could hamper database performance and many customers aren’t willing to endure the inconvenience.

For those reasons, I can understand why M2M uses the data utilities.

However, what excuse can be made when even the data utilities are buggy as well?

M2M Releases Version 5.6 SP3…. umm..6 weeks ago.

Let me preface this by saying that very few M2M Admins are more active than myself. I am on M2M Expert nearly every day. I found out that SP3 was released through the Los Angeles Area Made2Manage User Group on Google Groups.

Am I the only one who didn’t know SP3 was released? It wasn’t posted in the “Latest News” section, nor was there an e-mail informing admins about its release. It wasn’t posted in their own users forum either. I know it was posted in the “Latest Downloads” section, but who really checks there?

I get plenty of e-mails from M2M regarding their latest add on modules in an attempt to increase sales. How about informing us of something important via the same method?

I know that people from M2M read this blog, what do you guys think? Would you like M2M to e-mail you when they release these service packs?

Answers are Easy, the Questions are Hard.

In the past year I’ve been doing a lot of T-SQL (Transact-SQL) programming. My current employer has required a great deal of it, and I have searched out several people and places on the internet to gain this knowledge.

However, as I’ve progressed on this journey I’ve noticed a curious thing. The typical situation begins when I run into a problem with some code I’m writing. I’ll google the issue or function I am struggling with. If you regularly ask questions in some internet forums without first searching for the answer yourself, the regulars will become annoyed and ignore you.

If the google search is fruitless, I will post a question to a forum such as SQL Team. However, it doesn’t take long to learn that you must be exceedingly detailed with your question or the experts will not bother to answer. This attention to detail is simply common courtesy as these experts are providing free help, and why should they waste their time to ask you a multitude of questions about your question.

The pattern I’m noticing is this: In forming my detailed question I am forced to step back from the problem and examine it, and in doing so I often answer my own question. Simply taking the time to properly form my question, answers it. When I mentioned that on the SQL Team forum, I got similar responses from the other members. In fact, a couple of them said that their employers had a policy that you first had to pose your question to an inanimate object (such as a human cardboard cutout or a teddy bear) before you could ask a senior programmer.

Have any of you found that answer your own questions by simply forming them?

Incidentally, my cardboard cutout would probably be something like this.