Re: Getting data from MS Access and importing into SQL.
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 On Mon, Aug 13, 2012 at 7:38 AM, ib webn65 ibweb...@yahoo.com wrote: 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.) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352259 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352125 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Getting data from MS Access and importing into SQL.
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. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352130 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Getting data from MS Access and importing into SQL.
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.happ...@gmail.comwrote: 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. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352131 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Getting data from MS Access and importing into SQL.
If end users have connectivity to the sql server, yes (no special interface required though). On Mon, Aug 13, 2012 at 1:15 PM, Russ Michaels r...@michaels.me.uk wrote: 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 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352137 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Getting data from MS Access and importing into SQL.
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 ServerI 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 ow...@threeravensconsulting.com tel: 630-486-5255 fax: 630-310-8531 http://www.threeravensconsulting.com -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Monday, August 13, 2012 12:16 PM To: cf-talk Subject: Re: Getting data from MS Access and importing into SQL. 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.happ...@gmail.comwrote: 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. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352139 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Getting data from MS Access and importing into SQL.
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 ow...@threeravensconsulting.com wrote: 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 ServerI 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 ow...@threeravensconsulting.com tel: 630-486-5255 fax: 630-310-8531 http://www.threeravensconsulting.com -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Monday, August 13, 2012 12:16 PM To: cf-talk Subject: Re: Getting data from MS Access and importing into SQL. 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.happ...@gmail.comwrote: 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. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352140 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
