Welcome, guest | Sign In | My Account | Store | Cart

This script creates an Access 2000 table and adds a primary key to it.

Python, 41 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
		# attempt to attach to the database - if that fails, create a new one
		# use the file name as the base name for the database
		self.dbName = os.path.splitext(fileName)[0]
		dsn = 'Provider=Microsoft.Jet.OLEDB.4.0;' + \
				'Jet OLEDB:Engine Type=5;' + \
				'Data Source=' + 'test.mdb'

		# try to create the database
		catalog = win32com.client.Dispatch('ADOX.Catalog') 
		try:
			catalog.Create(dsn)
			catalog.ActiveConnection = dsn
		except:
			raise "Can't connect to database table"


		# create a table with the appropriate field names
		table = win32com.client.Dispatch('ADOX.Table') 
		self.rs = win32com.client.Dispatch('ADODB.Recordset')
		table.Name = "TabName"

		Column = win32com.client.Dispatch("ADOX.Column")
		Column.Name = "AutoNumber"
		Column.Type = 3 # adInteger
		Column.ParentCatalog = catalog
		Column.Properties('Autoincrement').Value = win32com.client.pywintypes.TRUE
		table.Columns.Append(Column)

		Key = win32com.client.Dispatch("ADOX.Key")
		Key.Name = "PrimaryKey"
		Key.Type = 1 #win32.com.client.constants.adKeyPrimary
		Key.RelatedTable = "TabName"
		Key.Columns.append("AutoNumber")
		table.Keys.Append(Key)

		# add other fields using table.Columns.Append()


		catalog.Tables.Append(table)
		del table
		del catalog

Many times, it is desirable to have a primary key in your table. Some reasons include: to enforce referential integrity, and to create views. I started with a Visual Basic sample that does the same thing, and pretty much rewrote that sample in Python.

Created by Theresa Stadheim on Mon, 15 Oct 2001 (PSF)
Python recipes (4591)
Theresa Stadheim's recipes (1)

Required Modules

  • (none specified)

Other Information and Tasks