Access and VBA (1 Viewer)

[PeteB]

Hi Guys
I am currently building front ends to various databases for a local charity, on a voluntary basis. The databases are in Access and I have used both macros and VBA in the construction of the front ends. Part of the exercise involves convincing a couple on the committee that the solution is the right one. These two gents are experienced in the IT arena but I'm not sure their comments are entirely valid.
These guys hold the view that:-

• [FONT=&quot]Access is a dying application and should therefore be avoided;[/FONT]
• [FONT=&quot]VBA is not a ‘clean’ language, is no longer supported by Microsoft and is susceptible to corruption and may corrupt data held within the databases.[/FONT]

I would appreciate advice from anyone on the forum who can put a justified counter argument.
[FONT=&quot]Although I have been an Access user for some time I am an absolute amateur compared to you guys so would very much appreciate your input.[/FONT]

 

[June7]

Point1: Have heard this claim for over 10 yrs but MS continues to issue new versions.

Point2: Yes, corruption has been known to happen but usually the cause is improper design and management. A multi-user db should be split and each user run their own copy of frontend. Db early in development is more susceptible to corruption of objects. I had to rebuild some forms a few times. Don't remember ever experiencing data corruption.

 

[Ranman256]

You are correct...create a split Access database and you can run a corporation with it.
It IS the way to go.
It is not dying for the little guys with a PC. Corporations with giant budgets don't need it but you can do a lot with it for your charity.

 

[Minty]

Have a read here http://www.enterprisefeatures.com/a...d-of-microsoft-access-is-largely-unjustified/

Many times access has been "on the way out" , "is crap" etc. etc.
The article highlights why many "IT depts" think so. VBA doesn't corrupt data. Poor programming and design do. VBA is heavily in use in Excel automation and Access uses pretty much the same library's, so it isn't going anywhere.

The only think Access isn't good at is providing remote WEB based access. You really have to look at a remote desktop or cleverly designed Azure access application.

As to it dying, I can't see that in the near future, Access is still used and supported by many many users.

 

[isladogs]

Agree with previous comments - a split database is ESSENTIAL
BUT also:
a) if you have many simultaneous users, your split database backend file should be in SQL Server (or similar) for improved security and stability.
The Express version of SQL Server is free and will be more than sufficient for your needs
b) do NOT allow any users to run your database on a wireless connection as there is a high risk of corruption when connections are lost

 

[sonic8]

• [FONT=&quot]Access is a dying application and should therefore be avoided;[/FONT]

Access 2016/O365 received regular updates during 2018 which amount to more than the updates released originally in A2016 RTM.
Access 2019 has been released as RTM version in Oct.2018, bringing most of the above mentioned updates to everybody not on O365.

Is a major update within the last 6 months an indicator for a dying application? - I don't think so.

• [FONT=&quot]VBA is not a ‘clean’ language, is no longer supported by Microsoft and is susceptible to corruption and may corrupt data held within the databases[/FONT]

Rubbish!
Agreed, VBA is not a very "modern" language and there have been no significant updates in the last 15 years to the language itself.

VBA does never corrupt data in the database on its own! Poor programming in any language can corrupt data, though.

Access (Jet/Ace) databases are not particularly resilient against corruption. - That is completely unrelated to VBA!

If this is of concern, you should use a SQL-Server (or similar) backend database. You can still build your frontend application with Access.

 

[JHB]

..
These guys hold the view that:-

• [FONT=&quot]Access is a dying application and should therefore be avoided;[/FONT]
• [FONT=&quot]VBA is not a ‘clean’ language, is no longer supported by Microsoft and is susceptible to corruption and may corrupt data held within the databases[/FONT]

Ask them why they think so and also for evidence of their claims.
What do they propose to use instead and if they able to do it, (or what they think it costs to get done if they are not willing/able to).
One can always be criticized, but are those who criticize being able to give / make another solution!

 

[PeteB]

A big thank you to JHB, sonic8, isladogs, Minty, Ranman 256, and June 7. You've all been very helpful. Thanks again.
PeteB

 

[The_Doc_Man]

Add to this that the U.S. Navy uses an Access FE/SQL Server BE to run a major medical records management application. It was still in place before I retired and served literally tens of thousands of sailors.

As to "dying application" ? I've been around Access since Access 2.0 and it was still going strong in 2016, almost 20 years later. At least three major attempts were made to replace Access, but they all failed. The IT attitude to Access is because their head is in the clouds (or perhaps is anatomically docked in a warm, dark place?) They shy away from it because they don't understand it.

So VBA isn't a "clean" language? Are they fans of C variants? Just remind them that C was created as a bunch of Assembly Language macros at Bell Labs running on a PDP-11 computer. Don't get any more unclean than that!

 

[CJ_London]

IT departments don't support Access - agreed - they don't want to get involved with correcting designs or messing around with the code.

IT departments say they support Excel - but ask them to get involved with designing a spreadsheet, writing a macro - I don't think so.

The limit to the level of support they provide is that the application will open and you have the necessary rights to folders.

Access provides a means to write an application - which is why it should be split and each user should have their own copy of the front end (as they each have their own copy of excel).

I agree with the other comments. Here are a couple of links about vba which may be of interest

https://answers.microsoft.com/en-us...a/bd437e03-00e4-4086-958a-fee61eadb901?auth=1
https://www.quora.com/Will-VBA-be-obsolete-in-a-few-years-time-from-now

would be interested to know what your two gents consider to be a suitable alternative given their expertise

 

[MushroomKing]

One tip with a split database to avoid most corruption...

"Don't Delete Data".

Use append data to another table or something.
Most corruptions happen when a record is deleted from a table that people use at the same time.

Concerning the front-end. I always set it up like this and never had a problem anymore:

1. I use a "OPEN DATABASE.accdb" file for all users to use that only does one thing, start a batch file.
2. The batch file copies the Main front end file (edited in Access 2010!!!!) and copies it to another location. The new file is saved as: username+year+timestamp.accdb
3. Then from the batch file opens the newly copied file for each user.

This way everybody has their own front end and is pretty stable.