11

Would you recommend working with multiple programmers on an MS Access application?

One of our MS Access application has grown to the point where the number of changes (bug fixes) and new features can no longer be handled by one programmer in the requested time frame.

We are trying to introduce version control using the undocumented SaveAsText and LoadFromText procedures in VBA to make collaboration on this application possible. Unfortunately we have already run into problems loading modified forms and reports back into Access as a checksum is stored in every form text file.

Before putting time into building an import/export application to compile text files into an Access database, we would like to hear your recommendations.

Marien
  • 171
  • 1
  • 7
  • To solve your specific forms problems (checksum issues), please write some code to make sure that, at export time, these checksums are deleted. This will solve your problem. Other data can be deleted. You can easily find which one by comparing different "exported" versions of the same form. – Philippe Grondier Oct 30 '08 at 16:45

6 Answers6

20

I think you should avoid this path at all cost, and try and persuade management into redevelopment.

It's a bitter pill to swallow, but this is going to need to be redeveloped sooner or later, and you are just saving them time and money.

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • 12
    Sorry but I don't agree. Access is really really quick and cheap to build a client interface for database management. Of course, development with Access needs to be tailored and standardised, but I cannot think of a cheaper tool to build an app! – Philippe Grondier Oct 30 '08 at 19:03
  • 2
    I agree with the message in this answer: Access is a trap. But this answer is to exactly to the point. – Yarik Nov 03 '08 at 09:28
  • 1
    Access development is perhaps 10 times faster than .Net or similar methods, so a first version in Access may well be a valid and financially reasonable choice, depending on the size and target of the project. – iDevlop Nov 18 '15 at 10:38
14

We were using Microsoft's own version control add-in for MS Access 2000/2002/2003 for about 5 years now, and I can't remember a single serious problem. Usability of this add-in barely deserves a "B", but it must be much, much more convenient than fiddling with any ad-hoc method involving manual or semi-manual exporting/importing of Access forms, modules, etc.

We were using VSS as a version control system all the time. No problems whatsoever. However, if you have some good reasons to avoid VSS, you may have some options:

  • The version control add-in that we were using does not require VSS. Theoretically it can be used with any version control system that implements Microsoft Source Code Control Interface (MSCCI). For example, when we had to let somebody work on this project remotely, we used SourceOffsite by SourceGear. Access version control add-in worked with this third-party product fairly well (not without some quirks, but well enough). So, if your favorite version control system complies with MSCCI, you could try to use it.

  • Now that Microsoft has this Team Foundation thingy, apparently there are other options to be used to integrate MS Access with version control. We did not explore this path, though. This article may be a good start for exploring it.

Hope this would be of some help. :-)

P.S. I am not a big fan of MS Access. In fact, I rather hate it as a platform for a user front-end. If I had a choice, I would run away from it yesterday. :-) However, I must admit that existence of this version control add-in is one of the few things that makes maintenance of our old Access+SQLServer project more or less tolerable. :-))

Yarik
  • 2,339
  • 2
  • 24
  • 30
9

In addition to what I already said here, I should add that the whole system works very well. The comparison process takes less than 30 minutes a week, for a team of 3 programmers. So let's describe it a little bit.

We have basically 2 versions of our Access program:

The "Developer's version", with all the stuff in it.

We each begin to work with an identical version of our developer's edition. As each one modifies or add parts of the code, we have to run some comparison routine on a regular basis. To do so, we have an object-export routine to a common "comparison" folder. An object (module for example) is exported as a text file (saveAsText command, do not work with tables, see infra), it will be compared to the existing equivalent text files in the folder. If files are identical, there is no file exported. If files are different, the new module is exported with the developer's name as an addition to the file name (if modQueries.txt exists, then modQueries_philippe.txt is created...). Of course if there is no equivalent .txt file in the folder, it will be created at first export.

At the end of the period, we would get in our folder the following files

  1. modQueries.txt, being the first "original", last common version of the module
  2. modQueries_Philippe.txt, with Philippe's modifications
  3. modQueries_Denise.txt, with Denise's modifications
  4. As the module was not modified by other developers, their export did not lead to the creation of a specific modQueries_developersName.txt file
  5. If for any reasons Denise exported many times her module, only the last version is in the comparison folder.

We can then compare (with a "text file" comparer) the different versions and create the "updated" version of the module. We have a screen giving us the number of objects in the comparison folder, number of version for each object, and it is even possible to open the file comparer directly from the developer's interface (We use "File Compare Tool" which has a command-line mode and can then be started directly from Access).

The forms compare issue is quite special, as one of our rules is to have no specific code in our forms (please see here for more details). Forms are then only for display, so usually we do not even compare them. We just make sure that each one of them is updated by only one person (which is quite logical).

The table compare issue (we have local tables) can be only made between mdb files. As we export one text file per module, we also export one mdb file per table. We have a small routine allowing us to identify table differences at the structure level or at the record level.

After each comparison procedure, a subroutine will use all the objects available ini the comparison folder and create a whole new clean mdb file from scratch. This is the new developer's version. Every developer can then copy it on his computer and continue his work.

Developer's versions do not have numbers, but contains last client version number.

The client version, with limited stuff, automatically distributed to users

Each developer has the possibility to build a "client" mdb for final users. This mdb is created from scratch, in a way quite similar to our developer's version, but not all objects are exported. Some specific switches are turned off (special keys, access to code, etc). This mdb holds a version number as a property. The version number is used to build the name of the mdb file.

At production time, this mdb file is zipped and placed in a specific "distribution" folder. Each time a user starts the app, it will automatically check this folder to see if a new version is available. If yes, the client mdb file is updated from the distribution folder, and the app is restarted.

This distribution folder is replicated at night time with our overseas agencies. Users abroad will then be able to install the new version on the following day.

Community
  • 1
  • 1
Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
6

Following the direction provided by Yarik we settled on continuing developing in Access using the Access Add-in Source Code Control, the SVN SCC API Plugin by PushOk Software and Subversion. This stack provides us with seamless Access integration, full-backup and restore and an open version control system.

We had to install a hotfix to Access 2003 and make sure the default database file type matched our database file type to make it work.

We will continue to update this answer with our findings.

Community
  • 1
  • 1
Marien
  • 171
  • 1
  • 7
1

Have look at this thread:

How do you use version control with Access development?

Community
  • 1
  • 1
DJ.
  • 16,045
  • 3
  • 42
  • 46
  • Those answers use saveastext and loadfromtext. They have the problem that you cannot merge two exports and use them to generate a database. – Jauco Oct 29 '08 at 16:15
0

Sounds like a terribly painful way to do team development. If you have any options for porting to another environment like VS2008 that would be my recommendation.

There is no easy way to work on Access as a team and even version control might be a bit tricky.

Vincent Ramdhanie
  • 102,349
  • 23
  • 137
  • 192