<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:SimSun;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"\@SimSun";
panose-1:2 1 6 0 3 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Dear Vernon,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> access = Dispatch('Access.Application')<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> dbpath = 'ProntoDB.accdb'<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> print dbpath<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> dbengine = access.DBEngine<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> workspace = dbengine.Workspaces(0)<o:p></o:p></span></p><p class=MsoNormal style='text-indent:9.0pt'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>newdb = workspace.OpenDatabase(dbpath)<o:p></o:p></span></p><p class=MsoNormal style='text-indent:9.0pt'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>recordSet = newdb.OpenRecordset('select * from Sheet1’)<o:p></o:p></span></p><p class=MsoNormal style='text-indent:9.0pt'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>print recordSet.Fields(headerText[v]).value<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>#-- same way the update and adding new records works.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>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 ?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Warm Regards<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Sachin<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> ext Vernon Cole [mailto:vernondcole@gmail.com] <br><b>Sent:</b> Thursday, August 23, 2012 8:01 PM<br><b>To:</b> S, Sachin (NSN - IN/Bangalore)<br><b>Cc:</b> python-win32@python.org<br><b>Subject:</b> Re: [python-win32] AccessDB 2007<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-bottom:12.0pt'>Dear S.:<br><br>You do have the correct name for the 64-bit Access data provider, and you have downloaded it from the correct place.<br><br>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.<br><br>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 <a href="http://sourceforge.net/projects/pywin32">http://sourceforge.net/projects/pywin32</a> and/or the complete test and sample code at <a href="http://sourceforge.net/projects/adodbapi">http://sourceforge.net/projects/adodbapi</a> . The latter also includes is64bit.py to sense which provider you need.<br><br>I include the following as a suggestion...<br><code><br>import is64bit # assume we are in the adodbapi.test folder<br>import adodbapi<br>print adodbapi.version<br>_accessdatasource = "test.mdb"<br>if is64bit.Python():<br> provider = "Microsoft.ACE.OLEDB.12.0"<br>else:<br> provider = "Microsoft.Jet.OLEDB.4.0"<br>connStrAccess = "Provider=%s;Data Source=%s" % (provider,_accessdatasource)<br>print connStrAccess<br>_table_name= 'Products'<br>#create the connection<br>con = adodbapi.connect(connStrAccess)<br>#make a cursor on the connection<br>c = con.cursor()<br>#run an SQL statement on the cursor<br>sql = 'select * from %s' % _table_name<br>c.execute(sql)<br>#get the results (but limit the number) using normal dbapi method<br>db = c.fetchmany(5)<br>#print them<br>for rec in db:<br> print rec<br>#or, using the cursor as an iterator, so you don't need to call c.fetch*<br>print repr(c.next())<br>c.close()<br>con.close()<br></code><br><console dump><br>adodbapi v2.4.2.2<br>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.mdb<br>('1', 'Widgit', '5.0', '15.1234', '2009-01-29 13:05:30')<br>('2', 'Thingamajig, Standard', '505.0', '0.1', '2009-01-29 15:05:19')<br>('3', 'Left Handed Smoke Shifter', '1.0', '1000000', '2008-04-01 12:00:00')<br>('4', 'Gravel (Bulk)', '100.25', '32.4567', '2009-01-29 13:05:31')<br>('5', 'Tube, Drinking, Plastic, For cold liquids', '500000.0', '0.0013', '2009-01-29 13:05:32')<br><SQLrow={itemnumber:6, itemname:u'Annoy-A-Tron', unitsonhand:1.0, costperunit:Decimal('12.95'), lastordered:datetime.datetime(2009, 1, 29, 13, 5, 33)}><br></console dump><br>--<br>Happy Pythoning,<br>Vernon Cole<br><br><o:p></o:p></p><div><p class=MsoNormal>On Thu, Aug 23, 2012 at 3:27 AM, S, Sachin (NSN - IN/Bangalore) <<a href="mailto:sachin.s@nsn.com" target="_blank">sachin.s@nsn.com</a>> wrote:<o:p></o:p></p><p class=MsoNormal>Hi,<br>I am facing some problems while accessing the Microsoft Access Database<br>2007, using win32com. The code and errors are given below.<br>I also tried the steps in the link but to no avail:<br><br><a href="http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734" target="_blank">http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734</a><br><br>Could you help, or also if you know of a any other way of accessing the<br>database using the win32com module.<br><br>Version:<br>ActivePython 2.7.2.5 (ActiveState Software Inc.) based on<br>Python 2.7.2 (default, Jun 24 2011, 12:22:14) [MSC v.1500 64 bit<br>(AMD64)] on win32<br><br>Operating System: Windows 7<br><br>Code:<br>try:<br> import sys<br> from win32com.client import Dispatch<br>except ImportError as e:<br> print e<br> sys.exit(1)<br><br>if __name__ == '__main__':<br> data_source = "C:\\temp\\Database1.accdb"<br> access = Dispatch('ADODB.Connection')<br> access.Open('PROVIDER=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft<br>Access Driver (*.mdb, *.accdb)};DBQ=C:\\temp\\Database1.accdb;')<br><br><br>Error:<br><br>C:\> a_db.py<br>Traceback (most recent call last):<br>File "D:\Data\Python Scripts\For the heck of sharepoint\a_db.py", line<br>11, in <module><br>access.Open('PROVIDER=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft Access<br>Driver (*.mdb, *.accdb)}<br>;DBQ=C:\\temp\\Database1.accdb;')<br>File "<COMObject ADODB.Connection>", line 3, in Open<br>File "C:\ActivePython27\lib\site-packages\win32com\client\dynamic.py",<br>line 276, in _ApplyTypes_<br>result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType,<br>argTypes) + args)<br>pywintypes.com_error: (-2147352567, 'Exception occurred.', (0,<br>u'ADODB.Connection', u'Provider canno<br>t be found. It may not be properly installed.',<br>u'C:\\Windows\\HELP\\ADO270.CHM', 1240655, -21468245<br>82), None)<br>_______________________________________________<br>python-win32 mailing list<br><a href="mailto:python-win32@python.org">python-win32@python.org</a><br><a href="http://mail.python.org/mailman/listinfo/python-win32" target="_blank">http://mail.python.org/mailman/listinfo/python-win32</a><o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p></div></body></html>