Trying to connect to Access DB (2010-accdb) which is linked to a Sharepoint list.
Set up ODBC on server and when creating data store I am getting the following error:
ODBC data source error message for operation: : <[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented >. (BODI-1112339)
Have tested access with the job server user credentials, and user has access to DB location as well as the Sharepoint List (read only).
The error occurs when I try to Import a table from the DB. i.e. Right click on Tables in Data Store and select Import by Name. Double clicking on Tables opens, but shows nothing.
Tried with a non-sharepoint Access DB with no issue. So it seems Sharepoint is getting in the way here. I can access the list from the server, as job server user.
Thanks in advance.
Running: DS - 4.2 (14.2.5.894) - Win Server 2012 R2
Further information, I do also get a locking error which is:
The database has been placed in a state by an unknown user that prevents it from being opened or locked (BODI-1112346).
I noticed that a lock file appears in the source folder as soon as I access the datastore. The same occurs when I access the DB using MS Access, unless I change the Client settings to Exclusive (instead of Shared).
So it looks as though maybe DS thinks the DB is locked by another user which throws up this error. How can I get DS to also access it in Exclusive mode, given this is an Access DB client setting.
Hi there, how is the sharepoint lists were imported into access database. Are they linked tables, or imported tables… I am just wondering if they are link tables, whether they have some locks on the access database. Access is a single user environment, once a user users, second user will not able to use it.
I had a similar setup, to extract data from an external source, where they don’t want DS connect directly. I have setup an access database, and done some macro in access to refresh the imported tables, before connect to DS. Then I have setup the access macros to run on Schedule once a week, and run DS extract to run the next day.
Other option for Sharepoint, there is a software call SQLList, which sync’s the Shrepoint data into normalised SQL databases. e use this for our data migration portal, and really good software. Worth investigating.
Thanks for your response. The Access DB is linked to a Sharepoint List.
I guess it would be good to know if Sharepoint linked Access DB’s are officially supported by DS, before I go down a non-linked path, which will require scheduled macros to refresh. If I can get DS to talk directly to the linked DB then it is a much cleaner solution.
once you go non-linked path, you only have to concern about the access database to DS connection, using ODBC64. I am 100% certain this works, as we have used it previously, where we had a 32 bit application keeps crashing when DS tries to connects to it. I over come this by using an Access in the middle to use the 32 bit drivers, and import the tables I needed into Access first, and then use 64 bit ODBC drivers to connect Access to DS.
We had this on schedule to run for almost 6 months successfully.
No problems with connecting to Access DB, when it is non-linked. Error messages as mentioned when attempting to use a Sharepoint linked DB though.
Does anyone know if this is supported?
In the meantime I will use a non-linked Access DB and then use macros to refresh the DB (delete table, reimport). This is an annoying overhead, given how powerful the linked Access DB ‘could’ be for connecting DS to Sharepoint.
Did you find a resolution to this issue?
I have run across the same problem…scoured the web and other forums with no solution.
It appears just linking to my SP list causes the DB to lock exclusively when I open it, thus my other process cannot access any tables in it…
As soon as I remove the SP linked table or import it, all is fine again.
Best I could guess it is my cvompany’s deployment of SP itself must have son=me settings causing this to happen. I find nothing I can change within Access to correct this.
I know of other Access \ SP users who tell me they can link to SP tables just fine at their company.
…Hoping you found a solution and if so PLEASE share her with me!
(BOB member since 2012-04-11)