Solved

MS Access insert query

Posted on 2013-06-06
4
449 Views
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

Private Type BROWSEINFO
    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 tBI As BROWSEINFO
    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
    DoEvents
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.
Database1.mdb
0
Comment
Question by:dimmergeek
4 Comments
 
LVL 31

Accepted Solution

by:
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"
      .Filters.Clear
      '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
      Else
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   
   SelectFile = strFileNameAndPath
   
ErrorHandlerExit:
   Set fd = Nothing
   Exit Function

ErrorHandler:
   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.AddNew
   rst![FileName] = strText
   rst.Update
   rst.Close
   
End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
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


Regards
0
 
LVL 12

Expert Comment

by:pdebaets
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)
0
 
LVL 3

Expert Comment

by:xavixsb
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 & " ' )"
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now