adding a field to an in use mdb. Acc2k ADO.. 1

[Junior1544]

Thank you for taking the time for little ol me...

I am tring to normalize my data a bit more... Right now my personel information table contain's people's names all in one field. Last, First MI.

I want to split this apart... So far I have gotten the sql to pull the strings out, but I don't know how to create the fields to put the strings into. I can't just go into design view because people are alwas in the database...

How can I add fields programaticly...

My front end had dao, so I was going to use some DAO code from the front end to do it, but it wouldn't let me since the table is in the back end...

I tried to go into the back end, but DAO isn't referenced there. And people are in it, so I can't reference it, it wont let me do it...

Any idea's?? Do I just have to kick out the users for 5 minutes and do it by hand??

Thanks for the time guys and gals!

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[SBendBuckeye]

Dim strSQL As String
strSQL = "Alter Table TblName Add Column FldName Type;"
Docmd.RunSQL strSQL

This will add the columns to the table for you but requires exclusive access (I think). It would probably be better just to take everyone off so you can do a backup, etc. Test everything out on a copy and then you can minimize your down time.

Good Luck!
Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[cmmrfrds]

Here is a function I have to manipulate objects in the catalog. You should be able to set a reference to the catalog, then the table and then append columns. Give it a try since it is only a few lines of code and Access will let you know if you need exclusive use.


Function catalogInfo()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As ADOX.Column
Set cg.ActiveConnection = CurrentProject.Connection

''tb.Name = "Test"
''tb.Columns.Append "col1", adInteger
''tb.Columns.Append "col2", adVarWChar, 50
''Debug.Print "table = "; cg.Tables("Test&quot.Name
''cg.Tables.Append tb
''Exit Function
'-rename a column
Set tb = cg("test&quot
Set cl = tb("col2&quot
cl.Name = "col2aa"
Exit Function

Set cg.ActiveConnection = CurrentProject.Connection
Set tb = cg.Tables("dbo_categories&quot
Debug.Print "table name = "; tb.Name
'''Set cg.Procedures("myproc&quot = "select * from customer"
''Dim pp As Property
''Debug.Print "column = "; tb.Columns("Description&quot.Properties("default&quot.Value
''Exit Function
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
Debug.Print "type = "; cl.Type
''For Each pp In cl.Properties
'' Debug.Print "property name = "; pp.Name
'' Debug.Print "property value = "; pp.Value
''
''Next
Next

End Function