Type invalid... worked 5minutes ago though!

[gwar2k1]

OMG cant believe this has happened! This is due for tomorrow and I didnt even touch this subroutine... now its not working ='( Im hoping someone can help me.

Error: Type is invalid.

Private Sub Database_ProductTable(AbsDir, CompName As String)
Rem creates a table and adds it to the database catalogue

Dim DbaseCat As ADOX.Catalog
Dim newTBL As ADOX.Table
Dim rsProd As ADODB.Recordset 'product record structure
Dim adColNullable
Dim keyIndex As New ADOX.index

  adColNullable = 2
  Set DbaseCat = New ADOX.Catalog
  DbaseCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & AbsDir & CompName
  Set newTBL = New ADOX.Table
  With newTBL
    .Name = "Product"
    With .Columns
      .Append "EAN 8/13", adWChar
      .Append "Title", adWChar
      .Append "Price", adDecimal
      .Append "Category", adWChar
      .Append "Length", adWChar
    End With
    .Columns("EAN 8/13").Attributes = adColNullable
    .Columns("Title").Attributes = adColNullable
    .Columns("Price").Attributes = adColNullable
    .Columns("Category").Attributes = adColNullable
    .Columns("Length").Attributes = adColNullable
  End With
  keyIndex.Name = "EAN 8/13"
  keyIndex.Unique = True
  keyIndex.PrimaryKey = True
  keyIndex.Columns.Append "EAN 8/13"
  newTBL.Indexes.Append keyIndex
  
  [b]DbaseCat.Tables.Append newTBL[/b]
  DbaseCat.ActiveConnection = Nothing

  Set DbaseCat = Nothing
  Set newTBL = Nothing

End Sub

Seriously I dont know what happened. I merely added this code which I now know is wrong as Im using conflicting types DAO and ADO but I should be able to fix that up by myself.

Private Sub Database_CustomerProductTable(AbsDir, CompName As String)
Rem creates a table and adds it to the database catalogue

Dim DbaseCat As ADOX.Catalog
Dim newTBL As ADOX.Table
Dim adColNullable
Dim keyIndex As New ADOX.index
Dim tblRelation As New Relation
Dim ForeignFld As Field

  adColNullable = 2
  Set DbaseCat = New ADOX.Catalog
  DbaseCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & AbsDir & CompName
  Set newTBL = New ADOX.Table
  With newTBL
    .Name = "CustomerProduct"
    .Columns.Append "Order ID", adInteger
    .Columns.Append "Folder", adWChar
    .Columns.Append "EAN 8/13", adWChar
    .Columns.Append "Quantity", adWChar
    .Columns("Order ID").Attributes = adColNullable
    .Columns("Folder").Attributes = adColNullable
    .Columns("EAN 8/13").Attributes = adColNullable
    .Columns("Quantity").Attributes = adColNullable
  End With
  
  keyIndex.Name = "Order ID"
  keyIndex.Unique = True
  keyIndex.PrimaryKey = True
  keyIndex.Columns.Append "Order ID"
  newTBL.Indexes.Append keyIndex
  
  keyIndex.Name = "Folder"
  keyIndex.Columns.Append "Folder"
  
  keyIndex.Name = "EAN 8/13"
  keyIndex.Columns.Append "EAN 8/13"
  
  Set tblRelation = DbaseCat.Create("Relation 1")
  tblRelation.Table = "CustomerProduct"
  tblRelation.ForeignTable = "Customer"
  Set ForeignFld = tblRelation.CreateField("Folder")
  tblRelation.Fields.Append ForeignFld
  tblRelation.Attributes = dbRelationDeleteCascade
  DbaseCat.Relations.Append tblRelation
  
  DbaseCat.Tables.Append newTBL
  DbaseCat.ActiveConnection = Nothing
  
  Set DbaseCat = Nothing
  Set newTBL = Nothing

End Sub

And I also added this code today too:

Public Sub Add_Product()
Rem adds a product to the product table using data from the gui

Dim dbConn As ADODB.Connection 'Connection to the database
Dim rsProduct As ADODB.Recordset 'product record structure
Dim AdminRec As AdminRecord
Dim AdminFile As String
Dim index As Integer

  AdminFile = flpath & flName
  
  Open AdminFile For Random As #1
    index = 1
    Get #1, index, AdminRec
  Close #1

  Set dbConn = New ADODB.Connection
  Set rsProduct = New ADODB.Recordset
  With dbConn
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & AdminRec.flDir & AdminRec.flName
    .Open
  End With
    
  rsProduct.CursorType = adOpenDynamic
  rsProduct.LockType = adLockOptimistic
  rsProduct.Open "SELECT * FROM product", dbConn
  rsProduct.AddNew
  rsProduct.Fields("Title") = MOSgui.txtUserName
  rsProduct.Fields("EAN 8/13") = MOSgui.txtFlDir
  rsProduct.Fields("Price") = MOSgui.txtAddress2
  rsProduct.Fields("Category") = MOSgui.lstCategory
  rsProduct.Fields("Length") = MOSgui.lstLength
   
  rsProduct.Update
  dbConn.Close
  Set dbConn = Nothing
  Set rsProduct = Nothing

End Sub

This is merely a copy / paste of another add procedure, with the correct fields and recordset used. This procedure works but the Product_Table one doesnt. Stupidly its the same as a Customer_Table that I have which is the same code but the customer table is created (Order of creation: Customer, Product, CustomerProduct).

Sorry for taking up so much space but I wanted to give you as much info incase you can help while im checking books at the library. *must buy VB books*

TIA

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."

 

[gwar2k1]

never mind got some good books and ive decided to make the database with DAO instead of ADO. thanks if you intended to help though =D

~*Gwar3k1*~
"To the pressure, everything's just like: an illusion. I'll be losing you before long..."