[AccessD] Renaming a field through code

Bill:

That worked! Thank you.

Best,
 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Patten
Sent: Friday, July 18, 2008 10:36 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Renaming a field through code

Here is the ChangeFieldName function from that module: I think it would
work.

Bill



Function ChangeFieldName(TblName As String, OldFldName As String, NewFldName
As String) Dim Td As TableDef Dim db As Database Dim DbPath As Variant Dim
FldPos As Integer Dim rs As Recordset Dim IdxName As String

'get back end path of linked table
    DbPath = DLookup("Database", "MSysObjects", "Name='" & TblName & "' And
Type=6")
    If IsNull(DbPath) Then
        Set db = CurrentDb 'if local table
    Else
        Set db = OpenDatabase(DbPath) 'if linked table
        If Err <> 0 Then
            'failed to open back end database
            Exit Function
        End If
        'in case back end has different table name than front end
        TblName = DLookup("ForeignName", "MSysObjects", "Name='" & TblName &
"' And Type=6")
    End If

'get table
    Set Td = db.TableDefs(TblName)
    If Err <> 0 Then
        'failed to get table
        GoTo Done
    End If

'change field name
    Td.Fields(OldFldName).Name = NewFldName

ChangeFieldName = True  'defaults to false if it fails to get here

Done:
If Not db Is Nothing Then db.Close
End Function

----- Original Message -----
From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Friday, July 18, 2008 10:22 AM
Subject: Re: [AccessD] Renaming a field through code


Bill:

That will change the length of the field.  But I need to change the name.

I consulted SQL: Access to SQL Server (by a couple of notorious Apress
authors) - under the ALTER info but there doesn't appear to be a SQL command
for this.

IIRC, there was a thread a long time ago to the effect that you had to add a
new field with the new name, copy all the data from the old field to the new
field, and then delete the old field.  But I'm hoping I'm disremembering
that and that there's a method now to rename a field.


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Patten
Sent: Friday, July 18, 2008 10:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Renaming a field through code

Rocky,

 I have a routine that I got from you back in 2005, "mod_ModifyTablesFEorBE"

that I believe will do that. I can send it to you if you want.

Copy of your notes:
**********************
Never mind.  Figured it out.  I have the name and path of the linked
database in a global variable already. So:

Set wrk = DBEngine.Workspaces(0)
Set Db = wrk.OpenDatabase(gstrDatabaseName)

Dim strSql As String
strSql = "ALTER TABLE tblPODetail ALTER COLUMN fldPODPartDescription
TEXT(255);"
Db.Execute strSql, dbFailOnError


works.

Rocky

----- Original Message -----
From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>

*****************************

Bill








----- Original Message -----
From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Friday, July 18, 2008 9:56 AM
Subject: [AccessD] Renaming a field through code


Dear List:

Is is possible to rename a field in a linked table through code?

MTIA





Rocky Smolin

Beach Access Software

858-259-4334

www.e-z-mrp.com <http://www.e-z-mrp.com/>

www.bchacc.com <http://www.bchacc.com/>






-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com