1. The first thing to do is run the makepy utility. It isn't necessary, but it improves speed and makes life in the PythonWin IDE that much easier. To do this select COM Makepy Utility from the PythonWin tools menu, and then select Microsoft ActiveX Data Objects 2.5 Library.

2. Next you will need a Data Source Name [DSN] and a connection object. [I tend to use DSN-Less connection strings (as opposed to system DSNs as it improves performance and makes code portable)]
   For MS Access you can just copy the DSN below. For other databases, or for more advanced options like passwords and the like go to [Control Panel | Administrative Tools | Data Sources (ODBC)]. From there you can set up a system DSN. You can then either use it as a system DSN, or copy it (it is just a text file) into a string and make a DSN-Less connection string. You might also do a search for DSN-Less connection strings on the web. Better yet, here are some samples for SQL Server, Access, FoxPro, Oracle , Oracle, Access, SQL Server, and finally MySQL.

           >>> import win32com.client
           >>> conn = win32com.client.Dispatch(r'ADODB.Connection')
           >>> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
           >>> conn.Open(DSN)
           

   With these set up access to the database is fairly straight forward.

3. The next task is to open a Recordset. For fancy stuff like joins and whatnot, you can use SQL SELECT * FROM table_name type statements, or just use the table name surrounded by brackets []

           >>> rs = win32com.client.Dispatch(r'ADODB.Recordset')
           >>> rs_name = 'MyRecordset'
           >>> rs.Open('[' + rs_name + ']', conn, 1, 3)
           

   [The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic and are well beyond the scope of this tutorial. I typically use these settings as defaults, but your mileage may vary. Pick up a book on ADO for details.]

4. With the recordset open you can examine the field names/indices like ...

           >>> flds_dict = {}
           >>> for x in range(rs.Fields.Count):
           ...     flds_dict[x] = rs.Fields.Item(x).Name
           

   A field's type and size are returned by ...

           >>> print rs.Fields.Item(1).Type
           202 # 202 is a text field
           >>> print rs.Fields.Item(1).DefinedSize
           50  # 50 Characters
           

5. Actual work can be done as well. Adding new records to recordsets can be done both with INSERT INTO (see below) statements, or directly using the AddNew() and Update() methods.

           >>> rs.AddNew()
           >>> rs.Fields.Item(1).Value = 'data'
           >>> rs.Update()
           

   These values can be also be returned.

           >>> x = rs.Fields.Item(1).Value
           >>> print x
           'data'
           

   So, if one wants to create a new Record, and know what number an AutoNumber field has generated for it without having to query the database ...

           >>> rs.AddNew()
           >>> x = rs.Fields.Item('Auto_Number_Field_Name').Value  
           # x contains the AutoNumber
           >>> rs.Fields.Item('Field_Name').Value = 'data'
           >>> rs.Update()
           

6. You can get a list of the Tables in a Database using ADO.

           >>> oCat = win32com.client.Dispatch(r'ADOX.Catalog')
           >>> oCat.ActiveConnection = conn
           >>> oTab = oCat.Tables
           >>> for x in oTab:
           ...     if x.Type == 'TABLE':
           ...         print x.Name
           

7. Close the connection. Notice that to close this connection the 'C' is upper case, whereas to close a file opened with python the 'c' is lower case.

           >>> conn.Close()
           

8. To use SQL to INSERT or UPDATE data, use a Connection object directly.

           >>> conn = win32com.client.Dispatch(r'ADODB.Connection')
           >>> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'
           >>> sql_statement = "INSERT INTO [Table_Name] 
                                ([Field_1], [Field_2]) VALUES ('data1', 'data2')"
           >>> conn.Open(DSN)
           >>> conn.Execute(sql_statement)
           >>> conn.Close()
           

9. Here is a last example that often seems to be a sticking point with ADO. Generally, if one wants to get the RecordCount of a table, one must move through all of the records counting them along the way like ...

           >>> # See example 3 above for the set-up to this 
           >>> rs.MoveFirst()
           >>> count = 0
           >>> while 1:
           ...     if rs.EOF:
           ...         break
           ...     else:
           ...         count = count + 1
           ...         rs.MoveNext()
           

   Aside from being horribly inefficient, if the recordset is empty, moving to the first record will generate an error. ADO provides a way to correct this. Before opening the recordset, set the CursorLocation to 3. After opening the recordset, the recordcount will be available.

           >>> rs.Cursorlocation = 3 # don't use parenthesis here
           >>> rs.Open('SELECT * FROM [Table_Name]', conn) # be sure conn is open
           >>> rs.RecordCount # no parenthesis here either
           186
           

   [Again, the 3 is a constant.]

Please contact me with questions or comments. I have placed a number of old questions and replies online. Feel free to contact me with questions, but I may use your question publicly. I will, of course, remove any identifying information.