Redeveloping and streamlining database developed by ignoramus

Topic

New Reply

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.

Reply | Quote

Replies

It appears you have a very ambitious project ahead. It might help to decide a couple of high-level issues before you plunge into the project (again!:o:).

What sort of connectivity do your WAN users have? Your description suggests they are some physical distance away, and are likely to have Internet connectivity to the data. Also will they all have Access installed on their PC. If not, one option might be to install your Access application using the runtime option, but if they are scattered across the country, or the globe, that can get to be a challenge. If Access or the run-time version are feasible then you might want to look at a hybrid app using a cloud-based backend. If not, you should probably consider using a different technology.

One comment regarding your desire to move to unbound forms. We find that there are a number of advantages to using bound forms, including such controls as subforms and subreports. Developing unbound forms and reports to achieve the same end typically require as much as 10 times the effort, and usually exhibit significantly slower performance. Unbound forms do have a place, but in the apps we have developed they represent 10% or less of the forms.

Reply | Quote

I agree with Wendell about bound forms. One of the main reason to use Access as a front end is because of the power of Access’s bound forms.

Using an Access as the back end with linked tables over a WAN is generally never a good solution. If you as fortunately to have all your using on a 1 GIG WAN then go for it.

If you are not lucky enough to hav a 1 GIG WAN pipe then you best bet is to do with an SQL Server Back end. I would so much processing as possible tih T-SQL/Stored Procedures.

If it were my project I woudl use an Acess desktop app and Terminal Services.

If you already have a fully functioning spilt Access Desktop App that needs to be access remotely then I would go with a Terminal Server and RDP clients You could have your database up and running with little or no changes.

I have clients with 25-30 or more user all running my app on a single Windows 2008 R2 server running Terminal Services. Even the users in the Office will Run an RDP sessions because it is faster than running the Access front end on the local PC. They also run the full office suite. They have full desktop versions of Outlook, Word, and Excel. Notig is installed on the used device. I even have user that use tablets like iPad and Kindle. I use my Kindle tablet to run Access apps in a terminal server in my own office.

FIY: I have 35+ concurrent users running my Access App with an Access back end that is also on the terminal server. That mean no network speed issues. Since everything is local for all 35+ users it is really fast. I can only match that performance over a LAN with a SQL Server back end!

If you don’t now any Web Programming Language, like ASP.NET or PHP, since you already know Access then I would go with a terminal server. It allows you to leverage all the power of Access as a desktop app and still run it over the internet using an RDP Client session.

If you are having to create the app from scratch and want it web browser based then I would used VB.net/ASP.net and and SQL server to build a web app. I would recommend looking into IronSpeed ( http://www.ironspeed.com – check out the demo) to help you develop the app quickly.

FWIW:
Even with the cost of purchasing a Server to be used as a Terminal server I am still able to create a solution that is cheaper in the long run the developing web apps. And much more powerful!

Reply | Quote

If cost is an issue you may not be able to purchase the TS licenses. This will restrict you to using a SQL database, either MS or open source and PHP / ASP.net.
Either way it’s not going to be cheap.

cheers, Paul

Reply | Quote

Boyd and Paul both make excellent points – and since management is unwilling to extend the SharePoint license, it appears you may be cost contrained. I’m not a big fan of SharePoint based applications because of the limitiations it imposes, and perhaps a Terminal Services solution would be less expensive. But unless you develop a runtime application, all of the users will need a license for Access. In any event, if you have questions about what has been said so far, we’ll do our best to give advice.

Reply | Quote

Thank you for your input, in my opinion the best compromise between cost, scalability and ease of access would be an independent domain with a MySQL back end and PHP front end. Thus it would even be viable to have the claims compiled authorised and approved online, but this is not in line with the company IT policies. Unfortunately what is in line with policies costs money and getting that approved is a nightmare. To have all employees compile their claims online we are looking at around 25 000 licenses…..

Reply | Quote

Definitely a SQL backend and PHP/ASP.net. Very low cost for that number of users, plus it’s robust and easy to recover.

cheers, Paul

Reply | Quote

Well the decision was basically made for me; I have to make do with what is currently available to me; so in my opinion the best option is SharePoint back end with Access front end. Due to the licensing cost, I have to make do with SharePoint Foundation so I have to manually create the SharePoint lists (which I suppose is not really that big an issue).

So with the baseline set, I will be starting to ask more specific questions on separate threads

Reply | Quote

If possible I would go with an MS SSl Server Express (FRee versions) back end to avoid the Sharepoint issues.

Reply | Quote

I agree with what everyone has said so far. In addition, I would probably step back and deal with requirements first. This is a very messy part of a technical solution design, especially with databases, but a friend of mine told me that the three hours you spend doodling on paper saves you 30 hours of frustration later. A lot of techs and non techs want to dive in and “do stuff” and not bother with paper, but with data systems, paper is step one.

I would get some paper, yes paper, (do not do this on a computer) and write down what it is you’re trying to accomplish. My number one question would be what is it that the users need to seek, not necessarily what they want to see. Users most times have no idea what they want until they see some form of final product. Sometimes users want to see something one time in the 10 year useage cycle of the database that can cause you a lot of grief and frustration only to see that they glance at it for about five seconds.

The things that you are talking about such as “dead weight” is part of the collection of “stuff” that you are gluing together that would probably be better organized if you went to the public library inside one of the study rooms and with paper and ballpoint pen, write down what you have to do and then start drawing all the relationships on paper. This does not mean that you’re going to follow your pen scratchings, but chances are you going to feel a lot more organized.

There is a fancy term called “normalizing” and before you get into any of that or VBA stuff or the SQL server stuff or SSL sockets and all of that, you have to decide what it is you want in a very discreet, definite, and restrictive way. Once you have made that decision, you have to fight feature creep, and keep yourself inside of your requirement otherwise you won’t get it done.

Reply | Quote