Beacon an ADO Question for you

[rudvs2]

Hey Beacon as you seem to have your head around ADO I will direct this Q to you

I am just transitioning to ADO from DOA

Now I can create a DB from scratch no worries with ADOX

I have no trouble populating recordsets and navigating them with ADODB.

What I cant seem to get happening or find much info on Is to Modify eg Add additional fields to an existing Table. In DAO you could specify a table with the Tabledef object. However with ADOX the Table object doesnt have a propertie for this other than name. If i specify my existing tables name in this object my understanding is that it will try to create a new table with that name.

So how do I go about adding a field to an existing table???

[SoftwareMaker]

Do it in the SQL statements with ADO.CommandText Property and then execute the ADO.Command

[rudvs2]

Okay softwaremaker just to make sure I understand

You are recommending taht I create an ADO.Command object hat contains an SQL statement to create a New Field on the specified Table. Then just have my code execute the ADO.Command Object evertime it needs this new field is that correct??

Well assuming it is I am using an access Database what is the SQL sytax for creating and appending a new Field?

Sorry to be so thick but I really havnt had to deal with SQL statements previously I always managed to acheive what I wanted with out them.

[Beacon]

rudvs2 are you using Jet for your connection or odbc?

[rudvs2]

Im using Jet beacon

I can post a sample of my connection code if you like?

[SoftwareMaker]

I am not at a machine with SQL now...BUT if I am not wrong, you have to use the Alter Table Keyword in SQL

[Beacon]

ok i gots to try a few ideas bear with me:

Can you open a new recordset but make it disconnected and then
Use this:

.Fields.Append "NewField", adDouble, 8

[mdake]

The way to set an ADOX catalog to an existing table is to set its ActiveConnection property to a an ADO Connection Object...

Set cn = New ADODB.Connection
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Biblio.mdb;Persist Security Info=False"
    
    cat.ActiveConnection = cn
    
    With tbl
      .name = "TestTable"

I am not sure how to add a new Field though, I have only created new DB and fields at the same time. Obviously you use Append but not sure how to update the Cataog.

[SoftwareMaker]

Programmatically modifies the structure of a table.

Syntax

ALTER TABLE TableName1
ADD | ALTER [COLUMN] FieldName1
FieldType [(nFieldWidth [, nPrecision])]
[NULL | NOT NULL]
[CHECK lExpression1 [ERROR cMessageText1]]
[DEFAULT eExpression1]
[PRIMARY KEY | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]]
[NOCPTRANS]
[NOVALIDATE]
-or-
ALTER TABLE TableName1
ALTER [COLUMN] FieldName2
[NULL | NOT NULL]
[SET DEFAULT eExpression2]
[SET CHECK lExpression2 [ERROR cMessageText2]]
[DROP DEFAULT]
[DROP CHECK]
[NOVALIDATE]
-or-
ALTER TABLE TableName1
[DROP [COLUMN] FieldName3]
[SET CHECK lExpression3 [ERROR cMessageText3]]
[DROP CHECK]
[ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]]
[DROP PRIMARY KEY]
[ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]]
[DROP UNIQUE TAG TagName4]
[ADD FOREIGN KEY [eExpression5] TAG TagName4 [FOR lExpression6]
REFERENCES TableName2 [TAG TagName5]]
[DROP FOREIGN KEY TAG TagName6 [SAVE]]
[RENAME COLUMN FieldName4 TO FieldName5]
[NOVALIDATE]

Arguments

TableName1

Specifies the name of the table whose structure is modified.

ADD [COLUMN] FieldName1

Specifies the name of the field to add. A single table can contain up to 255 fields. If one or more fields allow null values, the limit is reduced by one to 254 fields.

ALTER [COLUMN] FieldName1

Specifies the name of an existing field to modify.

FieldType [(nFieldWidth [, nPrecision])]

Specifies the field type, field width, and field precision (number of decimal places) for a new or modified field.

FieldType is a single letter indicating the field's data type. Some field data types require that you specify nFieldWidth or nPrecision or both.

[rudvs2]

Beacon Im not sure what you mean about make it disconnected

Im going to have a bit of a play with setting the activeconnection property.

[SoftwareMaker]

ok i gots to try a few ideas bear with me:

Yes Beacon, the ado.fields.append way will work too

Although, you would want to use the native SQL statements for faster execution and for more control, for example, you might want to add constraints to the field via SQL

[SoftwareMaker]

What beacon meant about Disconnected Recordsets is that once you retrieve what you want from the database, disconnect from it.

That means your resulting recordset has got no connection and nothing to do with the Access database

This will free up a lot of resources as you set the activeconnection to nothing

Once you are finished with editing or whatever, send the final recordset back to the database.

A disconnected recordset is often underutilised BUT very useful esp for small databases like Access as it only supports up to 5 concurrent connections.

[Beacon]

software maker:
Hey i'm not disagreeing!
Just if kept going on the non SQL line just incase he didnt want to do it that way for some reason!
No offense!

You can always set the ado to acmdText instead of acmdTable to use SQL after!

[SoftwareMaker]

Cool Beacon,

you are definitly right in the fact that ADO Fields Collection is much easier to use and maintain.

[rudvs2]

Okay guys I have just seen your new posts

First of all thanks for being patient with me

Second of all Becasue I have just seen your new posts I havnt tried them

So you may be able to help me a bit more

here is the code I have got so far

Public Function addButtonField(intNum As Integer, strGroup As String) As Boolean
Dim tbldef As New ADOX.Table
Dim Mdb As New ADOX.Catalog
Dim strField As String
 
On Error GoTo errorHandler
Mdb.ActiveConnection = Rs
tbldef.Name = strGroup 
strField = "B" & intNum
 
With tbldef.Columns
.Append strField, adChar, 100
.Refresh
End With
 
Set tbldef = Nothing
Set Mdb = Nothing
Rs.Requery
 
addButtonField = True
Exit Function
 
errorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
addButtonField = False
End Function

So what you are saying is that Once I set the tbldef Name property I need to set its active connection to nothing

Do the appends and then reconnect it and update it?

Im with you all on setting it to nothing and carry on blah blah blah
But how would I send the updated tabledef back to the db once it has been changed??

[SoftwareMaker]

Once you retrieve the recordset, set the activeConnection to nothing

Work with the Disconnected Recordset buffer...Append a new field)

Then send the buffer back to the database. If what you are only doing is append a field, maybe you should just do what you have to do (short time) while the connection is still open.

Disconnecting is good if you want to spend time playing with the recordset retrieved...ie Long time keeping the connection open

[Beacon]

1) You have to do the append whilst the connection is closed otherwise you'll get a run-time error.
2)Get rid of that tabledefs stuff it's not needed!!

Give me a few minutes!!

[rudvs2]

Sorry guys Im just really struggling to get my head around this

Here is the code that Im using now

Execpt that when It executes the Append line it gets Error Code 3001 arguments are of the wrong type or are conflicting

here is my code

Public Function addButtonField(intNum As Integer, strGroup As String) As Boolean
Dim strField As String
 
On Error GoTo errorHandler
strField = "B" & intNum
 
 
With Rs
.Close
.Fields.Append strField, adChar, 100, adFldUpdatable, "" This is the error here
.Open
.Update
End With
 
addButtonField = True
Exit Function
 
errorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
addButtonField = False
End Function

Tanks for being patient with me

[Beacon]

Ok i'm just reading something that said i shortcut to this is too just assign a value to a non-existent field.

So try assigning a value to your new field?
Howd it go?

[Beacon]

sorry didnt see that reply!!

Ok make sure you have no activeconnections open and the rs hasnt been opened. You cant have any connections with the datasource!

[rudvs2]

No assigning a value to a non existant Field just gives me an error

I am still getting Error 3001 in the add field method as well

This is getting silly

Surely there must be a straight forward way of doing this simple task with ADO it does damn near everything else you could possible think of

[Beacon]

ok it is simple - ish!!

Just a matter of getting your head around it.

Ok i see 1 flaw

adFldUpdatable, make this :adFldIsNullable
try that!

Now i wont be posting agin till i have the answer for you. Hows that!

Also i know someone on here who had the same problem but i forget there name so i'll have a search.
I think i've almost got it.brb

[Skitchen8]

Beacon aren't you proud... you got your thread in the title of a thread ... i wish someone would do that for me :'(

[rudvs2]

Okay just so you know changing the code to this (as per your suggestion gave me no joy

Public Function addButtonField(intNum As Integer, strGroup As String) As Boolean
Dim strField As String
 
On Error GoTo errorHandler
strField = "B" & intNum
 
 
With Rs
.Close
.Fields.Append strField, adChar, 50, adFldIsNullable, "Temp"
.Open
.Update
End With
 
addButtonField = True
Exit Function
 
errorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
addButtonField = False
End Function

So I will wait with baited breath (or at least keep hunting through msdn) for your next idea

[Beacon]

ok i tink tis works makes an autonumber column though:

Sub create()

Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\nwind.mdb;"

' Create the new auto increment column
With col
.Name = "yomama"
.Type = adInteger
Set .ParentCatalog = cat
.Properties("AutoIncrement") = True
End With

' Append the column to the table
cat.Tables("table1").Columns.Append col

Set cat = Nothing

End Sub

I'm sure you can alter it. I.e get rid of the .properties bit to make the new field.

How'd that go?

[rudvs2]

Okay That code looks good I havnt tried It yet because I have made some Progress of my own

I can now close the recordset and append the field to it without the connection

Now all I cant make it do Is reconnect and add that Recordset data back into the database

Here is the working (sorta) code

Public Function addButtonField(intNum As Integer, strGroup As String) As Boolean
Dim strField As String
 
On Error GoTo errorHandler
strField = "B" & intNum
 
 
With Rs
.Close
.CursorLocation = adUseClient
.Fields.Append strField, adVarWChar, 50
.Open
.Update
End With
 
addButtonField = True
Exit Function
 
errorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
addButtonField = False
End Function

Im interested in seeing wether youknow how to make that update or not

Im off to try your suggested piece of code now

[rudvs2]

Beacon you are Fantastic Mate. I owe you a couple of cold fosters.

This is the code that works

Public Function addButtonField(intNum As Integer, strGroup As String) As Boolean
Dim strField As String
Dim Cat As New ADOX.Catalog
Dim Col As New ADOX.Column
 
On Error GoTo errorHandler
strField = "B" & intNum
Cat.ActiveConnection = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & db
Rs.Close
 
With Col
    .Name = strField
    .Type = adVarWChar
    Set .ParentCatalog = Cat
End With
 
Cat.Tables(strGroup).Columns.Append Col
Set Cat = Nothing
Rs.Open
Rs.Requery
 
addButtonField = True
Exit Function
 
errorHandler:
addButtonField = False
End Function

That is excellent

I cant thank you enough mate for your help

Cheers

[Beacon]

hmm ok what you'll have to do is the following.

Sorry not on vb computer at the moment.

Use more than 1 recordset. So 1's for opening the db and the other is created programmitcally and holds the extra field.
Then perhaps loop it to fill in data then update.

If that makes sense!!!???

Your old code:
You added the cursor location. But you really dont need to set that because your using the defualt cursor anyways!!

[Beacon]

No worries!! You helped me with my text parse problem last week!
Glad it works.
Damn forgot the rs bits. meh oh well!

Fosters: something i drunk way too much of on saturday night!

[SoftwareMaker]

Hi Guys,

Sorry for not getting back to you. I have been stumped by how to append a new field to a database using ADODB instead of ADOX

There is a Append method of the fields collection of the recordset object of ADODB that is supposed to add a new field to a recordset. BUT I have no idea on how to send the new recordset back to the database. The Update method doesnt seem to work.
I am kinda confused on when to reopen the recordset and set the activeconnection again. I am still working on it....

Meanwhile, I have always appended a new field to the database by using native SQL statements in the commandtext property of the ADO Command object. Its faster, cleaner and you have more control and its takes lesser code and resources.

Code as follows :

'OpenConnection - set connnection Properties and open Connection - named it adocn

Set adocm = New ADODB.Command
adocm.CommandType = adCmdText
adocm.CommandText = "ALTER TABLE [yourTableName] " & _
"ADD COLUMN [NewField1] " & _
"TEXT NULL"
Set adocm.ActiveConnection = adocn
Call adocm.Execute
Set adocm = Nothing

End of Code

By this time, the new field would have been appended to the Access database...Let me know if it works

Try not to use Recordsets so much. They are very memory-intensive. If you have to use one, use a disconnected one that uses a firehose cursor (Forward, Read-Only Type). In this way, they are faster and consume less resources.

[mdake]

With regards to using the Append method of the ADO Fileds collection, the way to do that is to create a ustom recordset with the fields are going to append to. Then you can use the SHAPE Provider to Open the recordset.

This will append the new fields on open.
MSKB has a couple of articlces on this.

All this seems to be alot of messing around though.
I agree, using the Command.Execute method and SQL to add fields is a lot easier and probably cleaner.

[Beacon]

Sort of there but what you have to do is:

create 2 recordsets 1 for the db and one for creating stuff out of thin air!
Then use append method to fields collextion of the 2nd recordset and use a loop to put the new field in on open.
Thats exaplaining it without complicated!
In which i dont thinks needed coz i think he's on his way!