Results 1 to 6 of 6

Thread: Access

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #6
    Join Date
    Feb 2009
    Location
    SW Connecticut/NY area
    Posts
    9,181

    Default Re: Access

    Funny you should come up with that, I was looking at it earlier and while it didn't actually work, a variation of it did:

    Code:
    SELECT *
       FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'C:\VBdotNet\QBInterface\AccessDB\Assets4_Data.mdb';
                 'admin';'',Employees)
    Different driver, this one for 64-bit. But then there are a couple of other wrinkles. Before the above approach will work you first need to execute the following:

    Code:
    sp_configure 'show advanced options', 1;  
    RECONFIGURE;
    GO 
    sp_configure 'Ad Hoc Distributed Queries', 1;  
    RECONFIGURE;  
    GO  
    ---------------------------------------------
    USE [master] 
    
    
    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
    Then download & install the 64-bit version of the "Microsoft.ACE.OLEDB.12.0" driver, assuming you're using 64-bit SSMS

    And then the query works!
    Last edited by Bob Worsley; 8-Mar-2022 at 11:31 AM.
    Bob Worsley
    203-249-2633
    rlworsley at gmail.com

    Do or do not. There is no try. — Yoda

Posting Permissions

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