Can Excel be a front end to the data base ?

[newexcler]

Hey experts
In our org, we have a big problem people enter all kinds of data wrong like name with spaces, name in short and ID wrong

Until we figure out the solution, we are non profit, I am asked to come with a immediate solution and long term which I suggested is front end ( another story, I might take advise in another thread)

For immediate solution,
without disrupting the workflow and stress users who need not be trained
Can I force the excel to run from sharepoint (so can be shared) and this sheet is now integrated with postgres data base then each column is data validated to table so auto refreshed on scheduled and autpoulate rest of the data relevant based on selecting the ID or name .. and only add event specific information ?

This way, I can ensure data integrity and database sanity ? Your advise is appreciated.
Thanks a ton.

 

[arthurbr]

Hi
before using XL as a front end perhaps watch this video about spreadsheets and their limitations as databases ( don't worry, no inadmissible things in there

 

[Jon von der Heyden]

I believe, as short answers, yes it can. I have built many front-ends to databases using Excel. Mostly they have served as tactical solutions, or as proof of concept, in anticipation of more sophisticated and managed strategic solution.

Few points:

1. Use ADO (VBA) to connect and communicate to the database. A simple way is to use ADO to send SQL commands to the DB (e.g. Select, Insert, Update, Delete etc).
2. If you are using a database, it might be possible that the Excel front-end need only be operated in Read-Only mode. That means you could distribute a copy of the matser file to users (hence no sharing necessary).
3. Sharing still isn't a problem, it should work just fine if on SharePoint, as long as your admins haven't restricted macro-enebaled workbooks.
4. I don't so much like scheduled refresh and data submission (personal preference). Rather I suggest that you use Excel events, such as when a user adds a new record to a table, then trigger the procedure that will send the data to the DB. Refresh the data when the workboook is opened, whenever a user changes something (after updating DB) and perhaps give the users an option to refresh themselves.

 

[newexcler]

Thank you @Jon von der Heyden your stamp of approval means a lot. I can focus my energy at my free time to develop a working prototype, Please may I kindly request for any reference links or material I can think of ?

 

[newexcler]

@arthurbr Thank you for the message, I will definitely look into this. My other solution in mind is Python based Flask as front end but all this new to me as well, but immediate goal is to contain rubbish destroying the database and

 

[Jon von der Heyden]

Some places to get you started:

MS Website - see the side bar for other chapters too:

ADO Introduction - ActiveX Data Objects (ADO)

ADO Overview and Benefits

docs.microsoft.com

Using ADO with Microsoft Visual Basic - ActiveX Data Objects (ADO)

Using ADO with Microsoft Visual Basic and Visual Basic for Applications

docs.microsoft.com

And I wrote an article for MS Access many years ago, but similar can be done for other DB's:

Create, Modify, Delete and Run Queries in MS Access

A handful of useful Excel VBA routines to execute and manage your database queries.

excelevolution.com

^Note references at the end of that article too.

Finally, there are plenty of forum members here who know and understand what you are looking to do, so I suggest you just get stuck in and ask questions along the way.

Good luck!

 

[newexcler]

Thanks a lot Jon
Great help