Post reply

Update Data from Excel to SQL Server

  • July 13, 2004 at 11:52 am

    #180962

    Hi,

    I have a question regarding updating data from Excel to SQL Server dynamically.

    I know that we can update the data from SQL Server to Excel using Microsoft Query but I am not sure if we can do it the other way round i.e., When ever there is a change in the Excel Sheet it should insert/update in the SQL Server table.

    Any help is appeiciated.

    Thanks

    MK

     

  • July 13, 2004 at 12:00 pm

    #514444

    Data Transformation Services will accomidate. You can create a DTS package and save it then run it within SQL.

    For a one time import just right mouse on the table in enterprise manager and select all tasks then import data. There is a wizard that utilizes DTS.

  • July 13, 2004 at 12:15 pm

    #514445

    Thanks for your reply, I know that we can import the data using DTS package and schedule it as a job to see if there are any updates and import the stuff into the table. But I want to know if we can configure Excel/SQL Server to recognise the changes in Excel and dynamically update the tables.

    MK

     

  • July 13, 2004 at 1:26 pm

    #514454

    I don't think that SQL is OLE compliant with Office components i.e. having Access dynamically update itself because the Excel spreadsheet changed or vice versa.

    What you could do is run a scheduled task to compare the data between SQL and the Excel spreadsheet and UPDATE/INSERT/DELETE SQL as needed.

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 13, 2004 at 1:36 pm

    #514455

    Thankyou for the Info. Ahrens.

  • July 13, 2004 at 8:43 pm

    #514491

    Or, you could create an Access adp (Project) and have some incarnation of the Transfer command to shift the data to the project - which would then update SQL Server.

    An example VBA command might be like:

    DoCmd.TransferSpreadsheet acImport, 8, tableName, TransferDir & xlsheetName, True, rangeName

    You'll find help for this in Access 2000.

    DB

     


    The systems fine with no users loggged in. Can we keep it that way ?br>

  • July 14, 2004 at 12:09 pm

    #514528

    You can use VBA and the SheetChange event in Excel to capture changes to the workbook as they occur.

  • July 14, 2004 at 4:12 pm

    #514563

    There is always the option of writing a VB or C application to check the Excel spreadsheet and update any changes to SQL table(s).

  • July 15, 2004 at 4:59 am

    #514622

    and write and update query to update the data in the sqlserver

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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