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
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
FileCopy sPath, dPath
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.
If you want to see the DB, I have it attached, as well.