MS Access insert query

Posted on 2013-06-06
Last Modified: 2013-06-06
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 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.
Question by:dimmergeek
LVL 31

Accepted Solution

Helen_Feddema earned 400 total points
ID: 39226821
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


Expert Comment

ID: 39226836
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

LVL 12

Expert Comment

ID: 39226839
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)

Expert Comment

ID: 39226842
I think the problem is a missing double quotes, try using single quotes instead, is more clear

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question