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.