Results 1 to 6 of 6

Thread: DefaultValue

  1. #1
    Join Date
    Feb 2004
    Location
    Ibiporã
    Posts
    2

    Unanswered: DefaultValue

    In a table [PEDIDOS] I have a field [MORA] whose default value is 10. I need change it to other new value at any time, by form, without need to open the .mdb in design mode.
    Someone did say me to use ALTER TABLE, but I don't know how
    Windows XP Office XP and Office 2003
    Can some one help ?

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    something like this

    Dim strSQL As String

    strSQL = "ALTER TABLE PEDIDOS ALTER COLUMN MORA NUMBER DEFAULT " & txtValue & " ;"
    CurrentProject.Connection.Execute strSQL

  3. #3
    Join Date
    Feb 2004
    Location
    Ibiporã
    Posts
    2
    Thanks for reply, but not worked.
    I still looking for solution.

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    did you get an error?

    how did you use it?

  5. #5
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Access 2k example
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Try this for a linked table:


    Function Alter_Table() As Boolean
    '---------------------------------------------------------------------
    ' Returns: True if successful, False otherwise
    '---------------------------------------------------------------------
    On Error GoTo Err_Alter_Table

    Dim strNewPath As String
    Dim dbs As Database
    Dim lVar As Variant

    strNewPath = GetAttachedPath("", "PEDIDOS") ' get path

    ' Return Database object variable pointing to strNewPath
    Set dbs = OpenDatabase(strNewPath)

    lVar = SysCmd(SYSCMD_SETSTATUS, "Updating the PEDIDOS table. Please wait...")
    DoCmd.Hourglass True

    'Alter PEDIDOS
    strSQL = "ALTER TABLE PEDIDOS ALTER COLUMN MORA NUMBER DEFAULT " & txtValue & " ;"
    dbs.Execute strSQL

    'Close database connection and remove from memory
    dbs.Close
    Set dbs = Nothing

    Exit_Alter_Table:
    lVar = SysCmd(SYSCMD_CLEARSTATUS)
    Exit Function

    Err_Alter_Table:
    MsgBox Err.Description
    Resume Exit_Alter_Table

    End Function


    Function GetAttachedPath(strDatabase As String, strTable As String) As String
    '---------------------------------------------------------------------
    ' Parameters: strDatabase - name and path of database to look in or
    ' "" (blank string) for the current database
    ' strTable - name of table to get the path for
    ' Returns: path
    '---------------------------------------------------------------------

    Dim dbsTmp As Database
    Dim tdfTmp As TableDef
    Dim strConnect As String
    Dim intPos As Integer

    On Error GoTo PROC_ERR

    ' Return Database object variable pointing to current database.
    If strDatabase = "" Then
    Set dbsTmp = CurrentDb()
    Else
    Set dbsTmp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    ' Return TableDef object variable pointing to strTable table.
    Set tdfTmp = dbsTmp.TableDefs(strTable)
    'Grab the path from the Connect property
    strConnect = tdfTmp.Connect
    If strConnect <> "" Then
    intPos = InStr(strConnect, "=")
    GetAttachedPath = Mid$(strConnect, intPos + 1, Len(strConnect))
    End If
    'Close the connection
    dbsTmp.Close
    Set dbsTmp = Nothing

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    GetAttachedPath = ""
    Resume PROC_EXIT

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •