excel: front end to sql server

0
votes

I am currently working on a project which has basically 2 users who will be the end users. Till now, we were using access as the front end but since the code was designed by an amateur, it frequently runs into some kind of memory problems and stops functioning on a regular basis. Now we have two options: redesign the access front end from scratch or probably try and build an excel interface.

All the data currently is stored in the sql server and we were only using access to input some text into it which was stored in access locally.

Please do let me know if you have any suggestions about creating excel as the front end. The amount of data stored in the sql server is about 6 table and 2 of them are really huge arnd 150,000 rows and 350 columns. these tables can be linked by unique keys. but ultimately for every unique key we currently have 1 row for it and end up writing comments on it and store and update the comments for further use. Ultimately the end user selects only about 300 rows and ends up editing it and closes it and moves to another 300 rows.

Also, since I am the only person who will be handling this, and the time is also very limited I am looking for a solution which will not be taking too much of my time to maintain the database later.

1
sql-serverexcelms-access
Why are you considering moving to excel? Although I would never advocate using Access as a front end, it's infinitely better suited to the task of editing data in a database than Excel is. Seems to me like you should probably explain what's going on with your Access front end and see what can be done about it. – NotMe
hi Chris, its been throwing a bunch of errors, like memory errors and the database has got corrupted. I have tried a variety of ways of fixing it like exporting it into a new database and re creating it which did not help, also tried compact repair, which ends up working for some time but then a day later, it stops working again. – codemacha
Looking at your other questions, I think I see what's going on. It appears that you know how to work with Excel and believe it's the hammer to fix this problem. It's not and you are just going to make things worse. Get a copy of Visual Studio 2012. It looks like you know VBA, VB.Net is pretty close to that. Take the time and learn it. Both you, and your client, will be much happier in the end. – NotMe
It has started behaving very odd, one time, almost half of the data got corrupted and as soon as I tried compact and repair, the I could see the data again in the tables. Some memory error, like There isn't enough disk space or memory error .mdb error was showing up – codemacha
Finally, you mention the "database has got corrupted". Is the database not really in SQL Server? – NotMe

1 Answers

1
votes

How is the Access database accessed? i.e. is it one file on a network share that is then used by both users? If so, that is your issue-- especially if both users try to use it simultaneously. That is almost always the cause of corruption on a mdb file. I absolutely agree with Chris that using Excel as a front end is a very bad way to go, however I don't know as you need to go all out with VB.Net. Access is very capable for simple applications when it's implemented correctly.

If multiple people are accessing an Access database, it needs to be "split" i.e. have one mdb file with all the tables and data (the backend) and another file with the user interface (the front end). The backend is placed on a network server or common location (but not dropbox) and is never directly opened. The front end is placed on each user's computer and is linked to the backend. If the database is not currently split, there is a wizard available that can help you do that.

You should be able to also link the same front end to anything in the SQL Server.