Category Archives: Access Database Programming

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.

Access Database Programming

Access (VBA) – reference control dynamically

So I will write my code as I need to but I always like being able to save key strokes.

I recently wanted to be able to copy text in a field to the clipboard to use it in another program (one that I could not link to the main program).  So I wrote this:

 

Private Sub SReqOrganizationName_DblClick(Cancel As Integer)
      Dim strCTL As String
      strCTL = Screen.ActiveControl.Name
      With Me.Controls(strCTL)
        If Len(.Value) Then
           .SelStart = 0
           .SelLength = Len(.Value)
           DoCmd.RunCommand acCmdCopy
        End If
      End With
End Sub

By using the with statement, I didn’t have to customize this for each field that I wanted to do this on.

The better thing would be to write this as a public command rather than put this is each fields DoubleClick command. But this works and works well too.

 

Access Database Programming, Computer Programming

Access/VB do not print report with no data

Just a little bit of programming..after all it pays the bills.

I had setup the option to print different reports, depending on if something is manufactured in house or outsourced.  So I came up with this:

The code behind the button on the form:

Private Sub cmdPrintReport_click()

    On Error GoTo CatchErrNo

     
    If Me.JobNo > “” Then DoCmd.OpenReport “rptWorkOrder”, acViewPreview, , “JobNo = ” & Me.JobNo

 

CatchErrNo:

    If Err.Number > 0 Then
        ‘report will not open if there is no data, but throws an error. This will catch that error and print the workorder instead
        ‘jk – 4/18/2017
        If Err.Number = 2501 Then
            If Me.JobNo > “” Then DoCmd.OpenReport “rptPickList”, acViewPreview, , “JobNo = ” & Me.JobNo
        Else
            MsgBox Err.Number & ” – ” & Err.Description
        End If
    End If

End Sub

In the report design mode, you have to tell it what to do with no data (I never knew this was here)

Report – Event – On No Data

Private Sub Report_NoData(Cancel As Integer)
   Cancel = True
End Sub

Are there other ways to do this, yes, probably 100, but this is what works in the environment that I’m working in.

Access Database Programming, Computer Programming, General

Access / VB Programming – Using a MultiSelect field to send selected info via email

I have never used a multi-select list before, but now is the time.

Below is my adaption of Allen Browne‘s code.

My requirements are this:

  • Allow user to select from open orders
  • send list of selected orders to production – formatted simply – via an email

The database I am working with has a SQL datafile attached so saving a queryDef with the selected records was not an option. So I came up with this work around:

  • Collect the selected items and create the strWhere variable
  • Open the report using the condition – but open hidden (WindowMode:=acHidden) – it does not display to the user, but you can manipulate anything it it = assign the where condition
  • Send the report as an Excel file to specified email address with a subject of “Open Orders” and “Attached is the latest Open Orders list” in the body of the email – because the report is open and the filter is applied that is what gets sent out in the email – kinda slick. 🙄 

 

My changes to his code are bolded:

Private Sub cmdSendList_Click()
 On Error GoTo Err_Handler
 'Purpose:  Open the report filtered to the items selected in the list box.
 'Author:   Allen J Browne, 2004.   http://allenbrowne.com
 'implemented into database - 3/8/2016 - jk
 Dim varItem As Variant      'Selected items
 Dim strWhere As String      'String to use as WhereCondition
 Dim strDescrip As String    'Description of WhereCondition
 Dim lngLen As Long          'Length of string
 Dim strDelim As String      'Delimiter for this field type.
 Dim strDoc As String        'Name of report to open.
 'strDelim = """"            'Delimiter appropriate to field type. See note 1. -
 strDoc = "rptOrderList"
'Loop through the ItemsSelected in the list box.
 With Me.lstJobs
   For Each varItem In .ItemsSelected
     If Not IsNull(varItem) Then
       'Build up the filter from the bound column (hidden).
       strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
       'Build up the description from the text in the visible column. See note 2.
       strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
     End If
   Next
 End With
'Remove trailing comma. Add field name, IN operator, and brackets.
 lngLen = Len(strWhere) - 1
 If lngLen > 0 Then
    strWhere = "[jobID] IN (" & Left$(strWhere, lngLen) & ")"
    lngLen = Len(strDescrip) - 2
    If lngLen > 0 Then
       strDescrip = "Categories: " & Left$(strDescrip, lngLen)
    End If
 End If
'Report will not filter if open, so close it. For Access 97, see note 3.
 If CurrentProject.AllReports(strDoc).IsLoaded Then
     DoCmd.Close acReport, strDoc
 End If
'Omit the last argument for Access 2000 and earlier. See note 4.
 DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, WindowMode:=acHidden, OpenArgs:=strDescrip
 DoCmd.SendObject acSendReport, strDoc, "Excel", "OpenOrders@company.com", , , "Open Orders", "Attached is the latest Open Orders list" 

 

If CurrentProject.AllReports(strDoc).IsLoaded Then
 'closes the report that I had opened with "WindowMode:=acHidden"
 DoCmd.Close acReport, strDoc
End If
Exit_Handler:
 Exit Sub
Err_Handler:
 If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
 End If
Resume Exit_Handler
End Sub

Access Database Programming

When does 2=1

When you have two sets of information that are calculated separately, but yet need to be combined into one result.

Confusing, maybe, but not once you get the meaning. You have an inventory account that a bunch of purchases are going to, only they are not inventory. They actually should be charged to a different account. So you need to create the list of debits/credits for the bad account and for the correct account.

If you have 2 queries that have the exact same structure and to minimize transactions you want to combine them into one.

  1. Create a new SQL query
  2. Here is the text you will enter
    Select * from qryOne
    UNION ALL select * from qryTwo;
  3. Save the query
  4. View it – you should see the data from both queries combined into one set of columns.

I used this today to enter edits into an accounting system we had some purchases that were entered into the incorrect Account numbers. So i had one query with the wrong Account and the debit column had the $ in it. Then I had a second column and this time it had the correct Account on it and the $ were in the credit column – ran this little union query and they were both in one list and I copied and pasted into the journal entry screen.

If you only have a few row of data to fix, it is probably easier to do this in excel. But if you have lots (I had over 500 lines) which translates into over 1000 lines (one for debits and one for credits) this is a nice little tool to run and combine it all into one.

Access Database Programming, General

MS Access Report – dynamically collapse groups

Here is another bit of programming to make things easier with reports.

As we all know, you can put in many levels of grouping in MS Access reports. And there is always the request – I want to see detail and then I don’t want to see detail. The simplest thing to do is have 2 reports – one with many levels of groupings and one without. But is that the best way?

Depends – if you are not comfortable writing VBA code, then yes that is the simplest thing to do, but beware you will have to update both(all) reports when any changes are asked for by the boss.

Here is what I did:

rptDesignModeI have a report that is setup with 3 levels of grouping: Territory, Salesman, Month – what I found is to set up the report with the most detail/grouping that you will want to have, then you can not view the ones you do not want. This gives you the ability to add text to the header, totals to the footers and/or any other custom things you may want in the header/footer.

With the report in design view, click on Event tab – go down to “On Open” and click on the down arrow and select “Event Procedure”. This should open the VBA screen.

You should see something like this:

Private Sub Report_Open(Cancel As Integer)

End Sub

Here is my code:

Private Sub Report_Open(Cancel As Integer)

Dim vdet As Integer

vdet = MsgBox(“Do you want to see Salespreson Totals?”, vbYesNo)

If vdet = 7 Then

Me.GroupLevel(0).ControlSource = “Territory”
Me.GroupLevel(1).ControlSource = “Month”
Me.GroupLevel(2).ControlSource = “Month”

Me.GroupHeader3.Visible = False
Me.GroupFooter4.Visible = False

End If

End Sub

 

rptPromptWhen the report is opened (usually by a button on a menu) you will see a prompt asking which report you want to see:

Here is an example (without numbers) using the above code to show or not show details.

Show Salesperson DetailfullDetailsRpt (default) Show Summary OnlySumRpt

Alternative: Have a checkbox or radiobutton (rptChoice) on your form that would make this decision before the report is opened. Here is the code to do that:

Private Sub Report_Open(Cancel As Integer)

Dim vdet As Integer

vdet = me.rptChoice.value

If vdet = 1 Then

Me.GroupLevel(0).ControlSource = “Territory”
Me.GroupLevel(1).ControlSource = “Month”
Me.GroupLevel(2).ControlSource = “Month”

Me.GroupHeader3.Visible = False
Me.GroupFooter4.Visible = False

End If

End Sub

Access Database Programming

MS Access Report – duplex, page breaks on Group Break

I haven’t done a computer related post for awhile so I thought I would write this up so that I can find it the next time I need to do this.

I am setting up a reporting tool to print out inventory counting sheets. We want them to break on a group AND start each group on an odd page number. So being the great googler that I am, of course, I googled. Boy did I ever get a hodge-podge of answers.  After trying about 8 of them and having them all mixed together and nothing was working, I decided to do an overall look of what they were doing and start from scratch.

So, here is what worked:

  1. Create your report, create a sort/group with header & footer displaying.
  2. In the footer itself –
    I put a line of text that says “This page is blank for pagination”
    On the Design Tab: Controls -> Insert Page Break
    This will appear as a set of small dots – nothing else.

On the Property Sheet:

  1. Group Header:
    FORMAT: Force New Page: Before Section
  2. Group Footer:
    FORMAT: Force New Page: Before Section
    EVENT: On Format: Event Procedure

    Here is the code that I have for that Event Procedure:

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Me.GroupFooter0.Visible = ((Me.Page Mod 2) = 0)
End Sub

GroupFooter0 is the name of my group, you will have to change that to what your footer is named.

Goods & Bads:

  • Good: you get a blank page so that each group will start on a odd page number – for duplex printing
  • Bad: the page header also prints out on that blank page. This was okay with me, so I left it.