Insert (not Append) Column using ADO? 3

[MBro2]

I'm trying to insert a column in a MS Access table. I can append it fine, but I don't want it as the last column. The table has data, so I don't want to delete columns. I suppose I can clone the table one field at a time, inserting the new column, then populate the new table with the data, then delete the old table and rename the new one. However, if there is some way to arrange the column orders without screwing up the data, I'd love to hear about it. It's certainly easy in MS Access. Thanx!

---Mike

 

[johnwm]

What difference does it make? You shouldn't let users see a table at any time, so just arrange your fields properly in your query.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[MBro2]

This is for "admins" who are modifying tables and queries for end users. It would make it easier for them. Currently, they use MS Access, and I want to give them the same features as MS Access without having to buy them each a copy.

I agree it's not terribly important, but I've duplicated most MS Access functionality, and this would be the last missing feature.

---Mike

 

[mmbsports]

Were you able to figure out how to do this?

I am having the same problem. I found the solution below; however, it does not work:

I have a vb app that utilizes msaccess. when the app is upgraded, the db, at times, will change to include additional fields in the table. I do not want to include a new database, but dynamically change the existing database located on the users PCs.

Here is what I found, but it does not work as it gives me an error: "arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

////////////////////////////////////////
Dim fileName
Dim File As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Set Cnxn = New ADODB.Connection
Set File = New ADODB.Recordset

fileName = "c:/folder/filename.ext"

With Cnxn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fileName & ";" & _
"Mode=ReadWrite|Share Deny None;Persist Security Info=False"
.Open
End With

MsgBox ("Add fields to a database")
File.Fields.Append "PlayNull", adBoolean, 1, adFldUpdatable, False

MsgBox ("status for the fields")
MsgBox (File.Fields("PlayNull").Status) 'adfldpendinginsert

'turn off error-handling to verify field status
On Error Resume Next

File.Fields.Update

MsgBox ("Update succeeds")
MsgBox (File.Fields("PlayNull").Status) 'adfldpendinginsert + adFieldUnavailable

' resume default error-handling
On Error GoTo 0

' clean up
Cnxn.Close
Set File = Nothing
Set Cnxn = Nothing

Your assistance would be greatly appreciated.

BTW: ADOX has a great solution, but i cannot use it.

 

[MBro2]

I ended up using ADOX. Even then I have to create new table with the columns in the right order, and then copy the data from the old table; I can't seem to simply re-arrange the column order of an existing database.

---MikeB

 

[fredericofonseca]

that is what ALL databases do. why should you be able to do something they don't?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Golom]

As it turns out, creaky, clunky old DAO is much better for this than ADO because it's much more tightly bound to JET. You need to mess with the "OrdinalPosition" property of a field as in this bit of code

    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim fd As DAO.Field
    Dim pp As DAO.Property
    Set db = DAO.DBEngine(0).OpenDatabase("myDatabase.mdb")
    Set tb = db.TableDefs("myTable")
    Set fd = tb.Fields("myField")
    Set pp = fd.Properties("OrdinalPosition")
    pp.Value = 10

or in more condensed form

db.TableDefs("myTable").Fields("myField").Properties("OrdinalPosition").Value = 10

 

[JerryKlmns]

Golom,

Although code executes without error, you should re-arrange the rest of the columns! Right??? Because there are gonna be two columns with the same OrdinalPosition. The column you change OrdinalPosition is placed before the old one with the same OrdinalPosition. What will happen the next time?



-----------------------------------------------
Once again DAO beets ADO for dirty mdb work.

 

[mmbsports]

Thank you Golum...however, I just changed from DAO to ADO because of system changes :-(

The difference from Mbro2 and I is that I dont care about the arrangement...the new column can be anywhere in my DB.

I want to add a column to an existing DB using ADO on the fly.

ORIGINAL DB STRUCTURE
Table Name: testTable
Table Column (field): fld1
Table Column (field): fld2

NEW DB STRUCTURE (simply add a column-field)
Table Name: testTable
Table Column (field): fld1
Table Column (field): fld2
Table Column (field): fld3

According to MS, I can do this with the example I provided above. However, I continue to get the error message I state.

I wonder if it is failing because of what JerryKlmns stated.

Any ideas? Is my code wrong? Am I missing something?

Thanks!

 

[Golom]

Right. JET doesn't have a problem with two fields having the same ordinal position but you might.

About the only time that column position is important (in my experience) is when using the ADO recordset GetString method. It produces a semi-colon delimited string of column values and the same column values in different positions will produce different delimited strings.

 

[JerryKlmns]

mmbsports,

I cant see where you alter the table structure!

This shall add the column Address to the table tblCustomers.

Cnxn.Execute "ALTER TABLE tblCustomers ADD COLUMN Address TEXT(30)",,129

It uses the opened connection to your database and no need for ADOX

Does it work for you?

 

[Golom]

I'm not sure why you're getting the error but why not just use SQL?

Cnxn.Execute "ALTER TABLE myTable ADD COLUMN myColumn BIT "

 

[mmbsports]

JerryKlmns and Golom,

You two have mentioned the same solution...so I hope it will work. I will try it tonight and get back with you.

I searched MS Knowledge base for weeks now, and the only solution I found was the one I mentioned (and I couldnt even get it to work). The "ALTER" sql method is straight forward and simple, and it looks like it will work. I'll keep my fingers crossed.

I'll have something posted up tonight between 5pm and 7pm.

THANKS!!

 

[Golom]

ALTER TABLE ... ADD COLUMN is standard SQL and it works well. The only problem you may have is specifying the data type of fields that you are defining. For help with that, check out this MS article and look at the Data Types topic.

 

[mmbsports]

Okay, got it to work. I dont know why, but I had to go with a different connect string. All works great. The only thing is that when I manually examine the database with MS, the new field shows up as Yes/No just like the rest, but when I look at the data the other fields have a checkbox, and the new field has a -1 or an 0; dont know how this will affect my results.

I used bit because I wanted it to be a boolean.

Here is the code that i used in VB6:
...............................................
Dim dbs As Database
Set dbs = OpenDatabase(LOCATION OF DB)

With dbs
On Error Resume Next 'used if the field already exist
.Execute "ALTER TABLE tbl_name ADD COLUMN col_name BIT;"
End With

dbs.Close
Set dbs = Nothing
................................................

Thanks to all that helped out!

 

[Golom]

Shouldn't affect the operation of the database but if you want to change it set the "DisplayControl" property of the field to 106 (DAO again). That property may not exist and you may need to add it to the field's properties collection.