none
Form VBA using SQL to update table field value with value of a string. RRS feed

  • Question

  • The code below I am trying to use to update a table field with the value of a string named " strSourceDb " using SQL.  Special attention to string "strSQL2" with the SQL code, when is use " SET SettingString = strSourceDb " it does not update the table using the value of the string.  Because this value changes depending on the file I select, I cannot statically declare it. 

    I use " CurrentDb.Execute strSQL2 " to execute the SQL code.  How do I get SQL to read the string from the form?

    Private Sub Database_Button_Click()
    
        Me.Database1_Button.Visible = False
        
        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        
        Dim rsSQL As DAO.Recordset
        Dim strSQL1 As String
        Dim strSQL2 As String
        
        strSQL1 = "SELECT * FROM TblSettingsFE WHERE SettingName = 'DatabaseLocationString'"
        strSQL2 = "UPDATE TblSettingsFE SET SettingString = strSourceDb WHERE SettingName = 'DatabaseLocationString'"
        
        With Application.FileDialog(1)
            If FileDialog(1).Show Then
                FileDialog(1).Filters.Clear
                FileDialog(1).Filters.Add Description:="Database", Extensions:="*.accdb"
                FileDialog(1).AllowMultiSelect = False
                FileDialog(1).Title = "Choose Database File"
                strSourceDb = FileDialog(1).SelectedItems(1)
                
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs
                    If Left(tdf.Name, 4) <> "MSys" Then
                        On Error GoTo ErrHandler1:    '<--- Suppresses error message when there is no table to delete.
                        DoCmd.DeleteObject acTable, tdf.Name    '<--- Deletes linked table.
    ErrHandler1:
    Resume Next
                        DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, acTable, tdf.Name, tdf.Name     '<--- Creates linked table.
                    End If
                Next tdf
                
                Set rsSQL = CurrentDb.OpenRecordset(strSQL1)
                CurrentDb.Execute strSQL2
                MsgBox "You have created a link to a Knowledge Cloud database.", vbInformation, "Completed"
            Else
                MsgBox "No database file was selected.", vbInformation, "Warning"
            End If
        End With
        
        Me.Database1_Button.Visible = True
    
    End Sub



    • Edited by Amedean Saturday, August 26, 2017 5:03 PM
    Saturday, August 26, 2017 4:57 PM

Answers

  • Try this version:

    Private Sub Database_Button_Click()
        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        Dim strSQL2 As String
        Me.Database1_Button.Visible = False
        With Application.FileDialog(1)
            .Filters.Clear
            .Filters.Add Description:="Database", Extensions:="*.accdb"
            .AllowMultiSelect = False
            .Title = "Choose Database File"
            If .Show Then
                strSourceDb = .SelectedItems(1)
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs
                    If Left(tdf.Name, 4) <> "MSys" Then
                        On Error Resume Next
                        DoCmd.DeleteObject acTable, tdf.Name    '<--- Deletes linked table.
                        On Error GoTo 0
                        DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, _
                            acTable, tdf.Name, tdf.Name     '<--- Creates linked table.
                    End If
                Next tdf
                strSQL2 = "UPDATE TblSettingsFE SET SettingString='" & strSourceDb & _
                    "' WHERE SettingName='DatabaseLocationString'"
                CurrentDb.Execute strSQL2
                MsgBox "You have created a link to a Knowledge Cloud database.", _
                    vbInformation, "Completed"
            Else
                MsgBox "No database file was selected.", _
                    vbInformation, "Warning"
            End If
        End With
        Me.Database1_Button.Visible = True
    End Sub

    I removed rsSQL and strSQL1 because they didn't serve any purpose, and I moved the line that assigns a value to strSQL1 down to below where strSourceDb is filled.

    I also streamlined the use of With ... End With.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Amedean Saturday, August 26, 2017 11:42 PM
    Saturday, August 26, 2017 6:00 PM

All replies

  • Put this just before CurrentDb.Execute strSQL2:

       strSQL2 = "UPDATE TblSettingsFE SET SettingString = " & strSourceDb & " WHERE SettingName = 'DatabaseLocationString'"
     


    -Tom. Microsoft Access MVP

    Saturday, August 26, 2017 5:49 PM
  • Try this version:

    Private Sub Database_Button_Click()
        Dim dbsSource As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strSourceDb As String
        Dim strSQL2 As String
        Me.Database1_Button.Visible = False
        With Application.FileDialog(1)
            .Filters.Clear
            .Filters.Add Description:="Database", Extensions:="*.accdb"
            .AllowMultiSelect = False
            .Title = "Choose Database File"
            If .Show Then
                strSourceDb = .SelectedItems(1)
                Set dbsSource = OpenDatabase(strSourceDb)
                For Each tdf In dbsSource.TableDefs
                    If Left(tdf.Name, 4) <> "MSys" Then
                        On Error Resume Next
                        DoCmd.DeleteObject acTable, tdf.Name    '<--- Deletes linked table.
                        On Error GoTo 0
                        DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDb, _
                            acTable, tdf.Name, tdf.Name     '<--- Creates linked table.
                    End If
                Next tdf
                strSQL2 = "UPDATE TblSettingsFE SET SettingString='" & strSourceDb & _
                    "' WHERE SettingName='DatabaseLocationString'"
                CurrentDb.Execute strSQL2
                MsgBox "You have created a link to a Knowledge Cloud database.", _
                    vbInformation, "Completed"
            Else
                MsgBox "No database file was selected.", _
                    vbInformation, "Warning"
            End If
        End With
        Me.Database1_Button.Visible = True
    End Sub

    I removed rsSQL and strSQL1 because they didn't serve any purpose, and I moved the line that assigns a value to strSQL1 down to below where strSourceDb is filled.

    I also streamlined the use of With ... End With.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Amedean Saturday, August 26, 2017 11:42 PM
    Saturday, August 26, 2017 6:00 PM