Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30

    Unanswered: Saving a Table with VBA code

    Can anyone help me with the correct command to save a foreign Table while in a VBA Sub.

    I have form open bound to a table (Note: this is not the table I want to save) however, a foreign table in which a serial number field has been updated.

    Thank you in advance.

    Dale

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the foreign table can be updated
    you could use one of several techniques
    if you want to update the serial number
    then you could run an SQL command using docmd****nsql

    another approach is to do the similar thing with a an update capable recordset. for knwo I'd suggest you consider using the macro docmd
    and probably in the relevant controls after update event

    make certain you understands the implications of what you are doing before doing it.
    when you use the update make certain you have the primary key of the row in the foreign
    key BEFORE attempting an update

    the UPDATE syntax is

    update mytable set anumericcolumn = 666 where theprimarykey = 'something'
    update mytable set astringcolumn = "blah" where theprimarykey = 666
    Make certain there is a where clause otherwise the update will process EVERY row. make certain you use an appropriate where clause to uniquely identify the row/rows you want to update AND only that/those row/rows

    ferinstance
    if you had a persons table and you wanted to update someones surname from Jones to Smith
    UPDATE mytable set surname = "SMITH" would set everyones surname to "Smith" in that table
    UPDATE mytable set surname = 'SMITH' where surname = "Jones" would set everyones surname to Smith in that table whose current surname was "Jones"
    update mytable set Surname = "Smith" where PersonID = 123456 would change all rows whose PersonID was 123456. assuming PersonID was the primary key then it woudl only change the surname off a single row
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here are some examples that use 2 different techniques: one is based on SQL, the other is a "pure" VBA solution. In both case an example shows to insert a row in a table (= add a line), and another example shows how to update an existing line (= change the values of a line).
    Code:
    Option Compare Database
    Option Explicit
    
    Sub NewRowSQL()
    '
    ' Insert a new row into a table, using a dynamically built SQL expression.
    '
        Const c_SQL As String = "INSERT INTO Table1 ( Column1, Column2 ) VALUES ( @1, '@2' );"
        
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@1", Me.Text1)     ' Column1 is defined as Numeric and Text1 contains a numeric value.
        strSQL = Replace(strSQL, "@2", Me.Text2)    ' Column2 is defined as Text and Text2 contains a String value.
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    
    Sub NewRowDAO()
    '
    ' Insert a new row into a table, using VBA and the DAO library.
    '
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
        With rst
            .AddNew
            !Column1 = Me.Text1
            !Column2 = Me.Text2
            .Update
            .Close
        End With
        Set rst = Nothing
            
    End Sub
    
    Sub UpdateRowSQL()
    '
    ' Update a row in a table, using a dynamically built SQL expression.
    '
        Const c_SQL As String = "UPDATE Table1 SET Column1 = @1, Column2 = '@2' WHERE RowId = @I;"
        
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@1", Me.Text1)      ' Column1 is defined as Numeric and Text1 contains a numeric value.
        strSQL = Replace(strSQL, "@2", Me.Text2)     ' Column2 is defined as Text and Text2 contains a String value.
        strSQL = Replace(strSQL, "@I", Me.TextRowId) ' RowId is defined as Numeric (Identity) and TextRowId identifies the row.
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Sub
    
    Sub UpdateRowDAO1()
    '
    ' Update a row in a table, using VBA and the DAO library.
    ' The RecordSet only contains the row to be updated.
    '
        Const c_SQL As String = "SELECT * FROM Table1 WHERE RowId = @I;"
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@I", Me.TextRowId)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        With rst
            If .BOF = False Then
                .Edit
                !Column1 = Me.Text1
                !Column2 = Me.Text2
                .Update
            Else
                ' not found: handle error.
            End If
            .Close
        End With
        Set rst = Nothing
            
    End Sub
    
    Sub UpdateRowDAO2()
    '
    ' Update a row in a table, using VBA and the DAO library.
    ' The RecordSet opens the whole table (all rows), 
    ' then search for the row to be updated.
    '
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        strCriteria = RowId = " & Me.TextRowId
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
        With rst
            .FindFirst strCriteria
            If .NoMatch = False Then
                .Edit
                !Column1 = Me.Text1
                !Column2 = Me.Text2
                .Update
            Else
                ' not found: handle error.
            End If
            .Close
        End With
        Set rst = Nothing
            
    End Sub
    Have a nice day!

  4. #4
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Quote Originally Posted by healdem View Post
    if the foreign table can be updated
    you could use one of several techniques
    if you want to update the serial number
    then you could run an SQL command using docmd****nsql

    another approach is to do the similar thing with a an update capable recordset. for knwo I'd suggest you consider using the macro docmd
    and probably in the relevant controls after update event

    make certain you understands the implications of what you are doing before doing it.
    when you use the update make certain you have the primary key of the row in the foreign
    key BEFORE attempting an update

    the UPDATE syntax is

    update mytable set anumericcolumn = 666 where theprimarykey = 'something'
    update mytable set astringcolumn = "blah" where theprimarykey = 666
    Make certain there is a where clause otherwise the update will process EVERY row. make certain you use an appropriate where clause to uniquely identify the row/rows you want to update AND only that/those row/rows

    ferinstance
    if you had a persons table and you wanted to update someones surname from Jones to Smith
    UPDATE mytable set surname = "SMITH" would set everyones surname to "Smith" in that table
    UPDATE mytable set surname = 'SMITH' where surname = "Jones" would set everyones surname to Smith in that table whose current surname was "Jones"
    update mytable set Surname = "Smith" where PersonID = 123456 would change all rows whose PersonID was 123456. assuming PersonID was the primary key then it woudl only change the surname off a single row
    WOW!, got a little HW and trial and error. Thank you this helps a lot.

  5. #5
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Quote Originally Posted by Sinndho View Post
    Here are some examples that use 2 different techniques: one is based on SQL, the other is a "pure" VBA solution. In both case an example shows to insert a row in a table (= add a line), and another example shows how to update an existing line (= change the values of a line).
    Code:
    Option Compare Database
    Option Explicit
    
    Sub NewRowSQL()
    '
    ' Insert a new row into a table, using a dynamically built SQL expression.
    '
        Const c_SQL As String = "INSERT INTO Table1 ( Column1, Column2 ) VALUES ( @1, '@2' );"
        
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@1", Me.Text1)     ' Column1 is defined as Numeric and Text1 contains a numeric value.
        strSQL = Replace(strSQL, "@2", Me.Text2)    ' Column2 is defined as Text and Text2 contains a String value.
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    
    Sub NewRowDAO()
    '
    ' Insert a new row into a table, using VBA and the DAO library.
    '
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
        With rst
            .AddNew
            !Column1 = Me.Text1
            !Column2 = Me.Text2
            .Update
            .Close
        End With
        Set rst = Nothing
            
    End Sub
    
    Sub UpdateRowSQL()
    '
    ' Update a row in a table, using a dynamically built SQL expression.
    '
        Const c_SQL As String = "UPDATE Table1 SET Column1 = @1, Column2 = '@2' WHERE RowId = @I;"
        
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@1", Me.Text1)      ' Column1 is defined as Numeric and Text1 contains a numeric value.
        strSQL = Replace(strSQL, "@2", Me.Text2)     ' Column2 is defined as Text and Text2 contains a String value.
        strSQL = Replace(strSQL, "@I", Me.TextRowId) ' RowId is defined as Numeric (Identity) and TextRowId identifies the row.
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Sub
    
    Sub UpdateRowDAO1()
    '
    ' Update a row in a table, using VBA and the DAO library.
    ' The RecordSet only contains the row to be updated.
    '
        Const c_SQL As String = "SELECT * FROM Table1 WHERE RowId = @I;"
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@I", Me.TextRowId)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        With rst
            If .BOF = False Then
                .Edit
                !Column1 = Me.Text1
                !Column2 = Me.Text2
                .Update
            Else
                ' not found: handle error.
            End If
            .Close
        End With
        Set rst = Nothing
            
    End Sub
    
    Sub UpdateRowDAO2()
    '
    ' Update a row in a table, using VBA and the DAO library.
    ' The RecordSet opens the whole table (all rows), 
    ' then search for the row to be updated.
    '
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        strCriteria = RowId = " & Me.TextRowId
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
        With rst
            .FindFirst strCriteria
            If .NoMatch = False Then
                .Edit
                !Column1 = Me.Text1
                !Column2 = Me.Text2
                .Update
            Else
                ' not found: handle error.
            End If
            .Close
        End With
        Set rst = Nothing
            
    End Sub
    Thank you very much Sinndho! I have two very good solutions to work with. Got a little H.W. to do but will get it right.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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