How can I change the name of a field of a table in an mdb (using ADO preferably) ?
Thanks for any help!
Printable View
How can I change the name of a field of a table in an mdb (using ADO preferably) ?
Thanks for any help!
There is probably an SQL statement that would do it. You could create a new table with the new name, copy the data from the old table to the new table then delete the old table.
Have a look at ADOX and search the forum (top right) for this...
Vince
Hi there Muddy, I have not found any ways to just rename a field in a db. What I'v had to do is making new field, then copy all the data from the old field into the new one. finally I deleted the old field.
sample
VB Code:
if you find a better way, please let me know :)
Thanks Peet,
That works. There is also a RENAME option for the ALTER TABLE SQL statement. I cant get it to work though. Gives "Syntax Error in ALTER statement"
In the "better late than never" category, using DAO:
VB Code:
Reply to Muddy,
Muddy Hello,
I think the RENAME is good only for renaming a TABLE!
peet has it!!!
From The MSDN:
Best RegardsQuote:
ALTER TABLE – SQL Command Examples
Example 1 adds a field called fax to the customer table and allows the field to have null values.
Example 2 makes the cust_id field the primary key of the customer table.
Example 3 adds a field validation rule to the quantity field of the orders table so that values in the quantity field must be non-negative.
Example 4 adds a one-to-many persistent relation between the customer and orders tables based on the primary key cust_id in the customer table and a new foreign key index cust_id in the orders table.
Example 5 removes the field validation rule from the quantity field in the orders table.
Example 6 removes the persistent relation between the customer and orders tables, but keeps the cust_id index tag in the orders table.
Example 7 adds a field called fax2 to the customer table and prevents the field from containing null values. The new structure of the table is displayed. Two ALTER COLUMN clauses are used to allow the field to have null values and set the default value for the field to the null value. Note that multiple ALTER COLUMN clauses are required to change more than one property of a field in a single ALTER TABLE command. The new field is then removed from the table to restore the table to its original state.
* Example 1
SET PATH TO (HOME(2) + 'Data\') && Sets path to table
ALTER TABLE customer ADD COLUMN fax c(20) NULL
* Example 2
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id
ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY
* Example 3
ALTER TABLE orders;
ALTER COLUMN quantity SET CHECK quantity >= 0;
ERROR "Quantities must be non-negative"
* Example 4
ALTER TABLE orders;
ADD FOREIGN KEY cust_id TAG cust_id REFERENCES customer
* Example 5
ALTER TABLE orders ALTER COLUMN quantity DROP CHECK
* Example 6
ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE
* Example 7
CLEAR
ALTER TABLE customer ADD COLUMN fax2 c(20) NOT NULL
DISPLAY STRUCTURE
ALTER TABLE customer;
ALTER COLUMN fax2 NULL;
ALTER COLUMN fax2 SET DEFAULT .NULL.
ALTER TABLE customer DROP COLUMN fax2
ERAN
Again in the "better late than never" category, using ADOX this time:
See what happens when you don't set your thread to "Resolved" !!VB Code:
Quote:
Originally Posted by DaveBo
Hummm .. this looks promising and while I can do this "Renaming of a Field" with the ALTER TABLE and then copy data from old field to new field and then DROP COLUMN solution as outlined at the beginning of this thread , I would like to see about doing it the way proposed above as the Public Function Rename_Field_In_Table_ADOX way .. Again, however, I need to do it with my protected database.
From the previous solutions in this forum dealing with the correct strings for the ALTER TABLE, on my protected database I used the following and it worked fine to establish the connection ... and then make the insertion to my mdw protected database
Code:Public Sub ConnectToDatabase()
Dim g_str_DatabasePath As String
Dim g_str_DataBase_MDW_SecurityFilePath As String
'
' 'Secured Database and Security File Paths
g_str_DatabasePath = App.Path & "\Data Files\Databases\exampleprotected.mdb"
g_str_DataBase_MDW_SecurityFilePath = App.Path & "\Data Files\Databases\exampleprotected.mdw"
Debug.Print g_str_DatabasePath
Debug.Print g_str_DataBase_MDW_SecurityFilePath
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Properties("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
Debug.Print g_str_DatabasePath
Debug.Print g_str_DataBase_MDW_SecurityFilePath
cnn.Open "Data Source= " & g_str_DatabasePath & ";User Id= johndoe;Password= 1234;"
'From vbForums Example Code that I then modified for my mdw protected database
' cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
' cnn.Properties("Jet OLEDB:System database") = _
' "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
'
' cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"
' cnn.Close
'
' strSqlA = "ALTER Table Adressen ADD Column myNewField varChar(35) Null"
'cnn.Execute strSqlA
End Sub
Private Sub cmd_Execute_Click()
strSqlA = "ALTER Table TABLE1 ADD Column myNewField varChar(35) Null"
cnn.Execute strSqlA
cnn.Close
End Sub
I am trying now to create the correct connection within the suggested Public Function Rename_Field_In_Table_ but am confused as to the syntax to connect tot he protected database
Here is what I am trying, and having blocks on:
Code:
'FROM THE SUGGESTED FUNCTION
'*********
'*****objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection ' Local DB
' For an external DB have to setup proper connection, e.g.
' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=" & App.Path & "\MyDB.mdb"
'*************
'THIS IS WHAT I AM TRYING AND NOT HAVING SUCCESS WITH
objADOXDatabase.ActiveConnection.Provider = "Microsoft.Jet.OLEDB.4.0;"
objADOXDatabase.ActiveConnection.Properties = ("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
objADOXDatabase.ActiveConnection.Open "DataSource = " & g_str_DatabasePath & " ;User Id= johndoe;Password= 1234;"
I am trying to model the previous way of making a connection into this new function but I am off base I think trying to use that objADOXDatabase.ActiveConnection.Provider = line ...
Advice????
Better start a new thread and copy or point to the relevant information in this very old thread.
try...Quote:
Originally Posted by clickman
Code:Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.Mode = adModeShareDenyNone
' Jet-Provider
.Provider = "Microsoft.Jet.OLEDB.4.0"
'path to DB
.Properties("Data Source") = App.Path & "\MyDB.mdb"
'password for Database ?
' .Properties("Jet OLEDB:Database Password") = "test"
'for .mdw
'path System- und Securety-MDW
.Properties("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
' UserId
.Properties("User ID") = "johndoe"
' Password
.Properties("Password") = "testpwd"
.Open
End With
Chris E
What you posted does work for me to do an Alter Table ... Add Columns action- I got that covered based on the forum help a couple weeks ago.
What I am trying to do, however, is to change the name of an already existing column. That Function posted by DaveBo to do that was what I was trying to do. Again, however, i have a mdw protected database.
I'm not sure what the problem is ?
I showed you how to access the .mdw
does the Functionwork from 'DaveBo' ?
I'm also not sure why you work with a .mdw, I think as of Access 2007 there is no more .mdw
do you have to use a .mdw?
Quote:
Originally Posted by ChrisE
The reason for needing solutions to work with a database having user level security, is that I have to do maintenance on client installs that have such a databases already on their systems.
As for showing how to access the MDW and establish the connection and then to add a column, yes your solution for establishing a connection and for adding columns is super, and I have used it to do just that.. The situation I am looking at now is trying to use the DanBo code for his Function that he calls "Rename_Field_In_Table_ADOX" against an MDW protected DB, to change the name of the field directly - which will do the action directly without doing an add column and then copy and then doing a drop column
His function uses what appears to me is a connection object objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection, and I am confused about how to implement his code against a User Level Security [MDW] protected database.
Where then do I insert your solution
into Danbo's direct solution [ In the DanBo code, have highlighted in blue the area in question where I have been trying to re-structure his code by using code of the same style as in your earlier Alter Table .. Add Column solution but No Joy.]Code:Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.Mode = adModeShareDenyNone
' Jet-Provider
.Provider = "Microsoft.Jet.OLEDB.4.0"
'path to DB
.Properties("Data Source") = App.Path & "\MyDB.mdb"
'password for Database ?
' .Properties("Jet OLEDB:Database Password") = "test"
'for .mdw
'path System- und Securety-MDW
.Properties("Jet OLEDB:System database") = App.Path & "\Data Files\Databases\exampleprotected.mdw"
' UserId
.Properties("User ID") = "johndoe"
' Password
.Properties("Password") = "testpwd"
.Open
End With
It is thatCode:Public Function Rename_Field_In_Table_ADOX(ByRef Tablename As String, _
ByVal OldFldName As String, ByRef NewFldName As String) As Boolean
' This will rename a field/column in an existing local table.
' call as: Rename_Field_In_Table_ADOX "MyTable","OldField","NewField"
' This returns True if successful
' Requires Reference to "Microsoft ADO Ext. 2.7 for DDL ..."
Dim objADOXDatabase
Dim objTable, objColumn
On Error GoTo ErrBranch
Set objADOXDatabase = CreateObject("ADOX.Catalog")
objADOXDatabase.ActiveConnection = Application.CurrentProject.Connection ' Local DB
' For an external DB have to setup proper connection, e.g.
' objADOXDatabase.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=" & App.Path & "\MyDB.mdb"
Set objTable = objADOXDatabase.Tables(Tablename)
'Debug.Print "Table: " & objTable.Name
'Debug.Print " OldFldName=" & objTable.Columns(OldFldName).Name
objTable.Columns(OldFldName).Name = NewFldName
'Debug.Print " NewFldName=" & objTable.Columns(NewFldName).Name
Rename_Field_In_Table_ADOX = True
ErrBranch:
Set objADOXDatabase = Nothing
Set objTable = Nothing
End Function ' Rename_Field_In_Table_ADOX
'#######################################################################
that I am trying to figure out .. if indeed it IS figureoutable:DCode:objADOXDatabase.ActiveConnection = ???????
What is that ???? supposed to be?
Hi,
see if this helps, you need a Ref. to Adox
Code:Private Sub Command5_Click()
'From vbForums Example Code that I then modified for my mdw protected database
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Properties("Jet OLEDB:System database") = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
cnn.Open "Data Source=.\NorthWind.mdb;User Id=Admin;Password=;"
Call renameColumn(cnn, "Customers", "CompanyName", "NewCompanyName", , , True)
cnn.Close
'
End Sub
Public Function renameColumn(ActConn As ADODB.Connection, _
TableName As String, _
ColumnName As String, _
NewName As String, _
Optional ErrNumber As Long = 0, _
Optional ErrDescription As String = "", _
Optional ShowErrorMsg As Boolean = True) _
As Boolean
Dim Cat As ADOX.Catalog
Set Cat = New ADOX.Catalog
Set Cat.ActiveConnection = ActConn
On Error GoTo Fehler
If (ColumnName <> NewName) And (Len(NewName) > 0) Then
Cat.Tables(TableName).Columns(ColumnName).Name = NewName
End If
renameColumn = True
Fehler:
If Err.Number <> 0 Then
ErrNumber = Err.Number
ErrDescription = Err.Description
If ShowErrorMsg Then
FehlerAnzeige Err.Number, Err.Description, _
"RenameColumn: " & ColumnName & _
" (" & TableName & ")"
End If
End If
On Error GoTo 0
Set Cat = Nothing
End Function
Private Sub FehlerAnzeige(ErrNumber As Long, _
ErrDescription As String, _
Optional Titel As String = "")
Dim Msg As String
Msg = "Fehler " & ErrNumber & vbCrLf & vbCrLf & _
ErrDescription
If Len(Titel) > 0 Then
MsgBox Msg, vbCritical, Titel
Else
MsgBox Msg, vbCritical
End If
'hier ggf Eintrag in FehlerProtokoll
End Sub
Let me take a look ...!!!!:)
YES! It works great .... This will really help with my work on the existing client databases. We had to change our underlying VB code that now interacts with these databases, and this required a change in the name of one of the columns in a particular table, AND the addition of several new columns to that table. I was faced with doing each database manually and this streamlines things.
THANK YOU!
Is this the way to mark as resolved
glad you got it workingQuote:
Originally Posted by clickman
you do know that renaming a Column can cause problems
if the Column is a FOREIGN KEY to another Table, or was that Column INDEXED
just making sure you know that
You making sure is most helpful .. I was in the next stage of thinking about that what you mention. Also thinking about how to set other properties of the field.. for example input mask etc if possibleQuote:
Originally Posted by ChrisE