Post reply

How to update a table on SQL Server 2005 from an Excel file?

  • July 23, 2009 at 8:39 am

    #133212

    I have a small MS Access application that uses linked tables and pass-thru and append queries. Its single purpose is to refresh a table in SQL Server with data from an Excel table. Access app uses ODBC connection to access SQL Server. Everything works OK.

    The problem is that if I want to use that Access/Excel setup from another machine is it not enough to just copy two files (.mdb abd .xls) - I need to build new ODBC connection on that machine.

    Is there a way to embed ODBC information into .mdb so it will be portable. I do not see anything machine-specific during ODBC creation and therefore do not see why it can not be copied inside or along with the mdb/xls.

    I was thinking to move the *.dsn file from C:\Program Files\Common Files\ODBC\Data Sources but it has one machine-specific parameter: WSID - could I just change to the name of the target machine?

    I tried to accomplish same results (update SQL server from Excel file) using linked servers - got an unspecified error message, tried to use ad hoc distributed query but it is turned off on the production server and I do not want to change any of its settings.

    any comments / suggestions would be appreciated

    thanks

  • July 23, 2009 at 11:44 am

    #1029174

    Have you considered using SSIS? The package contains the connection information. You would be able to accomplish the same thing (Excel to SQL) that you are using MSAccess for now.

    Another thought, what about creating a VBscript or similar for this and using a connection string in the file?

  • July 23, 2009 at 12:09 pm

    #1029181

    I would also consider SSIS but if you want to continue to use access you can setup DSN-Less connections. http://support.microsoft.com/kb/892490

  • July 23, 2009 at 12:13 pm

    #1029183

    This is the setup I have used with success. Particularly useful if the structure of your tables changes frequently.

    http://www.accessmvp.com/djsteele/DSNLessLinks.html

  • July 23, 2009 at 1:35 pm

    #1029245

    Thank you, Wesley and Emily. No, I did not think SSIS - I never used it. The update I am building will be used once a year so I am trying to make it very simple. More and more leaning toward putting together a stored procedure, triggered via SSRS, connecting to Excel and pulling data to SQL Table. Hope to eliminate Access... Will read through your links, thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply