No Error Message - using pyodbc.connect() on an Access DB Linked to a SharePoint List

[Bigbigyoshi]

Environment

• Python: 3.6.6 64bit
• pyodbc: 4.0.27
• OS: Windows 10 Enterprise
• DB: Microsoft Access DB Linked to Sharepoint 2016 List
• driver: Not sure how to check this. Please advise if this info is important.

Issue

I have two .accdb files. One is called Test.accdb, the other is called Test2.accdb.

Test.accdb is linked to a Sharepoint 2016 List. Test2.accdb is not linked to anything.

I am having issues setting up a conn variable.

I am assigning conn as follows:

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\\Users\\userName\\Desktop\\Project Folders\\sharepointList\\test.accdb;')

When I run that line of code with test2.accdb as the file to connect to I have no issues. The line completes and I can run the rest of my code.

When I run the line of code with test.accdb, the one linked to Sharepoint, I can not get past it. I wrote a small amount of code to test a theory. It is shown below:

print("Before")
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\\Users\\userName\\Desktop\\Project Folders\\sharepointList\\test.accdb;')
print("After")

I also tried the following, using try/except:

try:
    print("Before")
    conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\\Users\\userName\\Desktop\\Project Folders\\sharepointList\\test.accdb;')
except:
    print("Something Crashed")
print("After")

The new print statement was never executed. This leads me to believe that the code never really crashed, It just sort of stopped.

When I run this code with test2.accdb both print lines are executed. When I run this code with test.accdb, the one linked to sharepoint, only the "Before" print line is executed. I would expect the conn assignment to execute completely and allow me to execute the rest of my code. Instead, the code stops running at some point within the .connect() function. I get no error message when I run the code. The code simply stops.

[gordthompson]

Have you tried executing pyodbc.pooling = False before calling connect as suggested here?

[Bigbigyoshi]

Have you tried executing pyodbc.pooling = False before calling connect as suggested here?

I have tried adding pyodbc.pooling = False directly before calling connect. I got the same results.

import pyodbc
print("Before")
pyodbc.pooling = False
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\\Users\\userName\\Desktop\\Project Folders\\sharepointList\\test.accdb;')
print("After")

[v-chojas]

Could you post an ODBC trace?

[Bigbigyoshi]

Could you post an ODBC trace?

Where can I find this?

[v-chojas]

Instructions here

[Bigbigyoshi]

Start the trace, run my script, end the trace?

[v-chojas]

Correct.

[gordthompson]

It might also be helpful to know if the following VBScript works for you:

driver = "{Microsoft Access Driver (*.mdb, *.accdb)}"
dbq = "C:\Users\userName\Desktop\Project Folders\sharepointList\test.accdb"
Set con = CreateObject("ADODB.Connection")
con.Open "DRIVER=" & driver & ";DBQ=" & dbq
WScript.Echo "Connected."

[Bigbigyoshi]

I have found some trace information in an SQL.LOG file. There are 7 temp readouts that have appeared after running the trace around my failing script. Three of them have return codes of 0, or successful, the other 4 do not have any return codes.

I would like to avoid posting the file itself just in case there is any confidential information within it.

[Bigbigyoshi]

driver = "{Microsoft Access Driver (.mdb, .accdb)}" dbq = "C:\Users\userName\Desktop\Project Folders\sharepointList\test.accdb" Set con = CreateObject("ADODB.Connection") con.Open "DRIVER=" & driver & ";DBQ=" & dbq WScript.Echo "Connected."

In attempting to run this script in powershell, Powershell 'stopped working'.

[v-chojas]

That is not a Powershell script, it is a VBscript.

[Bigbigyoshi]

That is not a Powershell script, it is a VBscript.

Can I not just run the script in CMD or Powershell?

[gordthompson]

No, VBScripts need to be interpreted by CSCRIPT.EXE or WSCRIPT.EXE.

The equivalent PowerShell script would be

$connStr = @"
DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};
DBQ=C:\Users\userName\Desktop\Project Folders\sharepointList\test.accdb
"@
$con = New-Object System.Data.Odbc.OdbcConnection $connStr
$con.Open()
Write ("Connected.")

[Bigbigyoshi]

Same thing happens when I run the .ps1 as when I ran the VB Script using cscript(path to script): I got a pop-up telling me that PowerShell ISE has stopped working. Nothing was printed in the console as far as error codes go.

[gordthompson]

It's beginning to sound like the Access ODBC driver simply doesn't like that .accdb file.

[gordthompson]

... and if that is indeed the case then you might have better luck using pywin32 and ACE.DAO instead of pyodbc and ODBC:

import win32com.client

# ACE.DAO constants
dbOpenSnapshot = 4

dbe = win32com.client.Dispatch("DAO.DBEngine.120")
db = dbe.OpenDatabase(r"C:\Users\Public\Database1.accdb")
rs = db.OpenRecordset("SELECT DonorName FROM Donor WHERE DonorID=1", dbOpenSnapshot)
print(rs.Fields["DonorName"].Value)
rs.Close()
db.Close()

[Bigbigyoshi]

Strangely enough I get the same sort of issue when using this, only, it happens with the line: db = dbe.OpenDatabase(r"test.accdb").

Are there limitations with using these methods on a .accdb that's linked to sharepoint by any chance?

[gordthompson]

If you open the database in Access itself are you prompted for credentials when you try to open an object that is linked to SharePoint?

[Bigbigyoshi]

If you open the database in Access itself are you prompted for credentials when you try to open an object that is linked to SharePoint?

No. When I open the .accdb I can access everything. I can also add items to the .accdb and have them successfully transfer to the sharepoint list. Something to note though, I am using Microsoft Office 365. The account I am signed into there has access to the Sharepoint. If I was not signed into the account for Office 365 I might not be able to access the .accdb. I would think that since the .accdb can already talk to sharepoint I wouldn't need any login info for the pyodbc script.

(EDIT) One thing I am currently planning to try is to have someone access the permissions of the list the database is linked to on sharepoint. I will see if there are any additional requirements to connect to the list there.

[v-chojas]

This might be a limitation of the Access ODBC driver.

[Bigbigyoshi]

When I debug the script and step into the following function call: db = dbe.OpenDatabase(r"test.accdb")

This pops up in a new window of my IDE: Could not load source '<COMObject DAO.DBEngine.120>': Source unavailable.

[gordthompson]

You don't have the source code files for the ACE.ADO components. Only Microsoft has those.

One other thing you could try, again with pywin32:

import win32com.client

# ACE.DAO constants
dbOpenSnapshot = 4

print('Launching MSACCESS.EXE in the background ...')
appAccess = win32com.client.Dispatch("Access.Application")
print('... done.')
print('Opening database ...')
appAccess.OpenCurrentDatabase(r"C:\Users\Public\Database1.accdb")
print('... done.')
cdb = appAccess.CurrentDb()
rs = cdb.OpenRecordset("SELECT txt FROM Table1 WHERE ID=1", dbOpenSnapshot)
print(rs.Fields["txt"].Value)
rs.Close()
appAccess.Quit()

[Bigbigyoshi]

I ended up just using the following code to test if I could access the database file:

import win32com.client
print('Launching MSACCESS.EXE in the background ...')
appAccess = win32com.client.Dispatch("Access.Application")
print('... done.')
print('Opening database ...')
appAccess.OpenCurrentDatabase(r"test.accdb")
print('... DONE.')

This did not succeed, but I was given some information this time around:

Launching MSACCESS.EXE in the background ...
... done.
Opening database ...
Traceback (most recent call last):
  File "c:/Users/userName/Desktop/Project Folders/sharepointList/temp.py", line 6, in <module>
    appAccess.OpenCurrentDatabase(r"test.accdb")
  File "<COMObject Access.Application>", line 3, in OpenCurrentDatabase
  File "C:\Python\lib\site-packages\win32com\client\dynamic.py", line 287, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, "Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it
is not an ADP file.", None, -1, -2146820422), None)

This made me think it was not working because I had the .accdb file open, but I can assure you this is not the case.

[gordthompson]

Have your test script print(os.getcwd()) to verify that the current working directory is in fact the directory where "test.accdb" resides.

[Bigbigyoshi]

Have your test script print(os.getcwd()) to verify that the current working directory is in fact the directory where "test.accdb" resides.

Yes. I also confirmed by testing test2.accdb with this script. (The file not liked to sharepoint) Everything worked when I used test2.accdb and I have confirmed that my path to the file is correct.

[gordthompson]

Well, just on the off-chance that it is some incompatibility between Python and that particular file you could always try this in PowerShell:

Write "Launching MSACCESS.EXE in the background ..."
$Access = New-Object -com Access.Application
Write "... done."
Write "Opening database ..."
$Access.OpenCurrentDatabase("C:\path\to\test.accdb")
Write "... done."
Write "Closing database ..."
$Access.CloseCurrentDatabase()
Write "... done."
Write "Quitting MSACCESS.EXE ..."
$Access.Quit()
Write "... done."
$rtn = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Access)
Remove-Variable Access

[Bigbigyoshi]

Seeing a successful run is a sight for sore eyes. This PowerShell script seemed to do the trick.

PS C:\Users\UserName\Desktop> C:\Users\UserName\Desktop\powershellscript.ps1
Launching MSACCESS.EXE in the background ...
... done.
Opening database ...
... done.
Closing database ...
... done.
Quitting MSACCESS.EXE ...
... done.

Unfortunately, this is not quite what I need for this project. Though it is nice to see this work, it would be best if this project could be kept in python.

[gordthompson]

it would be best if this project could be kept in python

That could prove to be ... "challenging". Everything we've tried in Python has been unsuccessful, including COM Automation (Access.Application), but this last test indicates that COM Automation works (i.e., can at least open the linked database without crashing) from PowerShell. Unfortunately I was not able to get ACE.DAO to work from within PowerShell, even for a "normal" Access database, and depending on what you actually want to accomplish PowerShell might not be the best environment anyway.

If this was my project my next step would be to fire up Visual Studio and see if I could work with the linked database via COM Automation from C#.

[Bigbigyoshi]

it would be best if this project could be kept in python

That could prove to be ... "challenging". Everything we've tried in Python has been unsuccessful, including COM Automation (Access.Application), but this last test indicates that COM Automation works (i.e., can at least open the linked database without crashing) from PowerShell. Unfortunately I was not able to get ACE.DAO to work from within PowerShell, even for a "normal" Access database, and depending on what you actually want to accomplish PowerShell might not be the best environment anyway.

If this was my project my next step would be to fire up Visual Studio and see if I could work with the linked database via COM Automation from C#.

That's a bummer that we couldn't find a solution using Python. Thanks a ton for all the help and time you've provided!

[gordthompson]

This issue could do with a more descriptive title that mentions Access and SharePoint.

[mkleehammer]

Wow - lots of great work @gordthompson. I'm going to close this now as I don't think there are any avenues left to pursue. It does appear to be the Access driver.