hello,
I am not a professional programmer but always enjoyed using VBA. About five years ago I started to develop a database for the sales office where I work and then it started to grow to other departments of our company, maybe a common story. It's now an Access FE and SQL server BE, with about 60 users, each user has a local copy of the accde file, many work remotely through terminal server services. The FE has a size of about 45 Mb, made by around 140 forms. Every now and then I make updates and additions because it is rather appreciated in our organization and it also pulls many data from our MRP and Management System. Everything is working smooth and fine, the SQL back end is well mantained so there is no danger of data loss.
My concerns are:
I have consulted our supplier of the MRP and management software but they obviously discourage in-house development, and say we should buy this and that, etc. The point is that I find MS Access incredibly flexible and practical and being also one of the users I enjoy improving it! My daily work has nothing to do with programming, I am an engineer managing a sales and engineering team but sometimes I dedicate a little free time on Access development.
I appreciate any comment and opinion on this.
Andy
I am not a professional programmer but always enjoyed using VBA. About five years ago I started to develop a database for the sales office where I work and then it started to grow to other departments of our company, maybe a common story. It's now an Access FE and SQL server BE, with about 60 users, each user has a local copy of the accde file, many work remotely through terminal server services. The FE has a size of about 45 Mb, made by around 140 forms. Every now and then I make updates and additions because it is rather appreciated in our organization and it also pulls many data from our MRP and Management System. Everything is working smooth and fine, the SQL back end is well mantained so there is no danger of data loss.
My concerns are:
- is there a practical limit on the number of forms or size of the accde?
The FE was about 20 Mb 3 years ago but it later grew to about 40Mb because of the additions of forms, modules, code, etc.
what is best practice to keep the FE lean? For instance I normally leave the "auto compact on close" turned on, but I think it makes little sense as the date are all on SQL except some local tables.
- it has happened few times that a user ends us with a corrupt accde but in that case he/she simply delete the local copy and download again the latest accde. We have a simple batch file for automatic updates from a common directory.
- is this a common story? the tool is maybe becoming now "business-critical" so I wonder if it makes sense to look for a different technology. Honestly I don't think so.
I have consulted our supplier of the MRP and management software but they obviously discourage in-house development, and say we should buy this and that, etc. The point is that I find MS Access incredibly flexible and practical and being also one of the users I enjoy improving it! My daily work has nothing to do with programming, I am an engineer managing a sales and engineering team but sometimes I dedicate a little free time on Access development.
I appreciate any comment and opinion on this.
Andy