• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

MS Access insert query

I am developing an MS access DB for a buddy.
He wants users to be able to select an image file, copy from their HDD to a netwrok share and then insert a record in the DB with the new file location.
Everything works except the INSERT statement.

Here is my code:

Option Compare Database

    hOwner          As Long         'handle to window opening dialog
    pidlRoot        As Long         'A pointer to an ITEMIDLIST structure (a.k.a. a PIDL) which identifies the root folder for the dialog box. The user's selection is limited to this folder and any subfolders under it
    pszDisplayName  As String       'Receives the null-terminated display name of the folder the user selects. This must be initialized to an empty string of at least 260 characters
    lpszTitle       As String       'The title of the dialog box, which will appear above the folder tree
    ulFlags         As Long         'See BIF flags above
    lpfn            As Long         'A pointer to the BrowseCallbackProc callback function used to process the dialog box's messages. To use the default behavior, set this to 0
    lParam          As Long         'An application-defined value to pass to the callback function, if needed
    iImage          As Long         'Receives the index of the system image associated with the user's selection
End Type

Private Declare Function SHBrowseForFolder Lib "shell32" Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pIdl As Long, ByVal pszPath As String) As Long

Private Sub Command0_Click()
    Dim pIdl As Long
    Dim sPath As String
    tBI.lpszTitle = "Please Select your Image File..."
    tBI.ulFlags = &H4000
    pIdl = SHBrowseForFolder(tBI)
    'Check for cancel
    If pIdl = 0 Then Exit Sub

    'Get selected path from the id list, will rtn False if the id list can't be converted
    sPath = String$(260, 0)
    SHGetPathFromIDList ByVal pIdl, ByVal sPath
    ' Display the path and the name of the selected folder
    BrowseDirectory = Left(sPath, InStr(sPath, vbNullChar) - 1)
    Me.Text1 = sPath
    Dim avarSplit As Variant
    avarSplit = Split(sPath, "\")
    Dim intIndex As Integer
    intIndex = UBound(avarSplit)
    dPath = "G:\BR\QA\Images\" & avarSplit(intIndex)
    MsgBox dPath, vbOKOnly, "File Path to Upload"
    strSQL = "INSERT INTO [files] ([fileName]) VALUES(""" & dPath & """" & ")"
    'MsgBox strSQL, vbOKOnly, "SQL to Execute"
    Me.Text3 = strSQL
    DoCmd.RunSQL strSQL
    FileCopy sPath, dPath
End Sub

Open in new window

The strSQL statement ends after the dPath variable.  I have tried Chr(34), single quotes, double quotes....no matter what I do I cannot get the strSQL statement to close.

Please help.

If you want to see the DB, I have it attached, as well.
1 Solution
Helen FeddemaCommented:
I think it is possible to greatly simplify this code.  No need for API calls to browse for a file.  Here is a function to get the full path and name of a file (change the extensions as needed for your image files).  

Public Function SelectFile() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 3-Aug-2009
'Last modified 3-Aug-2009
'Word, Access and Excel have a FileDialog property of their Application
'object; Outlook does not

On Error GoTo ErrorHandler

   Dim fd As Office.FileDialog
   Dim varSelectedItem As Variant
   Dim strFileNameAndPath As String
   'Create a FileDialog object as a File Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   With fd
      'Set AllowMultiSelect to True to allow selection of multiple files
      .AllowMultiSelect = False
      .Title = "Browse for File"
      .ButtonName = "Select"
      'Modify filters as needed
      .Filters.Add "Documents", "*.doc; *.txt", 1
      .InitialView = msoFileDialogViewDetails
      If .Show = -1 Then
         'Get selected item in the FileDialogSelectedItems collection
         For Each varSelectedItem In .SelectedItems
            strFileNameAndPath = CStr(varSelectedItem)
         Next varSelectedItem
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   SelectFile = strFileNameAndPath
   Set fd = Nothing
   Exit Function

   MsgBox "Error No: " & Err.Number _
      & " in SelectFile procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

The return value of the function is a Text value, which can then be saved to the table, using code like this:

Public Sub AddValue(strText)

   Dim rst As DAO.Recordset
   Dim strText As String
   Set rst = CurrentDb.OpenRecordset("Files")
   rst![FileName] = strText
End Sub

Open in new window

Try this:

- strSQL = "INSERT INTO [files] ([fileName]) VALUES(""" & dPath & """" & ")"
- DoCmd.RunSQL strSQL

+ Dim rs As DAO.Recordset
+ Set rs = CurrentDb.OpenRecordset("files")
+ rs.AddNew
+ rs("fileName").Value = dPath
+ rs.Update

Open in new window

Try this:

strSQL = "INSERT INTO [files] ([fileName]) VALUES(" & chr(34) & dPath & chr(34) & ")"

(Side note: you may want to run that insert only after you've successfully copied the file)
I think the problem is a missing double quotes, try using single quotes instead, is more clear

strSQL = "INSERT INTO [files] ([fileName]) VALUES( ' " & dPath & " ' )"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now