By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,144 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

add a colmn to an existing table using ADO

P: n/a
I need to add a column to a table using code.

The following works, but only for a new table. How should I change this to
alter an existing table?
Dim strConn
Dim Catalog As New ADOX.Catalog
Dim Table As ADOX.Table

Set Catalog = New ADOX.Catalog

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mde"
Catalog.ActiveConnection = strConn

Set Table = New ADOX.Table
Table.Name = "tblExistingTable"
Table.Columns.Append "NewColumn", adBoolean
Catalog.Tables.Append Table

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

Użytkownik "da**@strategicdelivery.co.nz" <st***************@ihug.co.nz>
napisał w wiadomości
I need to add a column to a table using code.


Hi!

Private Sub OnButton_Click()
Dim sql As String
sql = "ALTER TABLE MyTable ADD MyField Text NULL"
DoCmd.RunSQL sql
End Sub

I'm sure you don't need more code ;)

Regards, Leon!
Nov 12 '05 #2

P: n/a

Użytkownik "Leon" <et**@op.pl> napisał w wiadomości
news:bu**********@news.onet.pl...

Użytkownik "da**@strategicdelivery.co.nz" <st***************@ihug.co.nz>
napisał w wiadomości
I need to add a column to a table using code.


Hi!

Private Sub OnButton_Click()
Dim sql As String
sql = "ALTER TABLE MyTable ADD MyField Text NULL"
DoCmd.RunSQL sql
End Sub


Soooorrryyyyy ;) ADO correction ;)

Private Sub OnButton_Click()
Dim sql As String
sql = "ALTER TABLE MyTable ADD MyField Text NULL"
CurrentProject.Connection.Execute sql
End Sub

My mistake ;)
Nov 12 '05 #3

P: n/a
Leon

Thanks for that - it works perfectly for a local database

How would I use it to connect to

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mde"

Dave
"Leon" <et**@op.pl> wrote in message news:bu**********@news.onet.pl...

Użytkownik "Leon" <et**@op.pl> napisał w wiadomości
news:bu**********@news.onet.pl...

Użytkownik "da**@strategicdelivery.co.nz" <st***************@ihug.co.nz>
napisał w wiadomości
I need to add a column to a table using code.


Hi!

Private Sub OnButton_Click()
Dim sql As String
sql = "ALTER TABLE MyTable ADD MyField Text NULL"
DoCmd.RunSQL sql
End Sub


Soooorrryyyyy ;) ADO correction ;)

Private Sub OnButton_Click()
Dim sql As String
sql = "ALTER TABLE MyTable ADD MyField Text NULL"
CurrentProject.Connection.Execute sql
End Sub

My mistake ;)

Nov 12 '05 #4

P: n/a
Sorry I have some problems with posting :/

Here is general case:

Private Sub OnButton_Click()
Dim cnn as ADODB.Connection
Dim sql As String
Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mde"
Set cnn = New ADODB.Connection

cnn.Open strConn, USERID, PASSWORD

sql = "ALTER TABLE MyTable ADD MyField Text NULL"
cnn.Execute sql

End Sub

Regards, Leon!
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.