Post reply

Copying data form MS Access to SQL Server 2005

  • October 31, 2006 at 9:03 am

    #170265

    Hi there,

    Could somebody point me in the right direction or give me something to search on google for?

    I would like to write a few lines of t-SQL which would import data from an access database into SQL Server. Put simply:

    Insert into SQLServerTable (z,y,z)

    select x,y,z from AccessTable

    I just don't know how to use T-SQL to access the MS Access database.

    I don't want to use SSIS as I find it's harder to administer as my database tables change quite oftern and I'd like to do it though code if possible.

    Any ideas?

    Thanks

    Ed

  • October 31, 2006 at 9:15 am

    #669000 

    Insert into dbo.YourTable (a,b,c)SELECT a.a, a.b, a.cFROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)    AS a

  • October 31, 2006 at 9:51 am

    #669017

    Thanks bud, that appears to be what I need although now I've got MDAC errors to sort out....

    Thanks

    Ed

  • October 31, 2006 at 11:38 pm

    #669136

    If you are going to do this on a regular basis, create a linked server.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • October 31, 2006 at 11:47 pm

    #669137

    I'll be doing this one a month or so at the most and entirly just by running a script.

    Thanks

    ed

  • January 24, 2007 at 2:10 am

    #685143

    I have used this query

    SELECT CustomerID, CompanyName

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'e:\datfiledata\Northwind.mdb';

    'admin';'',Customers)

    but it dosent work. It dispalyed following error

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

    any one help me

  • January 24, 2007 at 7:54 am

    #685221

    check your surface area configuration for features. almost everything is turned off buy default on a new install

Viewing 7 posts - 1 through 6 (of 6 total)

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