dimmergeek
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:
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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 & " ' )"
strSQL = "INSERT INTO [files] ([fileName]) VALUES( ' " & dPath & " ' )"
Open in new window
Regards