Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2014
    Posts
    22

    Unanswered: How to Import Data to SQL Server from Access Database using TSQL Script

    I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link -

    http://www.codeproject.com/Articles/...-or-Access-usi

    Here is the code that I have tried -

    Code:
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'MSAccessConnect')
    BEGIN
    	EXEC sp_addlinkedserver 'MSAccessConnect',
    	   'Access 97',
    	   'Microsoft.ACE.OLEDB.12.0',
    	   'C:\SQL Project\TestDB1001.mdb' -- put here your datasource path
    END
    GO
    
    SELECT * FROM OPENQUERY(MSAccessConnect, 'SELECT * FROM [Table1001]')  -- put table name here
    
    go
    sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure with override   
    GO

    The access database file path is - 'C:\SQL Project\TestDB1001.mdb'
    The Table from which I want to import the data is - [Table1001]

    but when I run this script, I get this error -
    9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

    I am not being able to figure out, how to make it work.

    Secondly I need to make 2 more changes to the code posted above.
    1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
    2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Linked servers can be very troublesome and so can Access. "Access 97" as in 1997? Are you running this one a Windows 98 machine a primitive MDAC?

    Do you need to always query live data in the Access database or is this a one time or reoccurring data pull? Could you just import the data using SSIS or the Import/Export wizard.

    As for the username and password you may need one of these ( https://technet.microsoft.com/en-us/...v=sql.80).aspx ) but it has been so long since I have used Access for anythng serious, I could not say for certain.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2014
    Posts
    22
    Quote Originally Posted by Thrasymachus View Post
    Linked servers can be very troublesome and so can Access. "Access 97" as in 1997? Are you running this one a Windows 98 machine a primitive MDAC?

    Do you need to always query live data in the Access database or is this a one time or reoccurring data pull? Could you just import the data using SSIS or the Import/Export wizard.

    As for the username and password you may need one of these ( https://technet.microsoft.com/en-us/...v=sql.80).aspx ) but it has been so long since I have used Access for anythng serious, I could not say for certain.
    Thanks for your reply.
    I need to do it very regularly. I am looking for doing it through SQL Script instead of using SSIS or the Import/Export wizard.

    I am using Windows 7 64bit and SQL Server 2012

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think SSIS would be the better option here. OPENQUERY does not allow for parameters or variables to be sent, nor does it accept a variable as the query. You would need to wrap the entire query with the OPENQUERY call in an exec block, which gets very hard to troubleshoot very quickly.

    SSIS should be able to connect to an .mdb file with little to no trouble, and filtering should be fairly easy, even with variable filters. As for the .mdb password issue, I am not certain, but if your users are applying potentially changing passwords on these files, you can pretty well forget about automating the process.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by happy1001 View Post
    Thanks for your reply.
    I need to do it very regularly. I am looking for doing it through SQL Script instead of using SSIS or the Import/Export wizard.

    I am using Windows 7 64bit and SQL Server 2012
    I think your @provider and @srvproduct arguments are wrong in sp_addlinkedserver script, but you can google that just was well as I can and as for the user name and password thing I think you need to look at the link in my first email maybe.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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