conecting with a MsAcces DB by dao

Discussion in 'Python' started by luis, Jun 30, 2006.

  1. luis

    luis Guest

    Hi
    I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
    (reading first http://starship.python.net/crew/bwilk/access.html)
    I have writed the following code

    def append_from_Access(self):
    try:
    import ...
    conn = win32com.client.Dispatch(r'ADODB.Connection')
    DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
    SOURCE=C:/Afile.mdb;"
    conn.Open(DSN)
    except Exception, inst:
    ...
    try:
    sql_statement='SELECT * FROM Mytable'
    rs = win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(sql_statement, conn, 1, 3)
    while not rs.EOF:
    id=rs.Fields(colName.Value) #colName, valid column name
    ...
    rs.MoveNext()
    rs.Close()
    conn.Close()

    except Exception, inst:
    ...

    I'm using it for reading tables or queries in a mdb file.
    With some mdb it works fine and return a no empty recordset, but with
    others mdb files, the recordsets are void (opening the tables or
    recorsets with Ms Access are not void).
    Some help is welcome,
    Thanks in advance
    Luis
    luis, Jun 30, 2006
    #1
    1. Advertising

  2. luis

    Iain King Guest

    luis wrote:
    > Hi
    > I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
    > (reading first http://starship.python.net/crew/bwilk/access.html)
    > I have writed the following code
    >
    > def append_from_Access(self):
    > try:
    > import ...
    > conn = win32com.client.Dispatch(r'ADODB.Connection')
    > DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
    > SOURCE=C:/Afile.mdb;"
    > conn.Open(DSN)
    > except Exception, inst:
    > ...
    > try:
    > sql_statement='SELECT * FROM Mytable'
    > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > rs.Open(sql_statement, conn, 1, 3)
    > while not rs.EOF:
    > id=rs.Fields(colName.Value) #colName, valid column name
    > ...
    > rs.MoveNext()
    > rs.Close()
    > conn.Close()
    >
    > except Exception, inst:
    > ...
    >
    > I'm using it for reading tables or queries in a mdb file.
    > With some mdb it works fine and return a no empty recordset, but with
    > others mdb files, the recordsets are void (opening the tables or
    > recorsets with Ms Access are not void).
    > Some help is welcome,
    > Thanks in advance
    > Luis


    I don't know if it's the problem your asking about, but your
    rs.MoveNext() should be inside the while loop, no?

    Iain
    Iain King, Jun 30, 2006
    #2
    1. Advertising

  3. luis

    luis Guest

    Iain King ha escrito:

    > luis wrote:
    > > Hi
    > > I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
    > > (reading first http://starship.python.net/crew/bwilk/access.html)
    > > I have writed the following code
    > >
    > > def append_from_Access(self):
    > > try:
    > > import ...
    > > conn = win32com.client.Dispatch(r'ADODB.Connection')
    > > DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
    > > SOURCE=C:/Afile.mdb;"
    > > conn.Open(DSN)
    > > except Exception, inst:
    > > ...
    > > try:
    > > sql_statement='SELECT * FROM Mytable'
    > > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > > rs.Open(sql_statement, conn, 1, 3)
    > > while not rs.EOF:
    > > id=rs.Fields(colName.Value) #colName, valid column name
    > > ...
    > > rs.MoveNext()
    > > rs.Close()
    > > conn.Close()
    > >
    > > except Exception, inst:
    > > ...
    > >
    > > I'm using it for reading tables or queries in a mdb file.
    > > With some mdb it works fine and return a no empty recordset, but with
    > > others mdb files, the recordsets are void (opening the tables or
    > > recorsets with Ms Access are not void).
    > > Some help is welcome,
    > > Thanks in advance
    > > Luis

    >
    > I don't know if it's the problem your asking about, but your
    > rs.MoveNext() should be inside the while loop, no?

    Yes, is inside
    >
    > Iain
    luis, Jun 30, 2006
    #3
  4. luis

    Iain King Guest

    luis wrote:
    > Iain King ha escrito:
    >
    > > luis wrote:
    > > > while not rs.EOF:
    > > > id=rs.Fields(colName.Value) #colName, valid column name
    > > > ...
    > > > rs.MoveNext()
    > > > rs.Close()
    > > > conn.Close()

    > >
    > > I don't know if it's the problem your asking about, but your
    > > rs.MoveNext() should be inside the while loop, no?

    > Yes, is inside
    > >


    You mean, it is inside the while loop in your code, but you made a
    mistake copying it into your post? In the code you posted it is not
    inside the while loop - it would have to be indented one more level for
    that.

    Iain
    Iain King, Jun 30, 2006
    #4
  5. luis

    luis Guest

    Iain King ha escrito:

    > luis wrote:
    > > Iain King ha escrito:
    > >
    > > > luis wrote:
    > > > > while not rs.EOF:
    > > > > id=rs.Fields(colName.Value) #colName, valid column name
    > > > > ...
    > > > > rs.MoveNext()
    > > > > rs.Close()
    > > > > conn.Close()
    > > >
    > > > I don't know if it's the problem your asking about, but your
    > > > rs.MoveNext() should be inside the while loop, no?

    > > Yes, is inside
    > > >

    >
    > You mean, it is inside the while loop in your code, but you made a
    > mistake copying it into your post? In the code you posted it is not
    > inside the while loop - it would have to be indented one more level for
    > that.
    >
    > Iain


    this is te correct identation

    def append_from_Access(self):
    try:
    import ...
    conn = win32com.client.Dispatch(r'ADODB.Connection')
    DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
    SOURCE=C:/Afile.mdb;"
    conn.Open(DSN)
    except Exception, inst:
    ...
    try:
    sql_statement='SELECT * FROM Mytable'
    rs = win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(sql_statement, conn, 1, 3)
    while not rs.EOF:
    id=rs.Fields(colName.Value) #colName, valid column name
    ...
    rs.MoveNext()
    rs.Close()
    conn.Close()

    except Exception, inst:
    ...

    I think my problem must be with ado and dao.
    Now I have run makepy utility and select Microsoft ActiveX Data Objects
    2.5 Library, perhaps I must also select Microsoft DAO3.5 Object Library
    and write
    win32com.client.Dispatch("DAO.DBEngine.35") for Access 97 or
    win32com.client.Dispatch(r'ADODB.Connection') for Acess 2000
    Do you know is it possible ?
    luis
    luis, Jun 30, 2006
    #5
  6. luis

    Iain King Guest

    luis wrote:
    > Iain King ha escrito:
    >
    > > luis wrote:
    > > > Iain King ha escrito:
    > > >
    > > > > luis wrote:
    > > > > > while not rs.EOF:
    > > > > > id=rs.Fields(colName.Value) #colName, valid column name
    > > > > > ...
    > > > > > rs.MoveNext()
    > > > > > rs.Close()
    > > > > > conn.Close()
    > > > >
    > > > > I don't know if it's the problem your asking about, but your
    > > > > rs.MoveNext() should be inside the while loop, no?
    > > > Yes, is inside
    > > > >

    > >
    > > You mean, it is inside the while loop in your code, but you made a
    > > mistake copying it into your post? In the code you posted it is not
    > > inside the while loop - it would have to be indented one more level for
    > > that.
    > >
    > > Iain

    >
    > this is te correct identation
    >
    > def append_from_Access(self):
    > try:
    > import ...
    > conn = win32com.client.Dispatch(r'ADODB.Connection')
    > DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
    > SOURCE=C:/Afile.mdb;"
    > conn.Open(DSN)
    > except Exception, inst:
    > ...
    > try:
    > sql_statement='SELECT * FROM Mytable'
    > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > rs.Open(sql_statement, conn, 1, 3)
    > while not rs.EOF:
    > id=rs.Fields(colName.Value) #colName, valid column name
    > ...
    > rs.MoveNext()
    > rs.Close()
    > conn.Close()
    >
    > except Exception, inst:
    > ...
    >
    > I think my problem must be with ado and dao.
    > Now I have run makepy utility and select Microsoft ActiveX Data Objects
    > 2.5 Library, perhaps I must also select Microsoft DAO3.5 Object Library
    > and write
    > win32com.client.Dispatch("DAO.DBEngine.35") for Access 97 or
    > win32com.client.Dispatch(r'ADODB.Connection') for Acess 2000
    > Do you know is it possible ?
    > luis


    Well, without being able to test on your system I don't think I can
    give you any real advice. This is the module I use to interface with
    Access:

    Access.py
    ---------------
    import win32com.client
    from win32com.client import constants

    def isWriteable(field):
    """Is given Field writeable?"""
    return field.Attributes & 4


    class Access(object):
    def __init__(self, filename, password=""):
    self._filename = filename
    self._connection = win32com.client.Dispatch(r'ADODB.Connection')
    if password:
    self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;Jet
    OLEDB:Database Password=%s;' % (filename, password)
    else:
    self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;' %
    (filename)

    def Query(self, query):
    self._connection.Open(self._DSN)
    rs = win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(query, self._connection, 1, 3)
    fields = []
    for x in xrange(rs.Fields.Count):
    fields.append(rs.Fields(x).Name)
    if rs.EOF:
    data = []
    else:
    data = rs.GetRows()
    rs.Close()
    self._connection.Close()
    return fields, data


    def Add(self, table, records):
    """Adds records to table."""
    self._connection.Open(self._DSN)
    rs = win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(table, self._connection, 1, 3)
    unwriteables = []
    for record in records:
    rs.AddNew()
    unwriteable = []
    for i in xrange(len(record)):
    if isWriteable(rs.Fields(i)):
    rs.Fields(i).Value = record
    else:
    unwriteable.append(rs.Fields(i).Value)
    unwriteables.append(unwriteable)
    rs.Update()
    rs.Close()
    self._connection.Close()
    return unwriteables


    def Update(self, query, function):
    """Updates all records found in query with function(record)"""
    self._connection.Open(self._DSN)
    rs = win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(query, self._connection, 1, 3)
    columns = rs.Fields.Count
    while not rs.EOF:
    record = []
    for i in xrange(columns):
    record.append(rs.Fields(i).Value)
    newRecord = function(record[:])
    for i in xrange(columns):
    if isWriteable(rs.Fields(i)):
    rs.Fields(i).Value = newRecord
    rs.MoveNext()
    rs.Close()
    self._connection.Close()


    def Delete(self, query):
    """Deletes all records found in query"""
    self._connection.Open(self._DSN)
    rs = win32com.client.Dispatch(r'ADODB.Recordset')
    rs.Open(query, self._connection, 1, 3)
    while not rs.EOF:
    rs.Delete()
    rs.MoveNext()
    rs.Close()
    self._connection.Close()


    We only have Access 2000+, so I don't know if it'll work with prior
    versions.

    Iain
    Iain King, Jun 30, 2006
    #6
  7. On 29 Jun 2006 23:50:08 -0700, "luis" <> declaimed the
    following in comp.lang.python:

    > Hi
    > I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002


    Technically, you are using M$ JET -- Access is really just a GUI
    form/report generator/front-end to a database engine. The "native"
    engine being Jet -- but "Access Data Projects" use M$ SQL Server as the
    engine.

    > while not rs.EOF:
    > id=rs.Fields(colName.Value) #colName, valid column name
    > ...
    > rs.MoveNext()


    Is that indentation correct? It looks very much as if the "move
    next" is outside the while loop.

    Unfortunately, between DAO, ADO, and ODBC -- I've never managed to
    develop a firm basis for how to manipulate Jet data... Heck, one of the
    few things I'd done in VBA (internal to Access) failed on the next
    computer I obtained -- because Access changed from DAO to ADO as the
    default, and I had a DAO-only feature <G>
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Jun 30, 2006
    #7
  8. luis

    luis Guest

    Iain King ha escrito:

    > luis wrote:
    > > Iain King ha escrito:
    > >
    > > > luis wrote:
    > > > > Iain King ha escrito:
    > > > >
    > > > > > luis wrote:
    > > > > > > while not rs.EOF:
    > > > > > > id=rs.Fields(colName.Value) #colName, valid column name
    > > > > > > ...
    > > > > > > rs.MoveNext()
    > > > > > > rs.Close()
    > > > > > > conn.Close()
    > > > > >
    > > > > > I don't know if it's the problem your asking about, but your
    > > > > > rs.MoveNext() should be inside the while loop, no?
    > > > > Yes, is inside
    > > > > >
    > > >
    > > > You mean, it is inside the while loop in your code, but you made a
    > > > mistake copying it into your post? In the code you posted it is not
    > > > inside the while loop - it would have to be indented one more level for
    > > > that.
    > > >
    > > > Iain

    > >
    > > this is te correct identation
    > >
    > > def append_from_Access(self):
    > > try:
    > > import ...
    > > conn = win32com.client.Dispatch(r'ADODB.Connection')
    > > DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
    > > SOURCE=C:/Afile.mdb;"
    > > conn.Open(DSN)
    > > except Exception, inst:
    > > ...
    > > try:
    > > sql_statement='SELECT * FROM Mytable'
    > > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > > rs.Open(sql_statement, conn, 1, 3)
    > > while not rs.EOF:
    > > id=rs.Fields(colName.Value) #colName, valid column name
    > > ...
    > > rs.MoveNext()
    > > rs.Close()
    > > conn.Close()
    > >
    > > except Exception, inst:
    > > ...
    > >
    > > I think my problem must be with ado and dao.
    > > Now I have run makepy utility and select Microsoft ActiveX Data Objects
    > > 2.5 Library, perhaps I must also select Microsoft DAO3.5 Object Library
    > > and write
    > > win32com.client.Dispatch("DAO.DBEngine.35") for Access 97 or
    > > win32com.client.Dispatch(r'ADODB.Connection') for Acess 2000
    > > Do you know is it possible ?
    > > luis

    >
    > Well, without being able to test on your system I don't think I can
    > give you any real advice. This is the module I use to interface with
    > Access:
    >
    > Access.py
    > ---------------
    > import win32com.client
    > from win32com.client import constants
    >
    > def isWriteable(field):
    > """Is given Field writeable?"""
    > return field.Attributes & 4
    >
    >
    > class Access(object):
    > def __init__(self, filename, password=""):
    > self._filename = filename
    > self._connection = win32com.client.Dispatch(r'ADODB.Connection')
    > if password:
    > self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;Jet
    > OLEDB:Database Password=%s;' % (filename, password)
    > else:
    > self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;' %
    > (filename)
    >
    > def Query(self, query):
    > self._connection.Open(self._DSN)
    > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > rs.Open(query, self._connection, 1, 3)
    > fields = []
    > for x in xrange(rs.Fields.Count):
    > fields.append(rs.Fields(x).Name)
    > if rs.EOF:
    > data = []
    > else:
    > data = rs.GetRows()
    > rs.Close()
    > self._connection.Close()
    > return fields, data
    >
    >
    > def Add(self, table, records):
    > """Adds records to table."""
    > self._connection.Open(self._DSN)
    > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > rs.Open(table, self._connection, 1, 3)
    > unwriteables = []
    > for record in records:
    > rs.AddNew()
    > unwriteable = []
    > for i in xrange(len(record)):
    > if isWriteable(rs.Fields(i)):
    > rs.Fields(i).Value = record
    > else:
    > unwriteable.append(rs.Fields(i).Value)
    > unwriteables.append(unwriteable)
    > rs.Update()
    > rs.Close()
    > self._connection.Close()
    > return unwriteables
    >
    >
    > def Update(self, query, function):
    > """Updates all records found in query with function(record)"""
    > self._connection.Open(self._DSN)
    > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > rs.Open(query, self._connection, 1, 3)
    > columns = rs.Fields.Count
    > while not rs.EOF:
    > record = []
    > for i in xrange(columns):
    > record.append(rs.Fields(i).Value)
    > newRecord = function(record[:])
    > for i in xrange(columns):
    > if isWriteable(rs.Fields(i)):
    > rs.Fields(i).Value = newRecord
    > rs.MoveNext()
    > rs.Close()
    > self._connection.Close()
    >
    >
    > def Delete(self, query):
    > """Deletes all records found in query"""
    > self._connection.Open(self._DSN)
    > rs = win32com.client.Dispatch(r'ADODB.Recordset')
    > rs.Open(query, self._connection, 1, 3)
    > while not rs.EOF:
    > rs.Delete()
    > rs.MoveNext()
    > rs.Close()
    > self._connection.Close()
    >
    >
    > We only have Access 2000+, so I don't know if it'll work with prior
    > versions.
    >
    > Iain


    Thanks, for your code

    My problem was opening a query (not a table) on mdb file

    1) If the query includes a where clause type
    field1="edf" and field2=3
    , for example, no problem, Access can retrieve a not empty recordset
    and my python code too.

    2) But if the Access's query includes a LIKE clause, for example
    field1="e*"
    , Access can retrieves a not empty recordset but my python code
    retrieves a empty recordset.

    If I write the WHERE clause inside my python code, the recorset returns
    an non empty recordset. Perhaps the problem was the different use of *
    and % in Access and SQL, I don't know.

    Luis
    luis, Jul 3, 2006
    #8
  9. luis wrote:

    > My problem was opening a query (not a table) on mdb file
    >
    > 1) If the query includes a where clause type
    > field1="edf" and field2=3
    > , for example, no problem, Access can retrieve a not empty recordset
    > and my python code too.
    >
    > 2) But if the Access's query includes a LIKE clause, for example
    > field1="e*"
    > , Access can retrieves a not empty recordset but my python code
    > retrieves a empty recordset.


    Which is exactly what it _should_ return on that query, unless you have
    records with "e*" in column field1.

    The proper SQL clause is:

    ... WHERE field1 LIKE 'e*' ...

    But, you did not include that part of the program, so you wound up
    crippling those who were willing to try to help you, because you
    thought you kinda-sorta knew what was going on (but not enough to
    fix it). Find "smart questions" and read it a couple of times.


    --
    --Scott David Daniels
    Scott David Daniels, Jul 3, 2006
    #9
  10. luis

    Tim Chase Guest

    >> , Access can retrieves a not empty recordset but my python code
    >> retrieves a empty recordset.

    >
    > Which is exactly what it _should_ return on that query, unless you have
    > records with "e*" in column field1.
    >
    > The proper SQL clause is:
    >
    > ... WHERE field1 LIKE 'e*' ...



    which would also return an empty recordset unless the OP has
    records with "e*" in column field1...

    the proper SQL clause would be

    ... WHERE field1 LIKE 'e%' ...

    (in SQL, the "%" and "_" are used as wildcards, not asterisk and
    question-mark respectively as they are in most shell expansions)

    :)

    -tkc
    Tim Chase, Jul 3, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mirek

    asp.net and dao?

    mirek, Aug 4, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    2,267
    Mirek Rewak
    Aug 6, 2003
  2. Otuatail

    Conecting to Access via DSN

    Otuatail, Dec 5, 2003, in forum: HTML
    Replies:
    3
    Views:
    406
    Whitecrest
    Dec 6, 2003
  3. =?Utf-8?B?cGVsZWdrMQ==?=

    asp.net and conecting to sql server2005

    =?Utf-8?B?cGVsZWdrMQ==?=, Jul 7, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    348
    =?Utf-8?B?cGVsZWdrMQ==?=
    Jul 8, 2007
  4. Bigadds

    Conecting web buttons to a web service

    Bigadds, Jul 12, 2007, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    81
    Manish Bafna
    Jul 13, 2007
  5. Maarten Porters
    Replies:
    1
    Views:
    413
    Florian Gilcher
    Jul 28, 2008
Loading...

Share This Page