Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: INSERT INTO Access table

    Is there a way of INSERTing INTO an Access table using T-SQL? Here's an Access query that I need to do in SQL Server:

    Code:
    INSERT INTO tblCreditMemos
    IN '\\NW-ITD-FS2\bilcas$\Access\Automated Budget Reporting\ABRBackEnd.mdb'
    SELECT * FROM billc_cred03f_temp;
    Any help appreciated.
    Last edited by bcass; 12-08-06 at 08:30.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Check out OPENROWSET in BoL

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on how you are trying to do this... I don't have enough of the context of your question to help you yet.

    My first guess would be that DTS might help.

    Another approach would be a linked server.

    There are other approaches, but they vary a lot depending on what you are trying to do.

    -PatP

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks for the replies. The OPENROWSET method wouldn't work because Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied on the SQL Server I'm trying to access. I can't use DTS because I only have read access to this particular server.

    This is what I ended-up doing (from within Access):

    Code:
    INSERT INTO tblFiscalSales
             SELECT *
             FROM billc_test_fiscal_sales_by_salesp 
             IN [ODBC;DSN=Epicor Test DB;UID=xxxx;PWD=xxxx;DATABASE=enatworld]
    This works, but I'm doing it from a CurrentProject.Connection so it's JET doing the work, not the server. I suspect there would be a slight performance gain if I did it the other way round and got the server to INSERT INTO Access but it's not an option without changing settings on the server.
    Last edited by bcass; 12-08-06 at 14:53.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Want performance gain, export the data out of Access and bcp the data in
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    Want performance gain, export the data out of Access and bcp the data in
    Wrong way round Brett

    I doubt it makes any difference whether you push or pull the data performance wise.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •