Import Excel file to Access DB on remote server

Question

hi guys the organisation that I work for has a website using an access db (mdb file) hosted on a server running .net 1.1 and asp.net. currently the web site is able to list a number of members of the organisation that it pulls out of a 'members' table in the access db. the current web site is able to export all the members info to a csv file and is also able to add and edit individual members. I've been asked to create functionality to import an entire excel spreadsheet into the members table. at present I'm using visual studio 2005 to do all my coding so what I thought I might do to add this functionality is to create a windows forms project to import the excel spreadsheet into the database (as the hosting platform doesn't support .net 2.0). is there some way of interfacing my app with the access db on the server? I've also thought of grabbing a copy of VS 2003 to create .net 1.1 compatible code but I hear that the RAD data tools in VS 2003 aren't as good as those in VS 2005 so this is not a preferable option... thanks in advance max

Max · Answer

Hey Mike. Thanks for your help! :-) From a quick scan the query tool looks pretty awesome! Might become quite useful. I was thinking about my problem a little more last night after I created the thread and I'm not even sure if they need an excel spreadsheet or if they have just been using one in the past because it has been easier to store the data in it and then manually add the data to the web site using the old (and not very efficient) system. What I thought of possibly doing is still creating a windows forms project using VS 2005 (because the web host only supports .net 1.1) and using a datagridview control to show the data in the access db. Then i'll make rows like the 'id' row read-only (and possibly hide it) and do some form of validation when the user enters data (haven't figured out quite how i'm going to do this yet). That way with the datagridview control the user will kinda feel like they are editing an excel spreadsheet anyway and i won't have to worry about sync problems between a seperate spreadsheet and db.. Would you know how to connect a windows forms project running on the user's desktop to the access db on the web host? Thanks again for your help Max

Mike Gold · Answer

I would use 2005. It has better data controls for access and much better Microsoft Excel Support. This project should be fairly straightforward. 1) Create an Office Excel Project using the Visual Studio Wizard 2) Use the Excel object to pull the data out of the spread sheet using the Range object (this article may help you in your project it has an excel wrapper to do some excel manipulation) http://www.c-sharpcorner.com/UploadFile/mgold/Query2Excel12032005011029AM/Query2Excel.aspx 3) Use something like the AccessDataSource to insert the cell data into access. Best