[RESOLVED]Add Field to access Database with code

[seanwpb]

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.

[si_the_geek]

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.

[seanwpb]

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.

[si_the_geek]

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"

[seanwpb]

Si,

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

[si_the_geek]

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.

[seanwpb]

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.

'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