Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38

    Unanswered: sql fails on network drive

    I have this sql that works fine but, when i give it a directory on a network drive it fails. I am a novice @ sql so any advise would be appreciated. U will notice the function is called with the location of the scans. When i single step the sql it gets the delete line & then the strFilename line but then the do statement jumps to the end of the function :-(.

    Here is the sql:
    Function Fill_Tbl_Files(ByVal Folder As String, Optional ByVal Extension As String = "*")
    'In this example, the Table that will contain the names of the files is named _Tbl_Files_ and
    ' it has a column [FileName] that contains the full name of a file
    ' (Disk:\FullPath\FileName.Extension)in each row.
    '
    'Code:
    '---------
    Const c_strSQL As String = "INSERT INTO Tbl_Files ( FileName ) VALUES ( '{@Filename}' );"
    Dim strFileName As String

    CurrentDb.Execute "DELETE FROM Tbl_Files;", dbFailOnError
    If Right(Folder, 1) <> "\" Then Folder = Folder & "\"
    strFileName = Dir(Folder & "*." & Extension)
    Do Until Len(strFileName) = 0
    CurrentDb.Execute Replace(c_strSQL, "{@Filename}", Folder & strFileName), dbFailOnError
    strFileName = Dir
    Loop
    '---------
    'This function could for instance be called when the form that uses the Tbl_Files table opens.
    '***************
    End Function
    Private Sub get_directory_Click()

    Dim stra As String

    'In this example, the Table that will contain the names of the files is named _Tbl_Files_ and
    ' it has a column [FileName] that contains the full name of a file
    ' (Disk:\FullPath\FileName.Extension)in each row.
    '


    'stra = Fill_Tbl_Files("c:\tmp\scans")
    stra = Fill_Tbl_Files("c:\documents and settings\...\desktop\my scans")


    End Sub ' hello

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm moving this thread from Microsoft SQL Server - dBforums to Microsoft Access - dBforums because it is really an Access / Jet question and has nothing to do with SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    When you are stepping through your code I would suggest you have the Immediate Window open. View/Immediate Window. Then when you are on the strFilename line then type:

    ?Folder & "*." & Extension

    In the immediate window and see if it is what you expected. It sounds to me like what you are expecting and what is actually being looked at are two different things. strFilename is probably equal to the empty string ("").

  4. #4
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    No strfilename has the string c:\documents and settings\ ... . But of course the do len ... acts like its 0 :-(.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would change:

    Do Until Len(strFileName) = 0


    To:

    Do Until strFileName=""

    In examples I have seen with Dir() that has always been the condition to look for.

  6. #6
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    It seems the links thru 'desktop' caused the failure. When i changed the link directly to the network drive ie \\wbcc\scans then the sql worked fine :-).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •