The aim of this tread is to provide background information for guidance I will be asking in individual threads in the near future.
First off, I am knowledgeable enough on access and VBA to know that what I know is dangerous.
Secondly some background on the app I am currently developing: It was initially excel based as requirements changed it was redeveloped from scratch in excel four times, then the dataset became too large to work efficiently in excel so I developed an access app. This was the first ever access database I developed, previously I only used databases developed by others.
It was a local hosted database containing mainly tables and most actions was carried out manually. This grew to a bound form based database that only I used and most actions was still mainly manual. Then the requirements changed and the database had to be shared and fully automated. At this stage I opted to redevelop from scratch, the idea was that users would access a SharePoint backend with an Access frontend giving users the ability to connect via any internet connection. After redeveloping the complete app, I only discovered that our SharePoint license is not enterprize and that the planned option would not be possible as upgrading the SharePoint license is not an option.
That brings me to my current situation: I have a database with 30 web ready tables and a bunch of partially automated forms (mostly macro driven) that is currently shared with three LAN based co-workers and must now be expanded to accommodate another thirty WAN based users, it must also be fully automated and secured so that users cannot access tables, ribbon etc. and streamlined in general.
Because the forms are mostly macro driven, I have created some forms that is only used by the macros to update records, this adds dead weight to my file and I want to replace this with code to update tables directly.
The purpose of the database is to audit expense claims and analyse the data for trends etc.
Data is received in the form of expense claims in pdf format that is then manually recaptured. Going forward, I will be receiving claims in both pdf and excel formats.
I have two main tables tblEmployeeRegister and tblClaim. The second table, tblClaim has a subtable tblLinethat has a subtable tblInvoice that has a subtable tblItem.
I will be starting from the beginning and work through the app chronologically.
In summary, I need to:
• Replace Macros and macro forms with code updating tables and streamline cumbersome procedures.
• Move from bound forms to unbound forms.
• Create a full audit trail.
• Ad functionality to import single records from Excel form.
• Cut dead weight from file.
• Add automatic email updates to claimants at certain points in the procedure.
The aim of the assistance I will be asking is not for you guys (and gals) to write the code and I copy paste it, so my questions will be in the form of what I currently have, my attempt at what I want and then asking for criticism and suggestions on best practise to attain what I want. I do not want to know only the “how”, but also the “why” so that this project is a learning experience for me and the final product is a best practise solution that even an experienced developer can be proud of.
I do not believe that there is such a thing as a stupid question, so I will be asking a lot of stupid questions!
Thank You to all of you that will be assisting me!
Should there be any more detail about the database structure etc that you feel would be relevant, please ask away…….I will also be adding relevant info as I become aware of the need for it.