What version to go for ?

[BeeJayEff]

Hi, I'm new here so please point me in the right direction if this isn't the right place to ask this question or it's been answered elsewhere.

A small company want me to develop an Access db for them. I only have familiarity with Access 2003 on my own PC and have thrown together a proof-of-concept with that (currently involving some 20 tables and a dozen or so Forms) to run on their network of about six users. They already have Access 2007 and have bought into the idea.

One feature absent from 2003 that I know will definitely be needed is linking from records to external objects (jpeg images) - rather than keeping images as bmps within the db. I also think that Split Forms might be very useful. Rather than just taking the learning curve to Access 2007, I'd probably prefer to jump straight to either 2010 or 2013. I envisage a traditional db app rather than a Web App (and Sharepoint is not in use). 2013 seems to be totally focussed on Web Apps - does that mean I should just go for 2010 ? What would I lose apart from a couple of years future-proofing ? What do you experts think ?

 

[Galaxiom]

I also think that Split Forms might be very useful.

Give Split Forms a big miss. Their object model is incomplete so they cannot be fully controlled with VBA.

Better to stick with building the same thing using subforms.

 

[shadow9449]

I'm not sure why you say that objects in Access 2003 can't be linked to external objects. I store the path to objects as data in a table all the time and then a bit of code can open the object.

SHADOW

 

[GinaWhipp]

What version does the Company have? That is the version you want to develop in. And if they have 2010 then be careful because if they upgrade some things were removed, such as...

http://technet.microsoft.com/en-us/library/cc178954(v=office.15).aspx

 

[BeeJayEff]

Thanks for the responses. Interesting to hear about potential problems with split forms - I had thought that they would save me a load of coding. And that's the same reason why I want to use better image handling. As I say, the company is currently using 2007. I guess I'm reluctant to stick with that as I understand that MS are now removing support for 2003 and so I can expect that support for 2007 will be removed in a few years time. And my learning curve will be pretty much the same whichever version we go for. 2013 seems unattractive, so maybe 2010 is the answer ?

 

[Lightwave]

Bee Jay Eff - stay well clear of storing jpgs within the database - Its really not a good idea. It leads to massive bloat very quickly. Stick to just keeping text and numbers in databases and in all likely hood you'll never reach the database size limits.

Images within Access is the equivalent to digital zoom on camera's - its a feature that is not a feature.

I tend to design in the lowest common denominator and port forward because you cannot port back.

So my advice is stick with 2003 until you have no choice and then move forward as and when you are required and when you are happy test on 2007 and 2010 machines.

I just use the same concept as Shaddow to link images and the real advantage it is incredibly scaleable with no bloat.

 

[BeeJayEff]

Hi Lightwave, yes I understand the problem with images bloating the db - that's one main reason for moving on from 2003. The other is that the business already has 2007 installed anyway (but unused) so I have to go to 2007 at least. As I have no experience of 2007 anyway, I'm wondering whether I should just jump straight to 2010 or 2013. The Macro Designer and Expresson Bulder seem to be the main new things in 2010 from 2007. The business is happy to buy a new version if I recommend it.

 

[Lightwave]

Sorry misinterpreted the store jpgs in a file forget my whitterings.

But its is very easy to link to any outside files in Access 2003. I don't even distinguish between types of file I make the link and the OS decides the application it should be pulled up in.

Firstly put the following code in a 03 module..

Option Compare Database
Option Explicit

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Public Function bimShellOpenFile(strFilePath As String, lngShowCmd As Long) As Long

' Opens the file passed by calling windows
' the file should be opened into the app associated with the files type

Dim r As Long
Dim hwnd As Long

r = ShellExecute(hwnd, "open", strFilePath, "", "", lngShowCmd)

bimShellOpenFile = r

End Function

Then you can code up any button in a form to call bimShellOpenFile function.
Here I give an example with directory and file given as static variables but you could reference them to a field in whatever form.

    Dim Retval As Variant
    Dim Directory As String
    Dim File As String

    directory = "C:\data\"
    file = "Example.jpg"

    path = directory & file

    retval = bimShellOpenFile(path, vbNormalFocus) ' vbNormalFocus  True

This code works for Access 07 as well

 

[GinaWhipp]

You always code in the same version or, if the same is unavailable, a version lower than the Client has. This guarantees you won't have issues with them running on their machines.

You will not be able to utilize the *cool* stuff in 2010 or 2013 that are not in 2007 *unless* you are going to also distribute with the runtime. 99% of the code will upgrade with no problem, especially, if you stay clear of the Wizards. Your only learning curve will be playing *Where's Waldo?* when you try to find actions on the Ribbon. (And this will also happen between 2007 and 2010 where they moved actions *again*.)

So, in my mind, your choice will be stay with what you have and let them stay wher they are or everyone go to Access 2013.

 

[BeeJayEff]

Thanks again to all. I don't really think that sticking with 2003 is an option, as that means I won't be able to modify the db on their system and then take it back to mine. I'm now leaning towards moving mine up to 2007 and staying with that.

 

[GinaWhipp]

Hmm, valid point. I do make it a habit of only developing on my machine as it's where the *master* is stored but you have a valid point if you plan to do modification on site.

 

[BeeJayEff]

Aaaaaaarrrggggggghhhhhh ... having decided to go for Access 2007, I cannot find it available anywhere, either as an upgrade or as a new product. Looks like 2010 might be the way to go ... unless anyone can point me at a source for 2007 ?

 

[Galaxiom]

MS stops selling old editions pretty soon after newer ones become available.

However you can by an "Open Licence". These can be downgraded to any equivalent past version. They are mainly aimed at corporations so they are expensive.