locked
Using ASP.net to Create Browser-based Interface for an Access 2007 Database? RRS feed

  • Question

  • User274198738 posted

    Hi all...

    I've been trying to find out if asp.net is overkill for this particular job. I don't know asp.net but am willing to learn if it'll let me do what I need to do. Anyway, we have a small Access 2007 database (about 6MB) that sits on a Novell share drive. We currently use a shortcut to access it. Only about 5 of us use this database to keep track of projects. We can open new projects, open/edit current projects, and create a report based on active projects. Even for a such a small db, performance over VPN is horrible. The database is practically unusable. We got much better performance when the db was Access 2003 and was about 40MB in size which really doesn't make sense to me.

    Basically I'm hoping that creating a browser based interface to the db would increase performance. If I can create an interface that mimics the db interface, that would be great. We use some drop down menus for assigning projects to project managers and changing project statuses (active, cancelled, completed, etc) but otherwise everything else is text entry (start/end dates, implementation dates, and a large text box for adding updates).

    Would this be difficult to do? Keep in mind the db just sits on a share drive and we don't have any other options. We can't move this to a website or run any type of webserver. It's got to sit on the share drive. And using terminal services or remote desktop aren't options either. I'm just trying to come up with ways to make performance better and this seems to be the most viable way of doing so.

    We all use XP Pro and we all have Office 2007 installed on our laptops.

    TIA!

    Wednesday, November 5, 2008 10:27 AM

Answers

  • User-1573490007 posted

    SQL Server Compact Edition's runtime CAN be installed with an installer if you want... however, all it is is 6 DLLs (or something) that you need to ship with your app in the same directory as all your other DLLs... it runs in the same "process" as the app that executes it. Its not a full blown server like SQL Server Express. It works very much so like Access really. Just, better in every ways, shape and form (and it supports the ADO.NET Entity Framework, so you can use that to query it instead of raw SQL!). You could ship your app as just a directory with the exe and dependencies, and a configuration file, instruct people to install .NET 3.5 SP1, edit the app.config if its required, and you're good to go.

    To make the app, just use Visual C# Express 2008 SP1. Depending on how long you have to learn, you can use either a very typical Windows Form application, or a Windows Presentation Foundation app.

    Then for the rest, you'll have to look at http://windowsclient.net/ (you can use the same login name and password as you used to login here)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 5, 2008 2:23 PM

All replies

  • User-1573490007 posted

    yeah over VPN performance would be bad, since access is a file based database, and the files data has to go through the pipe.

    Honestly, the most cost effective solution for you is to allow people to use RDP (Remote Desktop) or VNC over VPN, and access the app over that. You know for sure that will work, setup time is minimal, you don't need to train anyone or maintain anything beyond what you have.

    I'd only make it a web app if you had new feature requirements, then may as well start anew in something that will more easily suit the requirements. If what you have work, I'd rather just find a workaround to your issues, like the ones above.

    EDIT: Whoops, just reread ans saw you said remote desktop wasn't an option. Would VNC be? And if not, why? (may end up make it easier to suggest you an upgrade path). And if you cannot run a web server, then ASP.NET is out of the question right there :)

    Wednesday, November 5, 2008 11:14 AM
  • User274198738 posted

    Hi shados, thanks for the response.

    As far as any remote desktop goes, my group doesn't have the resources/budget to get a PC just to house a 6MB database.

    If ASP.NET isn't right for this particular job, then what would be? I just figured editing the db via a browser interface would have better performance than accessing the db correctly but I could be wrong. It's looking very bleak right now :D

    Wednesday, November 5, 2008 12:17 PM
  • User-1573490007 posted

    Yes, but to run an ASP.NET app, you need a web server, and you said a web server is out of the question :)

    The DB is already on a share is it not? So its on a computer. Can't you put VNC on that computer? Or does the novell thingy not have any such programs?

    The last option I'd see, is to let users remote/VNC to their work computers (its what people do where I'm currently working) via VPN, instead of running the app itself via VPN.

    Wednesday, November 5, 2008 1:06 PM
  • User274198738 posted

    Yeah, I wasn't sure if ASP.NET was the right solution, I was just exploring the language possibilities for a browser-based front end that could interact with a db.

    To my knowledge we wouldn't be able to get any type of remote software on a Novell server, and even if we could, there's no way our security team would allow it. We're pretty locked down.

    And the last option won't work since we all have laptops.

    Oh well, thanks for the responses. Much appreciated! I guess we're just SOL.

    Wednesday, November 5, 2008 1:27 PM
  • User-1573490007 posted

    Yeah, you're pretty much screwed. Anything running on the clients will need to go through the "Access Over The VPN" overhead, so any desktop solution is out of the question, regardless of languages or anything. And anything on the server (web or otherwise) would require some form of installation on the server's side, or need an always on machine (which you could get including the OS for 300$ max for your scenario, but oh well), so its not just an asp.net issue, but any server framework.

    So that eliminates all your possibilities right there, unfortunately :)

    The one remaining option is to make a desktop app with SQL Server Compact Edition (free and tiny, not the same as Express, its made to be embedded), let people work with that, and when they're done, they could run a process locally that would then upload all of their changes in one shot to the access database. It STILL would be slow as hell, but it would not require user interaction. The part that DOES require interaction would be fast and local (think of it as creating a temp database with the changes, then uploading all the changes in batch).

    Thats the only solution you have, IMO.

    Wednesday, November 5, 2008 1:43 PM
  • User274198738 posted

    Hmmm, SQL Server Compact Edition could be a solution. I'll have to look into that a bit more, thanks. I assume that any changes are injected into the db and doesn't really work like a temp db is downloaded locally, the user works in that, then the temp is uploaded overwriting the original.

    I just hope this isn't a difficult task. I'll have to do the developing! :D

    Wednesday, November 5, 2008 1:56 PM
  • User-1573490007 posted

    No actually, my idea is simpler than what you understood from it.

    Have an application that uses SQL Server Compact and work 100% locally, like if the Access database did not exist. That way if they close the app and reopen it, they don't lose their work.

    When your users are done, they click "Upload!" or something, and then your app will go through all the new records in the SQL Server Compact database, connect to the Access database, and run the insert statements (or whatever) against the Access database, a kind of "synchronization" process.

    So the "total time" to add 5 records to the access database is the same. But instead of having to wait after the DB continually, they only wait (a lot) at the very end, all at once, so they can go get coffee or something.

    Wednesday, November 5, 2008 2:00 PM
  • User274198738 posted

    Ahhh okay. That's kind of what I was thinking but I appreciate the thorough explanation. And we would all need to have the SQL Server Compact engine (along with the app after it's been developed) installed on our individual laptops, right?

    I guess my next question is what do I use to develop the actual app?

    Again, I really appreciate your help!

    Wednesday, November 5, 2008 2:19 PM
  • User-1573490007 posted

    SQL Server Compact Edition's runtime CAN be installed with an installer if you want... however, all it is is 6 DLLs (or something) that you need to ship with your app in the same directory as all your other DLLs... it runs in the same "process" as the app that executes it. Its not a full blown server like SQL Server Express. It works very much so like Access really. Just, better in every ways, shape and form (and it supports the ADO.NET Entity Framework, so you can use that to query it instead of raw SQL!). You could ship your app as just a directory with the exe and dependencies, and a configuration file, instruct people to install .NET 3.5 SP1, edit the app.config if its required, and you're good to go.

    To make the app, just use Visual C# Express 2008 SP1. Depending on how long you have to learn, you can use either a very typical Windows Form application, or a Windows Presentation Foundation app.

    Then for the rest, you'll have to look at http://windowsclient.net/ (you can use the same login name and password as you used to login here)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 5, 2008 2:23 PM
  • User274198738 posted

    Okay, I think I'm with you.

    I appreciate the help you've given me. I think you've got me headed in the right direction!

    Thanks again! :D

    Wednesday, November 5, 2008 2:30 PM