ADO - Help with Creating a Table

According to MSDN docs, if you want to create or link a table with ADO, you must go through the ADOX ( or catalog which is the same as the 'database' object in DAO ) hierarchy.

The example creates a new ACCESS database, looks at the existing system tables, creates a Table Object, gives that object a name and three fields then fails trying to append().

I know that it has been mentioned before that Winbatch doesn't do collections, but in the Script, using append() to add Column Objects to the Table appears to work, it is just a step down from adding the table to the catalog.

I hope some will give it a try. it is not a long script and I commented relevant sections. I would like to get this resolved, or at least get a more definitive answer as to why it fails.

; ///////////////////////////////////////////////////////
; Test Script for Working with JET Provider 4.0.
;
; Goal is to overcome or explain problems relating to
; Creation of 'Tables' in ACCESS Database with append()
; User should have either MDAC 2.1 or 2.5 installed
;
; STEPS in Script
; 1. Create New MDB file from 'scratch'
; 2. View System Tables in New File
; 3. Create a Table Object, define Table Name
; 4. Create Column objects, append to new Table
; 5. Append new table to Catlog <--- THIS SHOULD FAIL
;
; A 'workaround' would be to issue a "CREATE TABLE...."
; SQL statement, but this would not suffice with linked
; ISAM's or ODBC data sources
;
; Stan Littlefield 06/23/2000
; ///////////////////////////////////////////////////////



file = "C:\TEMP\NEW.MDB" ; change to suit your situation

IF FileExist(file) == @TRUE
  FileDelete(file)
Endif

crstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%file%;Jet OLEDB:Engine Type=4"

;creates a new ACCESS Database, Type=4 is Office 97 Compatible
oMDB = ObjectOpen("ADOX.Catalog")
oMDB.Create(crstring)

;the following has the same effect as creating a Connection Object
;the issuing Open()
oMDB.ActiveConnection = crstring

message(crstring,"Table Created - %file%")


; examine the table object, list the system tables
; which exist with all new MDB files
; just you show you can access the tables object
tbls = oMDB.Tables

nCnt = tbls.Count()

For i=0 to nCnt-1
    tbl = oMDB.Tables(i)
    name = tbl.Name
    message("Table # %i%",name)
Next

; create new Table Object
newTable =  ObjectOpen("ADOX.Table")

; set variables for table Types

adVarWChar = 202
adInteger  = 3

newTable.Name = "test"

cols = newtable.Columns

cols.append("ID",adInteger)     ; the append method works here
cols.append("ID2",adInteger)
cols.append("Name",adVarWChar)

nCnt = cols.Count()

; enumerate newly created Columns
For i=0 to nCnt-1
    col = newTable.Columns(i)
    name = col.Name
    message("Column # %i%",name)
Next

;this is where it all goes South
;message will display, so newtable.Type does not error
;but Type (which should be "TABLE" is null
message("Columns Added",newTable.Type)

;and this fails with OLE Exception Error
;to see it, uncomment the ErrorMode() lines
ErrorMode(@OFF)
tbls.append(newTable)

ObjectClose(newTable)
ObjectClose(oMDB)
ErrorMode(@CANCEL)

exit

Append Method (Tables)
Adds a new Table object to the Tables collection.

Syntax

Tables.Append Table

Parameters

Table A Variant value that contains a reference to the 
Table to append or the name of the table to create
and append.

Remarks

An error will occur if the provider does not support 
creating tables.

See Also
Example | Example

Columns and Tables Append Methods, Name Property 
Example (VB)

The following code demonstrates how to create 
a new table.

Sub CreateTable()

Dim tbl As New Table
Dim cat As New ADOX.Catalog

'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _ 
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"

tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl

End Sub 

[OLE Exception]
ADOX.Tables=Arguments are of the wrong type, are out of 
acceptable range, or are in conflict with one
another.

I just went over an article abput converting from DAO to ADO (OLE DB) and they used the same method to create a Table

1. OPen a Catalog

2. Set an Active Connection

3. OPen a Table Object

4. Populate the Table Properties

5. Append the table.

I am going to try to Open a table and append() a new column- then I'll rule out whether or not the append() method cannot be executed via Winbatch, or if it works I'll be even more confused.

(this is driving me nuts)

Having been unable to create a new 'linked' table with Winbatch and ADOX, I decided to attempt an end-run; create an MDB with a Linked Table, open the MDB and change specific properties of the table to the desired link I wanted.

Changing tbl.Name, is simple, you can change the name of any table almost on-the-fly. However, according to the MSDN documentation on ADOX, other table properties can either be built-in, or dynamic (Provider Specific). Since my tests were primarily with Jet 4.0, I assumed the properties were built-in, and always accesible.

It does appear that:

tbl = ObjectOpen("ADOX.Table") // in Winbatch

Anyway, back to the end-run. I created an MDB called NEW and created a linked table called GRPT (which linked to an external DBF File)

; this code opens the catalog and creates a Table Object
file = "C:\TEMP\NEW.MDB"

crstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%file%;Jet OLEDB:Engine Type=4"

cat = ObjectOpen("ADOX.Catalog")
tbl = ObjectOpen("ADOX.Table")
cat.ActiveConnection = crstring

; now I assign the table object to a table I know exists
; and the 'properties' appear built-in as I can enurmerate them

tbl = cat.Tables("GRPT")

;issue the next statment and the Table name is Changed in the MDB
tbl.Name = "RAO"

props = tbl.Properties
nCnt = props.Count()

For i=0 to nCnt-1
nProp = tbl.Properties(i)
cName = nProp.Name
cValu = nProp.Value

; show the Property name and Current Value
message("Property %cName%",cValu)


; now in order to modify the properties necessary
; to create a new link
If cName == "Jet OLEDB:Create Link"
cValu = -1
nProp.Value = cValu <---- OLE EXCEPTION
Endif


If cName == "Jet OLEDB:Remote Table Name"
cValu = "RAO#DBF"
Endif
If cName == "Jet OLEDB:Link Provider String"
cValu = "dBase III;HDR=NO;IMEX=2;"
Endif
If cName == "Jet OLEDB:Link Datasource"
cValu = "E:\wbdemo"
Endif

Next

ObjectClose(tbl)
ObjectClose(cat)

I then tried to format

cmd = ObjectOpen("ADOX.Command")