need help with adox.catalog using script for creating db, tables and columns

hello, below is what I have so far, it creates db if it doesn't exist and also creates the tables if they don't exist so that part works.

it doesn't create the column names, but there also is no error... so I don't know how to add the columns in there to an existing table, could someone please correct my code?

the n.fileexists is equivalent to fso stuff, so you can just ignore it. those parts work without problem, it's the adding of columns to the table already in db I can't get to work

TIA
function createdb(sDatabaseName)
 
sDatabaseName = CURRENT_DIRECTORY & "\root.mdb"
 
if not n.fileexists(sDatabaseName) then 
 
		Dim catNewDB ' As ADOX.Catalog
		Set catNewDB = Server.CreateObject("ADOX.Catalog")
		catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabaseName & ";Jet OLEDB:Engine Type=5;"
		' Engine Type=5 = Access 2000 Database
		' Engine Type=4 = Access 97 Database
		Set catNewDB = Nothing
 
end if
 
 
Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
 
objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabaseName
 
Dim objTable, objColumn
For Each objTable in objADOXDatabase.Tables
  If objTable.Type = "TABLE" then 
    Response.Write objTable.Name & "<br>"
	  tablesstr = tablesstr & "|" & objTable.Name & "|"
 
    For Each objColumn in objTable.Columns
      Response.Write "&nbsp;&nbsp;&nbsp;" & objColumn.Name & "<br>"
	  columnstr = columnstr & "|" & objTable.Name & ":" & objColumn.Name & "|" 
	  Next
 
  End If
Next
 
Set objADOXDatabase = Nothing
 
 
 
dbcolumns = "configuration|ID|3|True|1|1*configuration|item|202|False|2|0*configuration|itemstr|202|False|2|0*configguration|ID|3|True|1|1*configguration|item|202|False|2|0"
 
dbcolumnsarray = split(dbcolumns,"*")
 
		Dim catDB ' As ADOX.Catalog
		Set catDB = Server.CreateObject("ADOX.Catalog")
		' Open the catalog
		catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabaseName
		Dim tblNew ' As ADOX.Table
		Dim col ' As ADOX.Column
response.write tablesstr & "<BR>"
for x = 0 to ubound(dbcolumnsarray)
 
tmparr = split(dbcolumnsarray(x),"|")
response.write tmparr(0) & "|" & "<BR>"
		if instr(tablesstr, "|" & tmparr(0) & "|") = 0 then
		Set tblNew = Server.CreateObject("ADOX.Table") 
		tblNew.Name = tmparr(0) 
		on error resume next
		catDB.Tables.Append tblNew
		on error goto 0
		Set tblNew = Nothing
		end if
 
next
response.write columnstr & "<BR>"
 
for x = 0 to ubound(dbcolumnsarray)
 
tmparr = split(dbcolumnsarray(x),"|")
 
if instr(columnstr, "|" & tmparr(0) & ":" & tmparr(1) & "|") = 0 then
 
		Set tblNew = Server.CreateObject("ADOX.Table") 
		tblNew.Name = tmparr(0) 
 
		Set col = Server.CreateObject("ADOX.Column")
		
			col.ParentCatalog = catDB
			col.Name = tmparr(1)
			col.Type = int(tmparr(2))'adInteger
			if tmparr(3) = "True" then col.Properties("Autoincrement") = True
			col.Attributes = int(tmparr(4))'1 default 2 nullable
		
		tblNew.Columns.Append col
 
		if int(tmparr(5)) = 1 then tblNew.Keys.Append "PrimaryKey", 1, tmparr(1)
 
 
		Set col = Nothing
		Set tblNew = Nothing
response.write "column added " & tmparr(1) & "<br>"
 
end if
'response.write columnstr & "--------" & tmparr(0) & "|" & tmparr(1) & ":" & "<br>"
 
next
 
 
		Set catDB = Nothing
	
 
end function

Open in new window

quantumriderAsked:
Who is Participating?
All Courses

From novice to tech pro — start learning today.