Issue setting up UDC using ODBC Excel

[awhitfield]

Hi,

I am following he guidance supplied on Qlik Article https://qliksupport.force.com/articles/000037143

But am getting the errors;

60 20180614T101559.835+0100 ERROR KEN-QLIKDEV-01 UserManagement.Repository.Repository.Users.Factories.UserDirectoryFactory 46 2d38f0dd-856c-41c2-a0d1-b0fcc485f5d7 KEN-QLIKDEV-01\QS_admin Exception when trying to access the userid field of the [MyUsers$] table: ERROR [22018] [Microsoft][ODBC Excel Driver] Internal OLE Automation error 2d38f0dd-856c-41c2-a0d1-b0fcc485f5d7

61 20180614T101559.897+0100 WARN KEN-QLIKDEV-01 UserManagement.Repository.Repository.Users.Factories.UserDirectoryFactory 26 c754142c-1d7d-4d2c-bc57-250b39a51277 KEN-QLIKDEV-01\QS_admin Setting up UDC'EXCEL' of type Repository.UserDirectoryConnectors.ODBC.OdbcExcel unsuccessful ERROR [22018] [Microsoft][ODBC Excel Driver] Internal OLE Automation error↵↓Exception when trying to access the userid field of the [MyUsers$] table: ERROR [22018] [Microsoft][ODBC Excel Driver] Internal OLE Automation error at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)↵↓ at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)↵↓ at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)↵↓ at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)↵↓ at Repository.UserDirectoryConnectors.ODBC.DataBaseConnector.TestField(IDbConnection connection, String tableName, String fieldName)↵↓ at Repository.Users.SafeUserDirectoryConnector.CallWithTimeout(Func`1 func, TimeSpan timeout)↵↓ at Repository.Users.SafeUserDirectoryConnector.Setup(Logger logger)↵↓ at Repository.Users.Factories.UserDirectoryFactory.TrySetupUserDirectory(UserDirectory userDirectory) c754142c-1d7d-4d2c-bc57-250b39a51277

Even I am using the example file supplied with the article

Any ideas?

ltu‌

Andy

[Levi_Turner]

I don't have any obvious thoughts but briefly:

1. Make sure you have an up to date Access Engine (Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Cent...) , reinstall if necessary
2. Make sure the file isn't open when trying to do a UDC sync. I've had this issue internally when testing stuff

[awhitfield]

Hi Levi,

file is defo not open, no excel on the server, reinstalled the drivers, but the issue persists

Andy

[awhitfield · Accepted solution]

Hi Folks turns out that Microsoft Access Database Engine 2016 doesn't work, BUT 2010 does!

Andy

[dbaksa_1]

I was watching part 5 of a QMC video that Qlik put together. The video specified that the ODBC driver for Excel has to be 64Bit. I downloaded AccesDatabaseEngine_X64.exe and installed the driver on my system and was able to configure the UDC..

Keep in mind that if 32Bit Office apps are installed the 64Bit driver cannot be installed.

I hope this helps!