MS Access insert query

Posted on 2013-06-06
Medium Priority
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 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.
Question by:dimmergeek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 31

Accepted Solution

Helen Feddema earned 1600 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

801 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