[python-win32] AccessDB 2007

[Vernon Cole]

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/20120823/f04a88af/attachment.html>