Developing in Access



  • There is this Access database. Part of it manages inventory. There is a large storage, in which crates of different stuff from different sources are stored, and in the Access database there is a form onto which this information is entered. There are 38 rows of 10 columns of crates and the height varies.

    The form is graphical. You can enter a row number and it will give you a graphical view of it, which you can change by clicking the crates on and off. The crates and their positions are stored in a table. The individual crates are represented by labels, some of which will be hidden because of the varying height of the roof.

    The code is horrendous. Below are the 'load' and 'save' routines:

    Private Sub kisten_van_database()
       Dim qd As QueryDef
       Set qd = CurrentDb.CreateQueryDef("kisten_laad", "SELECT x,y,z,tdrid FROM kisten")
       Dim rs As Recordset
       Set rs = qd.OpenRecordset()
       x_kleur% = 1
       On Error GoTo x
       While True 'For i = 1 To rs.RecordCount
          x% = CInt(rs.Fields(0).Value) 'x
          Y% = CInt(rs.Fields(1).Value) 'y
          z% = CInt(rs.Fields(2).Value) 'z
          tdr_id_s(x%, Y%, z%) = Val(rs.Fields(3).Value)
          p_tdr_id_s(x%, Y%, z%) = Val(rs.Fields(3).Value) 
          If k_(tdr_id_s(x%, Y%, z%)) = 0 Then
             k_(tdr_id_s(x%, Y%, z%)) = x_kleur%
             x_kleur% = ((x_kleur% + 1) Mod 3) + 1
          End If
          rs.MoveNext
       Wend 'Next i 
    x:
       rs.Close
       qd.Close
       CurrentDb.QueryDefs.Delete "kisten_laad"
       gewijzigd = False
    End Sub
    

    Private Sub kisten_naar_database()
    For z% = 0 To 37
    For Y% = 0 To 5
    For x% = 0 To 9
    sqlcmd$ = ""
    If tdr_id_s(x%, Y%, z%) > 0 Then
    If Not tdr_id_s(x%, Y%, z%) = p_tdr_id_s(x%, Y%, z%) Then
    If p_tdr_id_s(x%, Y%, z%) > 0 Then
    sqlcmd$ = "UPDATE kisten " & _
    " SET tdrID=" & CStr(tdr_id_s(x%, Y%, z%)) & _
    " WHERE x=" & CStr(x%) & _
    " AND y=" & CStr(Y%) & " AND z=" & CStr(z%)
    Else
    sqlcmd$ = "INSERT INTO kisten (x,y,z,tdrId) VALUES (" & _
    CStr(x%) & "," & CStr(Y%) & "," & CStr(z%) & "," & _
    CStr(tdr_id_s(x%, Y%, z%)) & ")"
    End If
    End If
    Else
    If p_tdr_id_s(x%, Y%, z%) > 0 Then
    sqlcmd$ = "DELETE FROM kisten WHERE x=" & CStr(x%) & _
    " AND y=" & CStr(Y%) & " AND z=" & CStr(z%)
    End If
    End If
    If sqlcmd$ <> "" Then
    Application.DoCmd.RunSQL sqlcmd$
    p_tdr_id_s(x%, Y%, z%) = tdr_id_s(x%, Y%, z%) 'dit lijkt me duidelijk
    End If
    Next x%
    Next Y%
    Next z%
    gewijzigd = False
    End Sub

    Now, selecting a crate is done this way:

    Private Sub kist(x%, Y%)
     On Error GoTo fail
     tdr_id_s(x%, Y%, curr_rij%) = tdrid_list.Value
     Dim Naam$, ras$, Perceelnr$
     Call naam_en_ras_en_perceelnr(tdrid_list.Value, Naam$, ras$, Perceelnr$)
     If (k_(CInt(tdrid_list.Value))) = 0 Then
        k_(CInt(tdrid_list.Value)) = x_kleur%
        x_kleur% = ((x_kleur% + 1) Mod 3) + 1
     End If
     knoppenlijst(x%, Y%).Caption = Naam$ + vbCrLf + ras$ + vbCrLf + Perceelnr$
     knoppenlijst(x%, Y%).BackColor = kleuren(k_(CInt(tdrid_list.Value)), 0)
     knoppenlijst(x%, Y%).ForeColor = kleuren(k_(CInt(tdrid_list.Value)), 1)
     gewijzigd = True
     Exit Sub
    fail:
     Exit Sub
    End Sub
    

    Those look like arrays, don't they? Well, they are. If you have ever programmed in VB, you might think of control arrays.
    You are mistaken.

      Set knoppenlijst(0, 0) = Bijschrift76
      Set knoppenlijst(1, 0) = Bijschrift77
      Set knoppenlijst(2, 0) = Bijschrift78
      Set knoppenlijst(3, 0) = Bijschrift79
      Set knoppenlijst(4, 0) = Bijschrift80
      ..snip..
    

    and so on, for each and every possible crate. And, because events need to be registered as well,
    Private Sub Bijschrift76_click(): Call kist(0, 0): End Sub
    Private Sub Bijschrift77_click(): Call kist(1, 0): End Sub
    Private Sub Bijschrift78_click(): Call kist(2, 0): End Sub
    Private Sub Bijschrift79_click(): Call kist(3, 0): End Sub
    Private Sub Bijschrift80_click(): Call kist(4, 0): End Sub
    ..snip..
    Private Sub Bijschrift76_DblClick(Cancel As Integer)
       Call kist_r(0, 0)
    End Sub
    

    Private Sub Bijschrift77_DblClick(Cancel As Integer)
    Call kist_r(1, 0)

    End Sub
    Private Sub Bijschrift78_DblClick(Cancel As Integer)
    Call kist_r(2, 0)
    End Sub
    ..snip..

    With about 80% filled, it takes about one and a half minute to come up, about fifteen seconds to switch to another row, and about five seconds for something to happen after you click one of the crates. Printing is done by turning all control elements' Visible property off except for the labels representing crates, printing the form, and turning the control elements back on.



  • (Meta: where'd the quote function go?!)

    Access is a WTF generator, even in the hands of a good programmer. (Not sure I was personally as "good" a programmer when I used Access five years ago than I am now, though.) I've written code like this.

    Private Sub Bijschrift76_click(): Call kist(0, 0): End Sub
    Private Sub Bijschrift77_click(): Call kist(1, 0): End Sub
    Private Sub Bijschrift78_click(): Call kist(2, 0): End Sub
    Private Sub Bijschrift79_click(): Call kist(3, 0): End Sub
    Private Sub Bijschrift80_click(): Call kist(4, 0): End Sub
    ..snip..
    Private Sub Bijschrift76_DblClick(Cancel As Integer)
    Call kist_r(0, 0)
    End Sub

    Private Sub Bijschrift77_DblClick(Cancel As Integer)
    Call kist_r(1, 0)

    End Sub
    Private Sub Bijschrift78_DblClick(Cancel As Integer)
    Call kist_r(2, 0)
    End Sub
    ..snip..

    When I wrote such code, I was trying to implement a Date Picker dialog box entirely with Access GUI widgets and VBA with no external libraries. I scratched my head and searched the Internets, but the only way to I could come up with to create a grid of 35 buttons was to add 35 button widgets called btn01, btn02, etc, and copy out 35 even handler functions into the VBA window. Eew.

    Any WTF doesn't Access have a build-in Date Picker to begin with? It has a bloody built-in "Calendar" icon! But if you want to put that icon on a button and assign that to a Date Picker dialog box, your two options are 1) a Microsoft-authored ActiveX control that may or may not be installed on your user's system, or 2) make your own dialog box.



  • @Brendan Kidwell said:

    (Meta: where'd the quote function go?!)

    Access is a WTF generator, even in the hands of a good programmer. (Not sure I was personally as "good" a programmer when I used Access five years ago than I am now, though.) I've written code like this.

    Private Sub Bijschrift76_click(): Call kist(0, 0): End Sub
    Private Sub Bijschrift77_click(): Call kist(1, 0): End Sub
    Private Sub Bijschrift78_click(): Call kist(2, 0): End Sub
    Private Sub Bijschrift79_click(): Call kist(3, 0): End Sub
    Private Sub Bijschrift80_click(): Call kist(4, 0): End Sub
    ..snip..
    Private Sub Bijschrift76_DblClick(Cancel As Integer)
    Call kist_r(0, 0)
    End Sub

    Private Sub Bijschrift77_DblClick(Cancel As Integer)
    Call kist_r(1, 0)

    End Sub
    Private Sub Bijschrift78_DblClick(Cancel As Integer)
    Call kist_r(2, 0)
    End Sub
    ..snip..

    When I wrote such code, I was trying to implement a Date Picker dialog box entirely with Access GUI widgets and VBA with no external libraries. I scratched my head and searched the Internets, but the only way to I could come up with to create a grid of 35 buttons was to add 35 button widgets called btn01, btn02, etc, and copy out 35 even handler functions into the VBA window. Eew.

    Any WTF doesn't Access have a build-in Date Picker to begin with? It has a bloody built-in "Calendar" icon! But if you want to put that icon on a button and assign that to a Date Picker dialog box, your two options are 1) a Microsoft-authored ActiveX control that may or may not be installed on your user's system, or 2) make your own dialog box.

     

    Maybe you couldn't find it, just like you cannot find the quote button, right in front of your face...

    I'm just sayin...



  • @MasterPlanSoftware said:

    Maybe you couldn't find it, just like you cannot find the quote button, right in front of your face...

    I'm just sayin...

     

    Ha. Okay, so I found the Quote button again. (I wish all forums just used NNTP protocol!) But I still don't know how to create 35 buttons in Access with a single event handler. Or better yet, create the buttons at run-time. Can you help with that? :^b 



  • @Brendan Kidwell said:

    @MasterPlanSoftware said:

    Maybe you couldn't find it, just like you cannot find the quote button, right in front of your face...

    I'm just sayin...

     

    Ha. Okay, so I found the Quote button again. (I wish all forums just used NNTP protocol!) But I still don't know how to create 35 buttons in Access with a single event handler. Or better yet, create the buttons at run-time. Can you help with that? :^b 

     

    http://office.microsoft.com/en-us/access/HP011210601033.aspx



  • @MasterPlanSoftware said:


    Would have been nice to have that 5 years ago when I was restricted by external forces to developing simultaneously for Access 97 and Access 2000. Apparently, over a decade after Access was first introduced, they finally included a Calendar dialog WITH Access. (This doc says it applies to Access 2003.)

     



  • Don't get me wrong with all my cynical comments here. I love Microsoft Access (and I'm a Windows hater). I've never found a desktop database app that is remotely as useful as Access 2000 for hacking data and prototyping data-bound forms.

    I'm still keeping an eye on OpenOffice.org Database and Kexi. I need to do some work in them and see how good they are. Has anyone used either of these two programs?



  • @Brendan Kidwell said:

    @MasterPlanSoftware said:

    http://office.microsoft.com/en-us/access/HP011210601033.aspx


    Would have been nice to have that 5 years ago when I was restricted by external forces to developing simultaneously for Access 97 and Access 2000. Apparently, over a decade after Access was first introduced, they finally included a Calendar dialog WITH Access. (This doc says it applies to Access 2003.)

     

    In Access 97 I would have used the ActiveX calendar control. Not sure why you were opposed to activex controls, but doesn't really matter, and this will be a bad place to argue the point.



  • @MasterPlanSoftware said:

    In Access 97 I would have used the ActiveX calendar control. Not sure why you were opposed to activex controls, but doesn't really matter, and this will be a bad place to argue the point.

     

    It didn't come packaged with Access 97, and I couldn't count on my users having it installed, nor did I want to deal with getting it installed on their desktops. ... and I'm done kvetching now. :^) 



  • @MasterPlanSoftware said:

    Maybe you couldn't find it, just like you cannot find the quote button, right in front of your face...

    I'm just sayin...

     

     

    You are a loser.  Get a life.



  • @amar said:

     

    @MasterPlanSoftware said:

    Maybe you couldn't find it, just like you cannot find the quote button, right in front of your face...

    I'm just sayin...

     

    You are a loser.  Get a life.

     

    I'm a loser? You opened an account just to post that, while the person I commented to got the joke, and took the humor just fine.

    Really, we don't need anymore trolls here. SpectateSwamp is more than enough.



  • @Brendan Kidwell said:

    I love Microsoft Access (and I'm a Windows hater).
     

    Access has some decent back-end stuff. The SQL engine (whatever it's called these days. Jet? SQLserverDesktopEdition?) works quite nicely. It's a shame that Access-the-interface is so utterly crappy. Why is it that after about 20 years or so of Office, it STILL can't remember window sizes/positions/modes? Or pop up a design window at a decent size? The graphical query designer invariably pops up with 80% empty space below, and 10% actually available for the table/field stuff.

    And don't get me started on the SQL mode of the query designer. People wouldn't complain so much about this forum editor if they had to use that monstrosity on a regular basis. 



  • I use Access for all sorts of RAD hacks. Right now it's moving some files across servers for me so I don't have to. 

    ^ My code doesn't look like that.

    My way: done today, no cost except for my time

    The right way: fill out a form, get it approved, get a development budget, hire some consultants, wait six months for deployment, test, re-test, roll it out, sign a support contract ...

    People pick up on this pretty quick. "We can spend a quarter million dollars or call Tim" it ain't Enterprise but if all you really need is a roll of duct tape its often pretty silly to buy the whole adhesive manufacturing plant.

    I make no apologies for being an Access hack ... 



  • @MarcB said:

    @Brendan Kidwell said:

    I love Microsoft Access (and I'm a Windows hater).
     

    Access has some decent back-end stuff. The SQL engine (whatever it's called these days. Jet? SQLserverDesktopEdition?) works quite nicely. It's a shame that Access-the-interface is so utterly crappy. Why is it that after about 20 years or so of Office, it STILL can't remember window sizes/positions/modes? Or pop up a design window at a decent size? The graphical query designer invariably pops up with 80% empty space below, and 10% actually available for the table/field stuff.

    And don't get me started on the SQL mode of the query designer. People wouldn't complain so much about this forum editor if they had to use that monstrosity on a regular basis. 

     

     Access as a backend is pretty pointless when you can use SQLExpress. Access has a horrible locking method, and you risk having stale data with concurrent users, because it batches updates and inserts.

     SQLExpress is a much better desktop engine, if you only need it as a backend, there is no point to using JET...Plus, you have to use ODBC or OLE to connect to Jet, which is a good bit slower than using a native SQL connection to SQLExpress.

    Finally, because SQLExpress is 100% compatible with SQL Server, when you outgrow the needs of a desktop engine, you don't have to go change the True/False fields to bits, and fix a few of the other arcane access oddities (Dates that contain hashes? etc)...

     



  • @Jonathan Holland said:

     Access as a backend is pretty pointless when you can use SQLExpress.
     

    You're confusing 'Access' with the database engine. Access is purely a graphical database front-end and horribly bad application development environment (in terms of userfriendliness of the GUI). Access natively used Jet as its backend engine, and could talk to any other engine you wanted, as long as there was an ODBC driver for it. Unless you specified otherwise, you'd doubleclick the .mdb and be talking to Jet in the background without configuring anything.

    @Jonathan Holland said:

    Access has a horrible locking method, and you risk having stale data with concurrent users, because it batches updates and inserts.

    For all the marketroid blather about multi-user support, it was essentially grafted onto Jet as a hack. Anyone doing anything serious in the way of development would opt to use a better engine than Jet. But for single-user purposes, Jet was quite nice.

    @Jonathan Holland said:

    you don't have to go change the True/False fields to bits

    You'd think ODBC would be smart enough to do that for you. After all, it was supposed to present a unified and consistent interface to all and any database types. But Openly Dead-Brain Crap was badly designed from the get-go, as with any early Microsoft protocols. Just enough to work in most situations, and screw anyone else who needed something more, until the v3.0 revision came out (remember, M$ never gets anything right until v3).


Log in to reply