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

[Jesper Hertel]

Hi George,

any news on this subject?

I have a similar problem, although not from within Python, but from Visual
Basic 6. I'd like to hear of any solutions.


Best regards,


Jesper Hertel
Software Developer
Magnus Informatik A/S (a Wolters Kluwer company)
Denmark
jh@magnus.dk <mailto:jh@magnus.dk>  


-----Original Message-----
From: Kunkle, Rob E [mailto:REKd@pge.com]
Sent: Friday, July 19, 2002 12:48 AM
To: python-win32@python.org
Cc: George Vestergom
Subject: RE: [python-win32] Re: Problems using JET SQL CREATE TABLE
statem ent


Hi George -

I know the Access tables have a property that says whether or not they are
Visible in the database window. Maybe ADO does not do this by default? 

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
 




-----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,
 
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 executing
the statements in Python the objects do not get deleted and somehow the data
is not being written through to the file. Some of the scenarios support this
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 calling
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?
 
BTW I use ADO myself and manipulate Jet databases fairly often and at times
use Access in tandem and haven't seen any problems like this. You can create
new Jet databases too, you just use ADOX. ADOX also let's you get lists of
tables, etc.

	-----Original Message----- 
	From: George Vestergom [mailto:unifoam@total.net] 
	Sent: Wed 7/17/2002 10:22 AM 
	To: 'python-win32@python.org' 
	Cc: 
	Subject: [python-win32] Re: Problems using JET SQL CREATE TABLE
statement
	
	


	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
	
	
	_______________________________________________
	Python-win32 mailing list
	Python-win32@python.org
	http://mail.python.org/mailman/listinfo/python-win32
	

?+a|"}j)b	b')	ڜm࠶+az+᥃Yb࠲~	hᢟ


_______________________________________________
Python-win32 mailing list
Python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32