[RESOLVED]Add Field to access Database with code

I need to add some fields through code to an access 2000 database mdb file. Doing it manually with access isn't an option. I looked all over and haven't found anything that can do this I hope it's possible. This will be done in VB6.

How are you currently working with the database?

If you are using ADO code (or aren't using anything), I would recommend using ADOX - there are several threads that contain examples, so a search should get good results.

Si,

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
Dim sSQL$
Dim cnn As ADODB.Connection
Dim i%

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\work.mdb;"
Set cat.ActiveConnection = cnn

cat.Tables("WorkOrders").Columns.Append "NewField"
cat.Tables("WorkOrders").Columns.Refresh

Set cat = Nothing
Set tbl = Nothing


this just gives me error "user defined type not defined" I have referenced microsoft activex already.
what am I missing.

You need another reference too - for ADOX itself. I don't use it much, but I just had a look and I think you want "Microsoft ADO Ext. 2.x for DDL and Security"

Si,

that did the trick, that reference is from the file msadox.dll, should I now deploy this file with my setup file?

In a way yes - you need to deploy all files that your program requires (unless you are certain that they will be installed on all client computers already).

In this case tho, I think that simply installing MDAC (which you should do for ADO anyway) does the job, so you don't need to specifically install this file.

Si,

Thanks for everything it works great. To anyone else who might be working on the same Here is the entire code that worked for me. I placed the code in the submain and the second time it runs it creates a trapable error. if anyone has any suggestions to make this faster or better please post, but this seems to work very well for me.

VB Code:

'create new time in & time out fields in database - second time will create error. will trap at bottom with on error
 
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
Dim sSQL$
Dim cnn As ADODB.Connection
Dim i%
 
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & App.Path & "\work.mdb;"
    Set cat.ActiveConnection = cnn
 
    cat.Tables("WorkOrders").Columns.Append "Time_In" ' create a new field
    cat.Tables("WorkOrders").Columns.Refresh
    cat.Tables("WorkOrders").Columns.Append "Time_Out"'create another new field
    cat.Tables("WorkOrders").Columns.Refresh
 
    Set cat = Nothing
    Set tbl = Nothing
'done creating fields
 
'now lets resize the new fields
'connect to database
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\work.mdb" & ";" & _
        "Persist Security Info=False"
        
        
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = strConnString
ADOCn.Open strConnString
'the default size when adding a new text field is 255 this changed to 25
strTimeIn = "ALTER TABLE WorkOrders ALTER COLUMN Time_In text(25) "
ADOCn.Execute strTimeIn
strTimeOut = "ALTER TABLE WorkOrders ALTER COLUMN Time_Out text(25) "
ADOCn.Execute strTimeOut
 
 
'now it needs to close the connection
ADOCn.Close
 
ErrorHandler:   ' Error-handling routine.
   Select Case Err.Number   ' Evaluate error number.
      
               
         Case -2147467259' from second time resizing
         Load Form4 ' my startup form
         resume next
         
         Case -2147217858 ' created from second time you try to add new fields
         Load Form4 ' my startup form
         resume next
         
   End Select