Searching for txt file and importing to ms access

[J Correia]

"Mark Line" <mline at abilitec.com> wrote in message
news:dj8f1r$do7$1 at nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Hello!
>
>
<snip>

> I've also managed to connect to my access database, and just print out a
> field in a table, but I cant find anywhere on the web that will help me to
> import data?  Any help would be great?!
>
>
<snip>

Another method of talking to MS Access is to set up an ODBC datasource...
Control Panel > Data Sources (ODBC).   Then download and import
the mx.ODBC module... this worked a lot faster in my setup than using the
win32com route and i find the clear SQL layout simpler to understand.

Attached is some sample code I used to quickly get some data from SQL Server,
process it, and load into Access, both set up as ODBC data sources.

<Python Code>
import mx.ODBC.Windows

dbc1 = mx.ODBC.Windows.Connect('<SQLServer source>', user='<user>',
password='xxx', clear_auto_commit=0)
dbc2 = mx.ODBC.Windows.Connect('<MS Access source>', user='<user>',
password='xxx', clear_auto_commit=0)

    # Create cursors on databases.
    crsr1 = dbc1.cursor()
    crsr2 = dbc2.cursor()

    # Get record(s)  from SQL Server database.
    try:
        crsr1.execute(
            """
            SELECT product_id, image
            FROM SUP_CATALOGUE_PRODUCT
            """)
    except Exception, err:
        print "*** Error extracting records from SQL Server***"
        print "Exception:", Exception, "Error:", err
        sys.exit()
    else:
        results = crsr1.fetchall()      # fetch the results all at once into a
list.
        if not len(results):     # No records found to be processed.
            print "No records returned from SQL Server table, aborting..."
            sys.exit()
        else:                   # Have records to work with, continue processing
            print len(results), "records to be updated..."

            i = 0
            for item in results:
                ....
                ....  < processing of each record goes here.>
                ....

                # Now update 1 record in the Access table.
                try:
                    crsr2.execute(
                        """
                        UPDATE SUP_CATALOGUE_PRODUCT
                        SET image = '%s'
                        WHERE product_id = %d
                        """   %   (new_image, product_id)
                        )
                except Exception, err:
                    print "*** Error updating records in MS Access***"
                    print "Exception:", Exception, "Error:", err
                    sys.exit()

                i += 1

            print "All done... records written:", i

</Python Code>


HTH,

JC