November 3, 2021 at 3:26 pm
Hello,
I would like to ask, where can I check which versions if SQL Management Studio is installed on a client?
My user cannot run the import tool, getting the error :
'Microsoft.ACE.OLEDB.16.0' provider is not registared on the local machine
This tool which should be 64-bit is part of the SQL Server, which the user is not allowed to have on a local PC.
One of the solutions is to install "SQL Server Data Tools (SSDT)". I cannot seem to locate an offline version of the installer.
Threads I am following are here:
https://www.sqlshack.com/import-data-excel-file-sql-server-database/
Can anyone offer some hints?
Kind Regards,
Richard
November 3, 2021 at 3:30 pm
I don't have time for a full answer, but the ACE driver is a separate install.
Take a look here: https://www.microsoft.com/en-us/download/details.aspx?id=54920
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2021 at 6:42 pm
SQL Server Management Studio is a 32-bit application - and therefore requires the 32-bit version of the drivers for Office based applications. However, many are now installing the 64-bit version of Office and that doesn't provide the correct driver.
There are 2 possible solutions:
Option 1 is a bit of overkill for a client machine - and option 2 will fail if the 64-bit driver is already installed. However, you can install the 32-bit version side-by-side by installing it using the command line with the appropriate qualifier. Depending on the version of the driver you are attempting to install - that will be either the /quiet or /passive qualifier.
Installing SSDT does not install the Office (ACE) driver either - and since SSDT is also a 32-bit application it also will require the 32-bit version of the ACE driver, and as stated above - Office x64 installs the 64-bit version.
Of course, you could also just uninstall Office x64 and install Office x86 - but that would not be my recommendation. Especially since there is an easy way to resolve the issue by installing the 32-bit ACE driver.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 4, 2021 at 9:19 am
Hello,
thank you for this thorough explanation. I can hardly believe this:
SSMS is available only as a 32-bit application for Windows. If you need a tool that runs on operating systems other than Windows, we recommend Azure Data Studio. Azure Data Studio is a cross-platform tool that runs on macOS, Linux, as well as Windows.
Can you shed some more light on this?
2. Install the 32-bit version of the ACE drivers
So how exactly do this?
Download Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920
Install it in Command Prompt?
1. Press “Win +R”.
2. Type “AccessDatabaseEngine.exe /quiet”.
3. Press Enter.
Note: A helpful commenter has informed me that for Access 2016 the switch is /quiet rather than /passive.
Kind Regards,
Ryszard
November 4, 2021 at 7:47 pm
Not sure if you were asking for more details - seems you found the answer. For earlier version of the ACE drive - the parameter was /passive vs /quiet for 2016.
My general rule is to install both versions of the ACE drive on a workstation - and only the x64 version on servers (unless client tools are needed on the server - which may be needed by vendors).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply