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.