Post reply

Importing a spreadsheet into SQL Server 2000 via Microsoft Access Front End

  • August 16, 2004 at 1:01 pm

    #66523

    Hello all,

    Here is the thing :

    I am trying to import a spreadsheet into a central SQL Server database via Access 2003 front end using the following code :

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,  "tblSeniorImportData", "C:\Senior I & A\SeniorI&AData.xls", True

    The table "tblSeniorImportData" is present in the SQL Server database and when I log in onto the network machine as "dbo",  the transfer works fine.

    But when I log in as some other user, during the transfer a copy of the table "tblSeniorImportData" is created into the SQL Server with owner as my network login name. (I have all the dbo rights on the database)

    What do I need to do to prevent the SQL Server from making the copy of the table rather import the spreadsheet data into the original table?

    Thanks,

    Manjiri

     

     

     

  • August 17, 2004 at 6:48 am

    #519319

    I would not use the method you are using.  I would link the Excel file and use an append query to do the data transfer.  This is assuming that teh SQL table is also linked.

  • August 17, 2004 at 12:30 pm

    #519418

    Thanks. Can u give me some details as to how do I do this exactly? HOw do i link the Excel file?

  • August 17, 2004 at 1:01 pm

    #519424

    Define the range for your data and name it in Excel.

    Click on File | External Data

    Select your excel file and the named range

    Once it is linked, you can create an append query to move the data to SQL Server.

    As long as you name the excel file the same and the named range the same, you can keep importing the data without a problem.

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

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