Category Archives: Internet

Access Database Programming, Computer Programming, Digital, Internet

Access VB – create ODBC connection to download data – part 1

I am transitioning a program from manual data entry at a registration table to online registration and item entry done by the sellers.

This is from an application that I wrote for a non-profit/hobby group that holds a monthly auction – similar to a swap meet. People come and bring their stuff to sell and the buy other peoples stuff.

Currently we have an online form that sellers enter their items and prior to the auction start we do a mass download that fills the onsite program with all items. Then item labels and lists are printed along with bidder cards so that the sellers can stop at the check in desk and pick up their packet when they get to the auction.

The challenge is that we close item entry at 7am and the auction does not start until 11am – we have to drive 2 hours to get to the auction site and up until now, there has not been an internet access at the location. So the download had to happen before we left the house.  An internet access has been made available and so I wrote this code so that we can download at the time of check in – allowing people much more time to enter the items they were bringing into the online form.

The programming problem is that when an access database with a linked table (non local) is opened the ODBC connection needs to be “refreshed” at that time, however it will close when there is no activity (?). AND the kicker is that the database/program will need to be closed to reactivate it. This is a well-known annoyance and according to all the forums I have read lately they are supposed to be working on it but nothing has really been seen to have changed as of yet. So you have to do a couple of work arounds.

Here is my take on the subject:

*** This will not run if you are running this application using Access Runtime – you must have a full version of MS Access to get this to run – at least that is what 20+ hours of recoding, testing and research has proven to me so far.

***the ODBC connection will time out – not necessarily when you plan it to – Connection Timeout=6000 – doesn’t necessarily mean what it should. According to all the documents I have found you SHOULD have to close the program and load it again for this to work. What I did find out is by having the timeout=6000, yes the connection will close. And if you try it again once it closes or feels that it has been quiet for that period of time, you will be a error/message that the ODBC connection is closed. However, if you continue working with the database program still open, after a period of time – more than the amount you have set in the timeout option – and you click the button it will refresh/reset the connection and start working again.  I am testing this but it is difficult to get exact measurements because each time you ‘test’ it, the timer seems to get reset so you start counting all over again. But I have gotten it to work pretty regularly/irregularly.

From:

Form is a seller information form.
Find the seller database and there is a button to “Download Items”

Code:

Dim myDB As Database
Dim tdf As TableDef
Dim strCN As String
Dim imsg As String
Dim pRecCount, aRecCount As Integer

‘get count of items for this seller before download
pRecCount = DLookup(“CountofIID”, “qrySellerStats”, “sid = ” & Me.SID)

Set myDB = CurrentDb()
Set tdf = myDB.TableDefs(“tblItems-web”)
‘Debug.Print tdf.Connect

strCN = “Driver={MySQL ODBC 5.1 Driver};” & _
“Server=1.2.3.4;” & _
“Port=1111;” & _
“Database=database on server;” & _
“Uid=username;” & _
“Pwd=password;” & _
“Connection Timeout=6000;”

tdf.Connect = strCN
tdf.RefreshLink

DoCmd.SetWarnings False

‘access query to download the data and append to the database for selected user

DoCmd.OpenQuery “qryAppendFromWeb_sid”, acViewNormal

Set tdf = Nothing

‘refresh form to show new items downloaded
Me.subfrmInputItems.Requery

‘find how many records are in the database for this seller
aRecCount = DLookup(“countofiid”, “qrySellerStats”, “sid = ” & Me.SID)

If aRecCount > pRecCount Then
      Me.type.Value = “M”   ‘sets a field on the form that items were entered online
End If

imsg = aRecCount – pRecCount & ” records imported”

Me.Refresh

DoCmd.SetWarnings True

MsgBox (“Import Process complete – ” & imsg)

~~~~~~~~~~~

So to get around the problem of the connection time out issue I rewrote this to use a secondary database just for downloading the data. 

Access Database Programming, Computer Programming, Digital, Internet

Access VB – create ODBC connection to download data – part 2

Part 1 of this topic.

Read the Notes and comments about this process/code – it might save you some time.

So to deal with the disconnection issue and not knowing exactly when it resets I took another approach.

Form:

everything on the form is the same – there is a button do download the items. However, this time I have that button open a new database file so that the connection will always be available.

Option 1.a:

download button code:

Dim pRecCount, aRecCount As Integer

pRecCount = DLookup(“CountofIID”, “qrySellerStats”, “sid = ” & Me.SID)

Application.FollowHyperlink “\\localdrive\folder\itemdownload.accdb”, , True

‘opens message box have this program stop and wait for manual answer as to when the import is done so that it can refresh the item list for this seller
MsgBox (“download applications starting” & vbCrLf & “click ok when complete”)

aRecCount = DLookup(“countofiid”, “qrySellerStats”, “sid = ” & Me.SID)

If aRecCount > pRecCount Then
  ‘simply sets a field on the form that items were input on the web if items were downloaded
   Me.type.Value = “M”
End If

Me.subfrmInputItems.Requery

~~~~~

When the other application opens up, there is a spot where the user has to enter (Select from a dropdown) the Sellers ID number and then it runs the code that was originally written.

****This will not work if you are running using Access Runtime – it requires a full version of MS Access to do this.

Option 2

This was actually the first way that I wrote this but after testing it I found that many parts of it would not work in the runtime version that I use on the computers. 

This option also uses a secondary database to do the actual download – getting around the problem of the odbc connection.

Download Button code:

Dim objAcc As Object
Dim accFrm As Object
Dim strBookmark, strDBName As String
Dim pRecCount, aRecCount As Integer

pRecCount = DLookup(“CountofIID”, “qrySellerStats”, “sid = ” & Me.SID)

other database program that does the actual download
strDBName = “W:\auctiondownload.accdb”

Set objAcc = CreateObject(“access.application”)
objAcc.UserControl = True
‘opens the new database
objAcc.OpenCurrentDatabase (strDBName)
‘in the new application that is running, takes the SID from this form and populates a field (selsid) on the new screen
objAcc.Forms(“frmMainMenu”).selsid = Me.SID
‘updates the information box on that form to show the user that is being downloaded
objAcc.Forms(“frmMainMenu”).sellerinfo.Requery

objAcc.Forms(“frmMainMenu”).SetFocus

‘there is code out that to run the actual download program but I could not get it to run from here
‘so what I did is on this secondary access program (auctiondownload.accdb) the main menu opens
‘and the main menu is on a timer – after 5 seconds
‘if there is a seller id in the selsid field on the form it performs the download process and closes the application

‘this message box is just so that once the download program is done you confirm that and it refreshes the item list
MsgBox (“download applications starting” & vbCrLf & “click ok when complete”)

aRecCount = DLookup(“countofiid”, “qrySellerStats”, “sid = ” & Me.SID)

If aRecCount > pRecCount Then
  Me.type.Value = “M”
End If

Me.subfrmInputItems.Requery

~~~~

AuctionDownload.accdb
MainMenu – loaded automatically

The database program puts the seller ID into this form

The form is setup with a timer interval and what to do when that interval is reached.

So 5 seconds after the form is loaded it runs the code in Timer()

‘you should see the other program open up and run the process and a msg box should show up that it’s done.

Private Sub Form_Timer()
     cmdDownload
     DoCmd.Quit
End Sub

I know this really seems like a cheat – but it works and it works really well.

Note:

  • the download process will not run use the Access runtime. It just errors out.
  • If you are directly connected to the server (locally) that holds the data, then the ODBC disconnect problem should not be an issue for you. Meaning – if you can click on those tables and see the data in there without having to do any type of connection (ODBC screen does not pop up asking for username/password) you do not need to use any of this -you can just run the append queries without a problem.
  • There are probably 20 or 200 other options to do this, THIS is what worked in my environments (I have used this in multiple situations now with only minor issues) and maybe it will work for you too.
  • THIS WILL NOT WORK IF YOU USE MS ACCESS RUNTIME – i put this here because no one else has and I spent a lot of time trying to get it to work and got very frustrated.
  • Also, be aware that different versions of Windows sometimes has an affect on how the secondary database will open – I tested it on one computer that is running Windows 8 and the secondary application window would never come up to the front – that is why I went with the timer option on the form. So this is not a full proof answer – just my answer to the situation.

Computer Programming, Digital, Internet, Javascript

Javascript – check required fields in form – return to form

PROBLEM: check required fields (more than 2) before adding items to a database – this will help curb bad data entries in your database.

I have a form that uses .ajax to write to the database and then relist all the items added. Like a shopping cart…in a way.  So I split out the validation part – it was simpler this way.

<input type=”button” class=”addtocart_button” value=”Save” title=”Save” onClick=”ckItem()” />

SOLUTION:

When the “Save” button is clicked it runs: ckItem()  –

function ckItem(){
//javascript function loaded in header
   
   $formID = "itemEntry";
   //pulls information from form that was submitted
   $category = document.forms[$formID]['category'].value;
   $genius = document.forms[$formID]['desc1'].value;

   //checks required fields for data  
   if (!$category || !$genius) {
       //if required field(s) are blank, do not add record
      alert("Category & Description are required fields");
      if (!$category) {
         document.forms[$formID]['category'].focus();  //returns cursor to this field
         return;  //stops the function
      } else if (!$genius) {
         document.forms[$formID]['desc1'].focus();  //returns cursor to this field
      }
   } else {
      //if both fields have data, it will now write the data to the database
      saveItem();
   };
}

 

Computer Programming, Digital, Internet

PHP Multidimensional Array NOT using Index

Okay – so I run into this every once in awhile and I usually code around it, but I decided on this cold day in January (-18° below at 7am) that I was going to get this working the way I want it to be!!! 

When I google this issue it just loops around and around in a big circle – very frustrating – so when I got it working I decided to put it here – mostly so I could find it!!! 🙄 

SITUATION:

I have a form where the information is then put into an sql table.
On this form is a dropdown selection box – being a good data steward, I am only recording 1 character for each choice. However, when displaying the completed list of items that have been entered, I would rather display the actual description rather than the “value” of the dropdown. 
Seems like a simple thing – right?!

Here is the dropdown  Here is the code behind it 
<select name="category" id="category">
   <option selected value="">Select Category</option>
   <option value="F">Food</option>
   <option value="L">Live</option>
   <option value="E">Equipment</option>
   <option value="P">Plants</option>
   <option value="M">Media</option>
   <option value="T">Tank</option>
   <option value="R">Large Item</option>
   <option value="O">Other</option>
 </select>
So when the items are listed it looks like this:

So, what I wanted to do is when I am looking thru the recordset of the database, I want to have an array that has a cross-reference to the Value that is stored in the Category field in the database.

Seems simple enough – but every reference I could find to arrays in PHP you use the index number to find and display the item in the array. I went thru 6 pages on google to finally find one that references it using the item not the index number.  Kudos to this place.

MY SOLUTION:

In my listing file, I created this array (this is the information used in the form – shown above):

$catList = array(
             "F"=>"Food",
             "L"=>"Live",
             "E"=>"Equipment",
             "P"=>"Plants",
             "M"=>"Media",
             "T"=>"Tank",
             "R"=>"Large Item",
             "O"=>"Other",
 );

Then, to do the lookup in the array:

           $catList[$row[‘category’]]

$row is the array of information that is being pulled from the database, category is the the field from the database that holds the info from the submitted form.

Well, now the list looks a whole lot better:

BEFORE AFTER

 

Digital, Internet, Web Design

A bit of php programming…

I do this type of programming about 4 times a year and each time I have to lookup how to compare dates in php. There are so many options and some make it out to be so complicated I thought I would just thru my answer into the pool. The following code is used to display a message depending on the date. Basically it announces a business will be closed for a holiday.

I use a multidimensional array to hold the holiday information, then go thru it to see if it needs to be displayed. This is also setup so that more than one message could appear at any given time (usually around Christmas & New Years). As you can see from my array, I supplied 2 dates – start date and end date.

array definition: collection = array(start date, end date, first line of message, second line of message)

php code :

<?

$holidays = array(
“Easter”=>array(‘2013-03-20′,’2013-03-30′,’Friday March 29th’,’in recognition of Good Friday’),
“MemDay”=>array(‘2013-05-15′,’2013-05-28′,’Monday May 27th’,’in recognition of Memorial Day’),
“IndDay”=>array(‘2013-06-25′,’2013-07-05′,’Thursday July 4th’,’in recognition of the 4th of July’),
“LabDay”=>array(‘2013-08-15′,’2013-09-03′,’Monday September 2nd’,’in recognition of Labor Day’),
“Christmas”=>array(‘2013-12-10′,’2013-12-26′,’Tues December 24th & Wed, December 25th’,’in recognition of Christmas’),
“NewYear”=>array(‘2013-12-12′,’2014-01-02′,’Wed January 1st’,’in recognition of New Years Day’)
);

$txtMsg = ‘<p>We will be closed<br /> ‘;
$MsgCount = 0;
$today = date(“Y-m-d”);
foreach ($holidays as $value)
{

if (date($value[0])<$today && date($value[1])>$today)
{
$msgCount++;
$txtMsg .= ‘* <span>’.$value[2].'</span> * <br />’;
$txtMsg .= $value[3].'</p>’;
}
}

if ($msgCount >0) {
print $txtMsg;
}

?>

I could have had the php code write out the day of the week, Month name etc, but because of limited space where this is being placed on the web page, I wanted to control what was displayed. For example, where Christmas is 2 days, writing out the complete names of the days would have caused it to wrap around and look out of place on the web page. But you sure could do that if you wanted to.

I actually have an excel worksheet that has this data in it and does the calculations for me to set the start date 10 days ahead of the shutdown date.

General, Internet, Javascript, Web Design

Web Hosting Options

My 3 favorite hosting options (in no specific order):

  • pair.com – I have hosted with pair.com for 15+ years and I have never had an outage or interruption of the service. They used to be more for the hands on web master that doesn’t need or want a graphical interface to do things. They have recently introduced a new Control Panel that will walk you through any and every thing you want to do. I highly recommend them.

  • justhost.com – I have hosted with justhost.com for the past 5 years or so. They offer unlimited hosting of domain names. They have a great control panel that offers you all the options you need or want. Lots of space for websites and email. I highly recommend them.
  • godaddy.com – I have worked with many sites that are using godaddy.com hosting services, some options they are are better than others. Recently they started offering WordPress as an option. The only “con” I have for them is that some of their web site building applications have a slow response when you are creating. But they do offer so many deals it’s hard to not like these guys.

Internet, Javascript, Web Design

Find the Index number of an item in a Javascript Array

I recently was creating a quoting form for a company that I was working for and I wanted to streamline as much as possible (i love to combine things or as I say, get more for my money!). When the quote was completed it would return a description of the items ordered and assign a Smart Part Number to the configuration.

  • So I have a list of categories – with options, setup as radio buttons.
  • When a selection is made I want to load that item (description, price, partNo) into the display area.
  • And build and display the Smart Part number.

TASK: The order in which the items are displayed and added to the display area is very important, so I coded the radio buttons with an ID – then I lookup that ID in an array and get the index number (order number) of that item and that is the placement in the smart part number.

Here is the javascript code I used to do that:

<SCRIPT language = "JavaScript">

//define array outside of a function to make it universally available
var arrPNC = [ "00", "A", "B", "C", "D", "E", "F", "G", "H"];
var itemOrder = 0;

//function to do the lookup and return index(order) must pass a letter
function findItem(tComp){
    findVal=tComp.value  //gets the value from the radio button
	//alert(findVal);  //if you want to see what is being searched for
    for(n=0;n<9;n++){
        //alert(arrPNC[n]); // if you want to see what is checked
        if(arrPNC[n]==findVal)
         {//if matches break from loop and get the index
            itemOrder = n;
        }
    }
     if (itemOrder > 0 ){       
       alert(findVal+ " is in position " + itemOrder + " in the array");
     } else {
       alert(findVal + " was not found in the array. Please try again.");
     }
     itemOrder = 0;
}
</script>

Example of the radio button input tag:
<input id="F" onclick="findItem(this);" type="radio" name="B" value="F" />F

Try it select an item below:

A  B  C   D   E   F   G   H   I

General, Internet, Web Design

Show your work!!!!!

During a recent job interview, I was given a programming test / quiz – which I botched up royally – I froze. And no, I didn’t get the job. So in the spirit of my math teachers – I’m going to ‘Show My Work’.

The test was to write the code to display numbers 1-100, if divisible by 3 – write “Yeah”, if divisible by 5 – write “Baby”, if divisible by both – “Yeah Baby”. I didn’t freeze because I didn’t know how to do the “for/while” statements/loop or the “if” statements, it was because I had never had to check for a WHOLE number before. So my mind was racing on a small detail of the question – in my mind an important part of the question (but not to them I guess). Well, I now know how to check for a whole number in 3 different ways. Something learned!!!

So here are my 3 versions (I have a tendency to be dramatic you know):

JavaScript:

<script type="text/javascript">
function YB(){
    for(i=1;i<=100;i++)
    {
        x = i/3;
        y = i/5;
        if(x == parseInt(x)){
            a = " Yeah";
        }
        if (y == parseInt(y)){
            a = a+ " Baby";
        }

        document.write(i+" "+a+" <br />");
        a = "";
    }
}

</script>

PHP:

for ($i =1; $i<=100; $i++)
{
    if($i % 3 == 0){
        $a = " Yeah";
    }
    if($i % 5 == 0){
        $a .= " Baby";
    }
    print $i." ".$a."<br />";
    $a = "";
}

Java:

public class yeahBaby{
    public static void main(String[] args) {
        for(double i =1; i <= 100; i=i+1)
        {
            String a = "";
            double x = i/3;
            double y = i/5;
            if(x == Math.rint(x)){
                a = " Yeah";
            }
            if(y == Math.rint(y)){
                a = a+ " Baby";
            }
            System.out.println(Math.round(i)+" "+a);
        }
    }
}

General, Internet, Web Design

Another Ad Provider / Tracker

So I was doing some reading on the internet this weekend – staying out of the heat and humidity and I ran across a site that was rating the new rash of “Single deal” sites that are now out there (like Groupon), and they also talked about this as service that they use. Although the first thing they mention is not have have too many of these links/areas on your webpage or it will start to look cheesey. Then I looked around their layout – they had 8 different areas for ads on their page. Hmmmm?

Anyway – you get credit when people sign up as advertisers (company for them to push their ads) and publishers (you display those ads). Well at least their site didn’t have typos on it like another one that I ran across this weekend.

I signed up for a new ad service this weekend, just to try something different:

It’s another ad servicing site, but I thought I would give it a try.