Solved

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

Posted on 2011-02-14
2
1,203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

734 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