[python-win32] Re: Problems using JET SQL CREATE TABLE statem
ent
Kunkle, Rob E
REKd@pge.com
Thu, 18 Jul 2002 15:48:11 -0700
Hi George -
I know the Access tables have a property that says whether or not they ar=
e
Visible in the database window. Maybe ADO does not do this by default?=20
I can't find the details of this, but it could be that you just need to
somehow manipulate the extended properties of the table object and flip a
switch. Probably ADOX is your best bet to get more control of the objects.
Rob
=20
-----Original Message-----
From: Jorgensen, Jens [mailto:jjorgensen@Tallan.com]
Sent: Wednesday, July 17, 2002 1:43 PM
To: George Vestergom; python-win32@python.org
Subject: RE: [python-win32] Re: Problems using JET SQL CREATE TABLE
statement
George,
=20
Although I can't be certain I'm going to guess that the differences in
behavior between Python and VBS are occuring because when you are executi=
ng
the statements in Python the objects do not get deleted and somehow the d=
ata
is not being written through to the file. Some of the scenarios support t=
his
and others contradict it but I still believe this is the case. Since the
interface is COM the COM objects themselves don't know if python is calli=
ng
them or VBS is and the results should be no different. Try putting your
statements into a .py file and running the scripts with python.exe rathen
than pythonwin. Any difference observed then?
=20
BTW I use ADO myself and manipulate Jet databases fairly often and at tim=
es
use Access in tandem and haven't seen any problems like this. You can cre=
ate
new Jet databases too, you just use ADOX. ADOX also let's you get lists o=
f
tables, etc.
-----Original Message-----=20
From: George Vestergom [mailto:unifoam@total.net]=20
Sent: Wed 7/17/2002 10:22 AM=20
To: 'python-win32@python.org'=20
Cc:=20
Subject: [python-win32] Re: Problems using JET SQL CREATE TABLE
statement
=09
=09
2002 July 17, Wednesday
=09
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.
=09
Assumptions:
1. ACCESS 2000 / DAO 3.6
2. Python 2.2
3. win32all-148
4. MakePy on DAO 3.6 Object Library
=09
Example Session using Pythonwin (just to illustrate what I've done
>>> # open a database and print out objects.
>>> # database contains 2 tables
=09
>>> from win32com.client import Dispatch, constants
>>> dbe =3D Dispatch('DAO.DBEngine.36')
>>> wrk =3D dbe.Workspaces(0)
>>> db =3D 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>
=09
>>> # enumerate TableDefs (system tables not shown) will print 2
tables
currently in the database:
>>> tbldefs =3D db.TableDefs
>>> for tbl in tbldefs:
print tbl.Name
=09
tblEx01
tblEx02
=09
>>> # now lets create a new table using JET SQL
>>> txtsql =3D 'CREATE TABLE tblNewTable (Name CHAR(40), City CHAR(40)
);'
>>> db.Execute(txtsql)
=09
Everything's okay here, table is created, no problem. Now let's
refresh
TableDefs and print it out.
=09
>>> db.TableDefs.Refresh()
>>> tbldefs =3D db.TableDefs
>>> for tbl in tbldefs:
print i.Name
=09
tblEx01
tblEx02
tblNewTable
=09
If I switch to ACCESS and refresh the Database Window, table
tblNewTable
is
not included in the list.
=09
I've tried the following scenarios:
=09
1. If I close both ACCESS and Pythonwin and re-open the file with
ACCESS, the new table tblNewTable is not shown.
=09
2. Closing ACCESS and running Pythonwin: table shows up in the
TableDefs collection!
=09
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.
=09
4. Closing ACCESS and opening Pythonwin: I can add new records to
this
table using the following JET SQL INSERT statement:
=09
>>> r =3D 'INSERT INTO tblNewTable (Name, City) VALUES ("George",
"Toronto");'
>>> db.Execute(r)
=09
and will show up in Python. No data is shown in ACCESS, even if the
table was first created using VBA in a module.
=09
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".
=09
Any ideas?
=09
George Vestergom
=09
=09
_______________________________________________
Python-win32 mailing list
Python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
=09
?+a|"}j)b b=13=1A') =DA=9C=08m=E0=A0=B6+az+=E1=A5=83Yb=E0=A0=B2~ h=E1=A2=9F=
=08