Using ADO to Create Jet 3.51 Tables 
Author Message
 Using ADO to Create Jet 3.51 Tables

Hi,
I am trying to do a table creation with ADO, and jet 3.51 (to maintain
compatibility with access 97). The table gets created fine, however when
trying to add fields and their properties alot of strange errors occur.

Problem #1: When appending columns to the table, i get errors if I i try to
use a data type adVarWchar, adLongVarWChar, etc
Problem #2:  When trying to set a column to autoincrement I get the error
"3265 ADO could not find the object in the collection corresponding to the
name or ordinal reference requested by the application"
Problem #3: (I have lots of problems today. Sigh.) When the table is
created, rather than putting the columns in the table in the order I
specified them, it puts the columns in the database in alphabetical order.
How do I force ADO to make ItemNumber column 1 and ItemState column 2, etc?

Neither of these errors occur if I change the connection statement to
incorporate Jet 4.0, so I guess there is a different set of properties and
datatypes available for use with Jet 3.51. Can someone provide me with a
list of valid datatypes and  properties for a Jet 3.51 table creation with
ADO?
I have noted that if I use adChar,50 the table calls it text, however if I
do an adChar,500 the datatype is set to Memo. ??? Why?

Thanx
Kirk

'****************Code Starts Here

Private Sub Command1_Click()

Dim tbl As ADOX.Table
Dim cat As ADOX.Catalog
Dim idx As ADOX.Index
Dim Pkey As ADOX.Key

Set cat = New ADOX.Catalog
Dim col As New ADOX.Column
On Error GoTo MyError
    If Dir(App.Path & "\" & "test.mdb") > "" Then Kill App.Path & "\" &
"test.mdb"

    cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & _
    App.Path & "\" & "test.mdb"

    Set tbl = New ADOX.Table
    With tbl
        .Name = "ItemId"
        .Columns.Append "ItemNumber", adInteger
        .Columns.Append "ItemState", adBoolean
        .Columns.Append "DateOut", adDate
        .Columns.Append "ItemColour", adChar, 50
        .Columns.Append "ItemMemo", adChar, 500
        .Columns.append "test1",adVarWchar,50
'<--------------problem #1 Here
    End With
    cat.Tables.Append tbl
    With col
        .Name = "ItemNumber"
        .Type = adInteger
        .Properties(AutoIncrement) = True
'<--------------problem #2 Here
    End With
    cat.Tables("ItemId").Columns.Append col

    MsgBox ("The first table was created")
    Exit Sub

MyError:
    MsgBox (Err.Number & " " & Err.Description)

End Sub



Mon, 01 Jul 2002 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Using ADO to create jet 3.51 tables

2. ADO Table creation with Jet 3.51

3. Importing Jet 3.51 table into Jet 4.0 database

4. Q: How to use batch updates on acces using ado jet provider 3.51

5. Using ADO with Jet 3.51

6. ADO Jet 4.0 performance problem vs Jet 3.51

7. Converting JET 3.51(97) To Jet 4.0(2000) Using DAO In VB 5

8. ADO using 3.51 with Links to Access 97 tables

9. Jet DB Performance (Jet 3.51 vs. Jet 4.0)

10. Jet 3.51 and Index selection with ADO

11. Q:ADO AND MS JET 3.51

12. create/delete queries with adodb jet 3.51

 

 
Powered by phpBB® Forum Software