Link to home
Start Free TrialLog in
Avatar of dimmergeek
dimmergeekFlag for United States of America

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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 & " ' )"