[python-win32] Re: Problems using JET SQL CREATE TABLE statement

[George Vestergom]

2002 July 17, Wednesday

Problem:
When trying to use the CREATE TABLE statement (JET SQL), the table is
created but does not show up in the Database Window in ACCESS.
Enumerating the TableDefs collection in Python shows that the table has
been
added, but doesn't show up when I run ACCESS. Refreshing the window
doesn't do anything. When the same statement is performed in a VBA
Module, the table is created and shows up in the Database Window.

Assumptions:
1. ACCESS 2000 / DAO 3.6
2. Python 2.2
3. win32all-148
4. MakePy on  DAO 3.6 Object Library

Example Session using Pythonwin (just to illustrate what I've done
>>> # open a database and print out objects. 
>>> # database contains 2 tables

>>> from win32com.client import Dispatch, constants
>>> dbe = Dispatch('DAO.DBEngine.36')
>>> wrk = dbe.Workspaces(0)
>>> db = wrk.OpenDatabase('pydb.mdb')
>>> dbe
<win32com.gen_py.Microsoft DAO 3.6 Object Library._DBEngine>
>>> wrk
<win32com.gen_py.Microsoft DAO 3.6 Object Library.Workspace>
>>> db
<win32com.gen_py.Microsoft DAO 3.6 Object Library.Database>

>>> # enumerate TableDefs (system tables not shown) will print 2 tables
currently in the database:
>>> tbldefs = db.TableDefs
>>> for tbl in tbldefs:
        print tbl.Name

tblEx01
tblEx02

>>> # now lets create a new table using JET SQL 
>>> txtsql = 'CREATE TABLE tblNewTable (Name CHAR(40), City CHAR(40) );'
>>> db.Execute(txtsql)

Everything's okay here, table is created, no problem. Now let's refresh
TableDefs and print it out.

>>> db.TableDefs.Refresh()
>>> tbldefs = db.TableDefs
>>> for tbl in tbldefs:
        print i.Name

tblEx01
tblEx02
tblNewTable

If I switch to ACCESS and refresh the Database Window, table tblNewTable
is
not included in the list. 

I've tried the following scenarios:

1. If I close both ACCESS and Pythonwin and re-open the file with
ACCESS, the new table tblNewTable is not shown.

2. Closing ACCESS and running Pythonwin: table shows up in the
TableDefs collection!

3. Closing Pythonwin and opening ACCESS: If I use the CREATE TABLE (from
above) in a Sub procedure, using the SAME name, the table is created AND
SHOWS UP IN THE DATABASE WINDOW! One would think an error would have
been raised at this point. 

4. Closing ACCESS and opening Pythonwin: I can add new records to this
table using the following JET SQL INSERT statement:

>>> r = 'INSERT INTO tblNewTable (Name, City) VALUES ("George", "Toronto");'
>>> db.Execute(r)

and will show up in Python. No data is shown in ACCESS, even if the
table was first created using VBA in a module.

It may not be too important of a problem since I haven't had any problem
(so far) using DOA in Python. I just wanted to find out if JET SQL works
the same way and apparently I've come across these "warts".

Any ideas?

George Vestergom