So what then can we say is Visual FoxPro’s place in an enterprise application architecture today? Is it relevant when compared to other alternative offerings from Microsoft – the company that makes Visual FoxPro! This article attempts to document our experiences as a company that sells many custom-developed application software products made with virtually all of Microsoft’s Database Offerings (Ms-SQL Server, Ms Access and Of course Ms Visual FoxPro)!

I first started using FoxPro in 1992! Then it was version 1.01 running on a PCMOS 4.1 Network. I was fresh out of school with some little programming knowledge on GW-Basic, MF Cobol and dBASE III! The Mortgage Bank I joined and worked for ran its Banking Software (Swift Banking System) written with Clipper 5 on a FoxPro Database. At the time, most people wrote their programs on dBASE IV and then had those programs compiled with FoxPro or with Clipper! I fell in love with FoxPro immediately because it was so easy to use and had a much more polished interface than dBASE IV and what is more, I was also very impressed that a package as huge as Banking was running on a FoxPro box and was very stable in indeed!

Then in 1993, I left the banking sector and did not again use FoxPro until 2001 when I taught ‘Building solutions with Visual FoxPro 6.0’ to Students at MicroLink College (then the school had the option to use either Microsoft Access or Visual FoxPro to demonstrate Database Concepts)! It was during this time that I started exploring the idea of developing a powerful, versatile, modern and affordable MIS Package for Colleges and Universities since the college at that time had no computerized data storage and retrieval system – that package would eventually today become CampusManager™ University Advantage – still in commercial distribution and in use among several University Colleges in Ethiopia today (and running as a pure Fox solution).

The requirement was for a system that is feature –rich, user friendly, provided reasonable storage capacities for the sort of volumes of data that could be generated by a school or university college and was yet affordable by schools in the third-world. The combined features of an affordable and  feature-rich system was necessary because most of the clients that buy our system can be considered as small or medium-scale enterprises that will ‘balk’ at the cost involved in licensing Ms SQL Server or Oracle Databases. Also, the programs would be running in an environment where these organizations may not necessary afford thousands of dollars to retain trained SQL Server or Oracle DBA’s so the system had to be light, easy to run and still provide all the storage capacity and power of an enterprise solution built to run on SQL Server or Oracle!

This reality for us was underscored by the fact that most of the Visual Basic programs we wrote that were designed to run with MS SQL Server; have always suffered from  customer (small to medium scale organizations) complaints – Managers do not simply find that purchasing expensive Ms SQL Server database  licenses is somewhat prohibitive! At the same time, Microsoft Access (Jet Database Engine)/MSDE did not present an attractive alternative because of the 2GB File Size limit that would sooner rather than later force a mid-sized company to look for alternative storage. Add to this the fact that in a typical school, we would have more than 10 users working at a time and Access’s well documented behavior when 10 (ten) or more users are accessing the database concurrently! To summarize, our ‘business parameters’ for selecting an application development tool for our CampusManager™ Offering was (and still is) as follows:

• System must be feature rich
• System must provide reasonable storage capacity for at least 5 years for a school or college with about 2000 student population
• System must be capable of supporting at least 20 concurrent users without signs of stress (imagine that at least 15 homeroom teachers at a time have to record marks and enter grading information during peak period such as exam time concurrently along with other routine activities by staff and faculty). Our first site, a premium private school boasted 60 homeroom teachers (Grades 1 to 12 each with five sections A to E)
• System has to be capable of routine performance on just about any environment (the first school we sold to had a P3 Server with Two Processors) since in our market, most clients may just have a PCs and may never even have heard of Servers
• System must be light and fast and be able to be easily, deployed, configured and implemented in the end-users environment
• Total cost of ownership for the system must be kept to a minimum. For example, can users easily administer the database (most of our clients may have a teacher or two with a background in simple applications and so would not possess the type of skills required to manage a typical SQL Server Database). FoxPro in contrast is easy and fun to learn!
• System must be affordable. If our clients can pay not only for our system but also license the database engine for online database maintenance, it is great for them and for us!

So for us it is against this backdrop that we realized that we had a copy of Visual FoxPro bundled with our Visual Studio 6.0 suite of enterprise development tools. After carefully assessing our options, we came to the conclusion that Visual FoxPro 6.0 was the best tool in the Visual Studio suite (we had been concentrating on Visual Basic and Visual C++ against Jet/SQL Server for our other applications) to build a fast, data-centric MIS system for schools, colleges and universities because it just about offered the right combination of power, speed, storage capacity and licensing cost for the market we wanted to target with our system.  For us, this meant that we would not have to ‘fight’ with our clients about expensive database licensing nor would we have to explain why our clients need to move to a more expensive Database Server after just 2-3 years. At the same time, should the need arise, we can easily upgrade the system to run on a bigger database engine (such as SQL server or the Advantage Database Server that now provides full Visual FoxPro Compatibility) without expensive code rewrites!

Since our software was released in June of 2006, we have upgraded to using Visual FoxPro 9.0 and have found that our product has elicited quite a lot of interest because of the attractive cost of the package and licensing terms for a VFP Database (optional). But whether our clients accept to buy VFP Database licenses or not (in which case they can use the VFP runtime), our product is still a money-spinner for Microsoft since our clients will still need to license Microsoft Windows to run the application; plus, they always license the application with a promise to upgrade to Ms SQL after a number of years.

A couple of months after SD Times posed the question “Where have you gone Visual FoxPro?”; we want to supply an emphatic answer – “Visual FoxPro is still here today! Visual FoxPro is still very relevant in the enterprise!” Here in Africa, we are using Visual FoxPro to build modern, powerful, object-oriented and affordable applications for fast growing companies!

And yes! Even though Microsoft says they shall make Visual FoxPro no longer, the product can only get better as many members in the community work to make great add-ons and improvements in the product, adding badly needed functionality. With some of the cool tools we have found on CodePlex such as VFPX and most especially ActiveVFP, we can say that we are in no great hurry to re-write our application on the .NET framework anytime soon!

Yes! Visual FoxPro lives!

All the tools that we need to created windows scripts are included in windows and the is simply notepad. You can get other editors to use such as Script Editor, Scintilla and SciTE, and many more. But all that you need is Notepad and you are good to go. When you create a scripting file you will create any name you want and you can give it the extension of .VBS (Visual Basic Script).

Lets write out first script, the famous Hello World script, by opening up notepad and simply typing:

wscript.echo “Hello World”

Now simply double click on the file that you have created and you will see a pop up box that will display the text “Hello World” give it a try. This script can also be executed by a command prompt by typing “cscript hello.vbs” from the command prompt.

All good programmers comment their programs so that years from now they can look back at a program they wrote and tell what it is doing based on the comments. A comment is simply text in the program that is only seen by the programmer and never by the person running the program. You can type anything you want in a comment and it will not effect the program in any way. Professional programmers comment on every aspect of their program so that others who read the code can understand what it is doing. To add a comment in a VBS file simply type REM or ‘ at the beginning of a line and every thing on that line will be a remark and will have no effect on the program. Lets look at our Hello.vbs program and lets add a couple of remarks in the program so that we know what is going on.

‘ hello.vbs
‘ This is just your typical Hello World program

wscript.echo “Hello World”

If you happen to type something wrong windows will give you an error telling you that you have done something wrong and will even give you the line that the error is on. Lets mess up our program and see what kind of error we receive.

‘ hello.vbs
‘ This is just your typical Hello World program

wscript.echo “Hello World”
wscript.ech “I messed up this line”

Now lets run the program and see what kind of error we get. The first line is executed just fine but we receive a error on the second line:
Script: c:\users\mike\desktop\hello.vbs
Line: 5
Char: 1
Error: Object doesn’t support this property or method: ‘wscript.ech’
Code: 800A01B6
Source: Microsoft VBScript runtime error

So as you can see from this error that we have a problem on line 5 character 1 of our code. The error Object doesn’t support this property or method: ‘wscript.ech’ usually means that you have a misspelling somewhere. The Code is not really that important to know just the location of the problem and what the “Error” actually says.

As with any programming language VBScript uses variables to hold values and strings. To assign a value to a variable simply use the variable name you would like and then the = sign. One examble would be ‘mike = 24′ now the variable mike has a value of 24, now lets add this to our Hello World program and show that the variable mike has a value of 24.

‘ hello.vbs
‘ This is your typical Hello World program

wscript.echo “Hello World”

mike = 24
jake = 30
diff = jake – mike

wscript.echo “Mike is “, mike
wscript.echo “Jake is “, jake
wscript.echo “The difference between their age is “, diff

As you can see here we assigned the value 24 to the variable mike and we assigned the value of 30 to the variable jake. We were then able to perform and mathmatical operation on the two values to get the difference between them. Then by using the wscript.echo command we were able to display the variables. Lets continue on with variables and lets store a string which is stored as text and we will get input from our user.

‘ hello.vbs
‘ This is your typical Hello World program

name = InputBox(“What is your name?”)
age = InputBox(“How old are you?”)

wscript.echo “Hello “, name
wscript.echo age, ” is a good age”

Do get the input box to pop up we will use the InputBox command and use the = sign to assign what the user types in to a variable and then we are able to print the results on the screen. Now lets go a little further and lets have the program make a decision about what to do based on what the user types. To make this program make a decision we will be using the IF… ELSE IF… ELSE… statements.

‘ hello.vbs
‘ This is your typical Hello World program

name = InputBox(“What is your name?”)
age = InputBox(“How old are you?”)

wscript.echo “Hello “, name

If age < 18 Then

wscript.echo “You’re Illegal!!”
ElseIf age > 17 AND age < 24 Then

wscript.echo “You’re a young one”
ElseIF age = 24 Then

wscript.echo “You are at the best age! (same as my age)”
Else

wscript.echo “WoW you are old!”
End If

With the script above we were able to receive input from the user and then make a decision on what to do based on what the user typed in. I will go through each line and describe exactly what it is doing so that you can fully understand the program.

name = InputBox(“What is your name?”)
age = InputBox(“How old are you?”)

Displays and input box asking the user “What is your name” then it stores the value in the variable name. Then is displays another input box asking the user “How old are you?” and stores the inputed value into the variable age. For this explaination we will say that the user typed in 24 for the age.

wscript.echo “Hello”, name
This line simply displays that variable name which is what the user typed in the first input box.

If age < 18 then

wscript.echo “You’re Illegal!!”
This line is is executed exactly as it is read, If the variable age is less than 18 then print on the screen “You’re Illegal!!”. As we stated above the variable age is going to be 24 so the variable age is not less than 18 so the line directly below will now be executed.

ElseIf age > 17 AND age < 24 Then

wscript.echo “You’re a young one”
The above statments is using the ElseIf statement which will only be looked at if the above statement was false. We are now checking to see if the age variable is between 17 and 24 and if it is we will print “You’re a young one”. But since the variable age is 24 then it is not between 17 and 24 so this statement will not be executed and we will move on to the next.

ElseIf age = 24 Then

wscript.echo “You are at the best age! (same as my age)”
This statement is the same as the previous one except we are seeing if the variable age is equal to 24 and if it is we will execute the line directly below it. In our example since we did type in 24 so the variable age equals 24 the line directly below will be executed and a windows will pop up saying “You are at the best age! (same as my age)” and the entire IF statement will be finished. Now we will continue on with this example and check on the last line.

Else

wscript.echo “WoW you are old!”
End If
The Else statement is only executed if none of the above criteria are met and in our case since the age variable equals 24 the Else statement will not be executed. We then have to add the End If statement at the very end to tell the program at the entire IF statement is over at this point.

We have covered a lot of ground here with VBScript so please review everything we have talked about and play with the scripts. Experiment as see what you can do with the script and think about how you can mix multiple variables and input boxes and IF statements to put together a very useful program that can make your life easier.

I stated in the beginning I was going to discuss a script that I created to modernize an older piece of software to save money on upgrading. What I will discuss here was not covered in this article but it will give you an idea of the kinds of things you can do with scripting.

I was asked to undertake a task at work to migrate all of our alerting systems from older pagers to our newer cell phones utilizing text messaging. This wasn’t a big problem with most of our software because of the ability to send an email to the cell phones as a text message. But we had two very important pieces of software that were about $10,000 a piece to upgrade to a version that would be able to send emails and upgrading was not an option for the time frame that we had to implement our new cell phones.

Both programs had the ability to print to a printer when an alert happened. I setup the programs to print to a file and put it in a specific directory whenever an alert happened. The script I wrote looped every 10 seconds and would look for a new file in that particular directory. When the script found a new file in folder it would then email the contents of the file to the appropriate cell phone and then delete the file and wait for another new file.

Please keep watching for new articles for more detail about windows scripting. If you have any questions or would like more detail about my custom emailing script please feel free to contact me. Thanks for reading

‘*******CODE*******

Dim root As String

Dim srcDB As database

Dim tdf As TableDef

Dim x As Integer

Root = “C:”

Set srcDB = OpenDatabase(root)

For Each tdf In srcDB.TableDefs

x = 0

If (tdf.Name = “CULVERT”) Then

DoCmd.TransferDatabase acImport, “Microsoft Access”, root, acTable, “CULVERT”, “CULVERT”

errorCheck = 1

End If

Next tdf

‘******************

After the variable declarations, the first line of code sets the variable “root” equal to “C:” which is the location of the external database holding the tables we wish to import. The next line sets our database variable equal to the external database. The next lines of code search through the external database to find the table named “CULVERT.” If this table is found, the table is imported using the DoCmd function. The “acImport” variable simply tells the function that we wish to import information from the database. “Microsoft Access” specifies the type of database containing the table. Following that is the folder location (“root”) of the external database. The variable actable specifies that the object we wish to import is a table. The next two strings contain the table name to import and the name to assign to the imported table when it is imported into the current database. It should be noted that if the table named to be imported already exists in the current database, MS Access will automatically rename the table being imported.

Once the desired tables have been imported, they can be utilized just like any of the other tables in the database.