Getting data from MS Access and importing into SQL

I have been assigned to a new project that has two applications.  One of the applications is a MS Access file that people carry on their laptop. They use MS Access because the users work in a lot of places where they have no access to the Internet. When they are done using the MS Access application the user has to go to a location with Internet access, export the data in one of the MS Access tables to an Excel file, which then gets uploaded to a website. Here are the steps the data goes through. 1) While in MS Access the user exports the table's data to an Excel file. 2) The user goes to the website to select the Excel file created by MS Access using an HTML file select form field. (The name of the Excel file is usually always different, but the table inside the file and its format are always the same.) 3) When the user clicks the submit button the selected Excel file is uploaded to the server. 4) The site uses cfspreadsheet to read the data from the Excel file 5) The site uses SQL code to insert the data into the site's SQL database. This seems cumbersome. I was wondering if there was a way to get the data directly from the MS Access file and insert it into the SQL database table?  The name of the MS Access file will change, but the format of the table in the MS Access file will never change. (I know, never say never. But for the purpose of this question, let's stick with saying never.) If it helps, we can use the following names: MS Access file = MyAccessFile MS Access table = MyAccessTable SQL Server database = MySqlDatabase SQL Server table = MySqlTable The columns in the table can be: GizmoName = Varchar (255) GizmoDescription = Text

Maybe this: - have end-users upload entire MDB. - in uploaded MDBs insert links to SQL Server tables. - execute a set of Insert queries to copy data from MDB tables to SQL Server tables.

it is possible to add a custom interface for msaccess that links it to sql server, so users can do this directly. I don;t know how to do this as I don;t do VBA, but we have a customer that does this very thing. He runs msaccess locally and uses it to conenct to his hosted database and manage data and sync with his local access db On Mon, Aug 13, 2012 at 6:12 PM, Captain Obvious <mr.happeee@gmail.com>wrote: ----- Excess quoted text cut - see Original Post for more -----

If end users have connectivity to the sql server, yes (no special interface required though). > > it is possible to add a custom interface for msaccess that links it to sql > server, so users can do this directly. > I don;t know how to do this as I don;t do VBA, but we have a customer that > does this very thing. He runs msaccess locally and uses it to conenct to > his hosted database and manage data and sync with his local access db >

There is an upgrade wizard that will take your access data and tables and recreate them in SQL Server.  That has been there for at least 3 versions of SQL Server....I believe that was implemented in SQL 2003.  So if you are using a version later than that, you should be in luck ;-) ------------------------------------ Three Ravens Consulting Eric Roberts Owner/Developer owner@threeravensconsulting.com tel: 630-486-5255 fax: 630-310-8531 http://www.threeravensconsulting.com ------------------------------------ it is possible to add a custom interface for msaccess that links it to sql server, so users can do this directly. I don;t know how to do this as I don;t do VBA, but we have a customer that does this very thing. He runs msaccess locally and uses it to conenct to his hosted database and manage data and sync with his local access db On Mon, Aug 13, 2012 at 6:12 PM, Captain Obvious <mr.happeee@gmail.com>wrote: ----- Excess quoted text cut - see Original Post for more -----

if your looking to move off access then Microsoft also provide free migration tools as well. On Mon, Aug 13, 2012 at 8:18 PM, Eric Roberts < owner@threeravensconsulting.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

There are a lot of ways to get data from MSAccess to a SQL database. However, even though this seems cumbersome, it is an effective method, the users know how to do it, and the excel import and export tools are solid. I would leave it alone.  It it ain't broke.... This seems cumbersome. I was wondering if there was a way to get the data > directly from the MS Access file and insert it into the SQL database table? >  The name of the MS Access file will change, but the format of the table in > the MS Access file will never change. (I know, never say never. But for the > purpose of this question, let's stick with saying never.) >