[python-win32] AccessDB 2007

Mon Aug 27 13:47:42 CEST 2012

Dear Vernon,

Thanks for your pointers, I will try the adodbapi. For now I have gone
ahead with the much simpler version of COM. Here is something which
works fine for my problem at hand.

    access = Dispatch('Access.Application')

    dbpath = 'ProntoDB.accdb'

    print dbpath

    dbengine = access.DBEngine

    workspace = dbengine.Workspaces(0)

newdb = workspace.OpenDatabase(dbpath)

recordSet = newdb.OpenRecordset('select * from Sheet1')

print recordSet.Fields(headerText[v]).value

#-- same way the update and adding new records works.

One more question, in case you don't want me to open another thread.
What kind of exceptions handling are needed while working with the
MS-AccessDB ?

Warm Regards

Sachin

From: ext Vernon Cole [mailto:vernondcole at gmail.com] 
Sent: Thursday, August 23, 2012 8:01 PM
To: S, Sachin (NSN - IN/Bangalore)
Cc: python-win32 at python.org
Subject: Re: [python-win32] AccessDB 2007

Dear S.:

You do have the correct name for the 64-bit Access data provider, and
you have downloaded it from the correct place.

I suspect that the problem could be that your connect string has both
the ADO syntax ("PROVIDER=") and ODBC syntax ("DRIVER=") at the same
time. You should probably eliminate the "driver=" clause.

As for another way of accessing the database, you could make use of
adodbapi. It does the Dispatch('ADODB.Connection') and all that for you.
I am not sure which version of pywin32 (and therefore which version of
adodbapi) Active State is including in their package at the moment, but
anything recent should work. If in doubt, you can get the latest
versions at http://sourceforge.net/projects/pywin32 and/or the complete
test and sample code at http://sourceforge.net/projects/adodbapi . The
latter also includes is64bit.py to sense which provider you need.

I include the following as a suggestion...
<code>
import is64bit # assume we are in the adodbapi.test folder
import adodbapi
print adodbapi.version
_accessdatasource = "test.mdb"
if is64bit.Python():
    provider = "Microsoft.ACE.OLEDB.12.0"
else:
    provider = "Microsoft.Jet.OLEDB.4.0"
connStrAccess = "Provider=%s;Data Source=%s" %
(provider,_accessdatasource)
print connStrAccess
_table_name= 'Products'
#create the connection
con = adodbapi.connect(connStrAccess)
#make a cursor on the connection
c = con.cursor()
#run an SQL statement on the cursor
sql = 'select * from %s' % _table_name
c.execute(sql)
#get the results (but limit the number) using normal dbapi method
db = c.fetchmany(5)
#print them
for rec in db:
    print rec
#or, using the cursor as an iterator, so you don't need to call c.fetch*
print repr(c.next())
c.close()
con.close()
</code>
<console dump>
adodbapi v2.4.2.2
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.mdb
('1', 'Widgit', '5.0', '15.1234', '2009-01-29 13:05:30')
('2', 'Thingamajig, Standard', '505.0', '0.1', '2009-01-29 15:05:19')
('3', 'Left Handed Smoke Shifter', '1.0', '1000000', '2008-04-01
12:00:00')
('4', 'Gravel (Bulk)', '100.25', '32.4567', '2009-01-29 13:05:31')
('5', 'Tube, Drinking, Plastic, For cold liquids', '500000.0', '0.0013',
'2009-01-29 13:05:32')
<SQLrow={itemnumber:6, itemname:u'Annoy-A-Tron', unitsonhand:1.0,
costperunit:Decimal('12.95'), lastordered:datetime.datetime(2009, 1, 29,
13, 5, 33)}>
</console dump>
--
Happy Pythoning,
Vernon Cole

On Thu, Aug 23, 2012 at 3:27 AM, S, Sachin (NSN - IN/Bangalore)
<sachin.s at nsn.com> wrote:

Hi,
I am facing some problems while accessing the Microsoft Access Database
2007, using win32com. The code and errors are given below.
I also tried the steps in the link but to no avail:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

Could you help, or also if you know of a any other way of accessing the
database using the win32com module.

Version:
ActivePython 2.7.2.5 (ActiveState Software Inc.) based on
Python 2.7.2 (default, Jun 24 2011, 12:22:14) [MSC v.1500 64 bit
(AMD64)] on win32

Operating System: Windows 7

Code:
try:
    import sys
    from win32com.client import Dispatch
except ImportError as e:
    print e
    sys.exit(1)

if __name__ == '__main__':
    data_source = "C:\\temp\\Database1.accdb"
    access = Dispatch('ADODB.Connection')
    access.Open('PROVIDER=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft
Access Driver (*.mdb, *.accdb)};DBQ=C:\\temp\\Database1.accdb;')

Error:

C:\> a_db.py
Traceback (most recent call last):
File "D:\Data\Python Scripts\For the heck of sharepoint\a_db.py", line
11, in <module>
access.Open('PROVIDER=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft Access
Driver (*.mdb, *.accdb)}
;DBQ=C:\\temp\\Database1.accdb;')
File "<COMObject ADODB.Connection>", line 3, in Open
File "C:\ActivePython27\lib\site-packages\win32com\client\dynamic.py",
line 276, in _ApplyTypes_
result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType,
argTypes) + args)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0,
u'ADODB.Connection', u'Provider canno
t be found. It may not be properly installed.',
u'C:\\Windows\\HELP\\ADO270.CHM', 1240655, -21468245
82), None)
_______________________________________________
python-win32 mailing list
python-win32 at python.org
http://mail.python.org/mailman/listinfo/python-win32

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20120827/158602a6/attachment.html>