Link to home
Start Free TrialLog in
Avatar of oraclexview
oraclexview

asked on

Open MS Access table, add new records, and update table using System.Data.Odbc in IronPython

I’m trying to open an MS Access table, add new records to it, and then update those added records using the System.Data.Odbc method from IronPython. I know this is possible since I was able to do this with VBScript using the createobject("adodb.recordset") method. At this point I figure instead of creating a recordset with a Client cursor to open the table I would need to use a datareader or a dataset somehow.  So I just need assistance with the proper syntax to do the same using System.Data.Odbc from IronPython.
Here is what I have so far which deletes all existing records from the “tblBooks" table, and this works perfectly.  Now what I want to do it open that same table where I can add new records to it, and then update the table with those added records.

import clr, os, sys
from os import path
clr.AddReference('System.Data')
from System.Data.Odbc import OdbcCommand, OdbcConnection

strScriptDirectory = os.path.dirname(__file__)
strComicBaseFile = strScriptDirectory + \\ComicBase.mdb
strMdbDataSource = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + strComicBaseFile
objConnMDBComicBase = OdbcConnection(strMdbDataSource)
objConnMDBComicBase.Open()
strSQL = "DELETE * FROM tblBooks"
objSQLCommand = OdbcCommand(strSQL, objConnMDBComicBase)
objSQLCommand.ExecuteNonQuery()

Open in new window


Thank you all for any assistance that you can provide.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of oraclexview
oraclexview

ASKER

Thank you LSMConsulting!  That was enough to get me what I wanted!  I found it interesting that using this method of updating a MS Access database file doesn't require a separate UPDATE command.  Here is the code (so others can benefit from what I've learned) I able to produce after utilizing your comment.

[code]import clr, os, sys
from os import path
clr.AddReference('System.Data')
from System.Data.Odbc import OdbcCommand, OdbcConnection
global strScriptDirectory, strComicBaseFile, strMdbDataSource, objConnMDBComicBase, strSQL, objSQLCommand

strScriptDirectory = os.path.dirname(__file__)
strComicBaseFile = strScriptDirectory + \\ComicBase.mdb
strMdbDataSource = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + strComicBaseFile
objConnMDBComicBase = OdbcConnection(strMdbDataSource)
objConnMDBComicBase.Open()
strSQL = "DELETE * FROM tblBooks"
objSQLCommand = OdbcCommand(strSQL, objConnMDBComicBase)
objSQLCommand.ExecuteNonQuery()

def UpdateComicBase2(books):
      for book in books:
            if book.ComicInfoIsDirty!="false":
                  strBookId = str(book.Id)
                  strSQLcolumns = "INSERT INTO tblBooks(\"Id\""
                  strSQLvalues = ") VALUES(\'" + strBookId + "\'"
                  strBookFile = str(book.FilePath)
                  strSQLcolumns = strSQLcolumns + ", \"File\""
                  strSQLvalues = strSQLvalues + ", \'" + strBookFile + "\'"
                  if book.Publisher != "":
                        strBookPublisher = str(book.Publisher)
                        strSQLcolumns = strSQLcolumns + ", \"Publisher\""
                        strSQLvalues = strSQLvalues + ", \'" + strBookPublisher + "\'"
                  if book.Series != "":
                        strBookSeries = str(book.Series)
                        strSQLcolumns = strSQLcolumns + ", \"Series\""
                        strSQLvalues = strSQLvalues + ", \'" + strBookSeries + "\'"
                  if book.Volume != -1:
                        strBookVolume = str(book.Volume)
                        strSQLcolumns = strSQLcolumns + ", \"Volume\""
                        strSQLvalues = strSQLvalues + ", \'" + strBookVolume + "\'"
                  if book.Format != "":
                        strBookFormat = str(book.Format)
                        strSQLcolumns = strSQLcolumns + ", \"Format\""
                        strSQLvalues = strSQLvalues + ", \'" + strBookFormat + "\'"
                  if book.Number != "":
                        strBookNumber = str(book.Number)
                        strSQLcolumns = strSQLcolumns + ", \"Number\""
                        strSQLvalues = strSQLvalues + ", \'" + strBookNumber + "\'"
                  if book.Tags != "":
                        strBookTags = str(book.Tags)
                        strSQLcolumns = strSQLcolumns + ", \"Tags\""
                        strSQLvalues = strSQLvalues + ", \'" + strBookTags + "\'"
                  strSQL = strSQLcolumns + strSQLvalues + ")"
                  objSQLCommand = OdbcCommand(strSQL, objConnMDBComicBase)
                  objSQLCommand.ExecuteNonQuery()[/code]

Thanks again for the assistance and enjoy the points!