Can Excel be a front end to the data base ?

newexcler

New Member
Joined
Nov 15, 2015
Messages
39
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,869
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

newexcler

New Member
Joined
Nov 15, 2015
Messages
39
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

New Member
Joined
Nov 15, 2015
Messages
39

ADVERTISEMENT

@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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,869
Office Version
  1. 365
Platform
  1. Windows
Some places to get you started:

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

And I wrote an article for MS Access many years ago, but similar can be done for other DB's:
^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! (y)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top