Solved

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

Posted on 2011-02-14
2
1,154 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:oraclexview
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34892850
You can use standard INSERT and UPDATE statments against Access as well, so you could just use the same basic syntax except with a valid SQL INSERT or UPDATE statement.

You can also open a Recordset, but unless you need to work with that recordset AFTER adding the new record, then there's really no reason to do this.

For example:

strSQL = "INSERT INTO tblBooks(BookName, BookAuthor) VALUES('My Pretty Pony', 'Stephen King')"

The nust build and run that Command object.
0
 

Author Closing Comment

by:oraclexview
ID: 34900681
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!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now