Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Compile Error for File Copy (2002 - SP1)

    Hello.... I'm getting a compile error. Hans wrote this code for me for a project in Access97. I have pasted the code into a 2002 project, and now I get a compile error. Can someone look at this code and tell me what to change to make it work in 2002?

    Function File_Copy()

    Dim dbs As DAO.Database, rs As DAO.Recordset
    ' Set up objects for the database and the recordset
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("File Copy")
    ' While there is data in the recordset
    Do While Not rs.EOF
    ' Copy from InputFilename to OutputFilename
    FileCopy rs!InputFilename, rs!DestFilename
    ' Get the next record from the recordset
    rs.MoveNext
    Loop
    ' Clean up after ourselves, this releases memory back to windows
    Set rs = Nothing
    Set dbs = Nothing

    End Function

    Thanks!
    KST

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    I selected Microsoft DAO 3.51 in the Reference area, and took care of the compile error. Now, I'm getting "RunTime Error 3061 - Too Few Parameters. Expected 1."

    Can ya help?

    Thanks!
    KST

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error for File Copy (2002 - SP1)

    You should set a reference to the Microsoft DAO 3.6 Object Library in Access 2002, not to the 3.51 version. If you still get the runtime error, on which line does it occur?

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    Hey Hans, thanks for your help again... One thing I found.... this file is actually Access 2000 format... don't know if that matters. And you are correct about the reference file... However, I get the same error either way.

    This is the line of code I get the Runtime Error...... Set rs = dbs.OpenRecordset("File Copy")

    Again, that error is: "Run-time Error 3061 Too few parameters Expected 1"

    Thanks!
    KST

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error for File Copy (2002 - SP1)

    It shouldn't matter whether the database is in Access 2000 or in Access 2002 format (I only use Access 2000 format).

    Is File Copy a table or a query?

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    It's a query.... and your code too... LOL! I think originally, we had it working off of a table in Access97

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error for File Copy (2002 - SP1)

    The code is actually from <!profile=patt>patt<!/profile>, not me - see <post#=228550>post 228550</post#>. Can you post the SQL for the query you're using now?

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    Wow! I thought it was code you sent me... Anyway, you've helped me alot in the past...

    Here's the SQL:
    SELECT tbl_Image_Selection_Info.[Image File Path Calc] AS InputFilename, "C:1A_Image_DatabaseImage Selections" & Forms!frm_Selection_List!Selection & "" & tbl_Image_Selection_Info.[Image File Name] AS DestFilename, tbl_Image_Selection_Info.[Selection Name]
    FROM tbl_Image_Selection_Info
    WHERE (((tbl_Image_Selection_Info.[Selection Name])=[Forms]![frm_Selection_List]![Selection]));

    Thanks!
    KST

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error for File Copy (2002 - SP1)

    DAO operates at a low level, it does not "know" about Access forms, so it doesn't recognize the reference [Forms]![frm_Selection_List]![Selection]. You can get around it like this:

    - Open the query in design view.
    - Change Forms!frm_Selection_List!Selection to [Forms]![frm_Selection_List]![Selection]
    - Select Query | Parameters...
    - Enter [Forms]![frm_Selection_List]![Selection] in the Parameter column, and Text in the Data Type column.
    - Click OK.
    - Close and save the query.

    Change the function as follows:

    Function File_Copy()
    Dim dbs As DAO.Database, rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    ' Set up objects for the database and the recordset
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("File Copy")
    qdf.Parameters("[Forms]![frm_Selection_List]![Selection]") = [Forms]![frm_Selection_List]![Selection]
    Set rs = qdf.OpenRecordset("File Copy")
    ' While there is data in the recordset
    Do While Not rs.EOF
    ' Copy from InputFilename to OutputFilename
    FileCopy rs!InputFilename, rs!DestFilename
    ' Get the next record from the recordset
    rs.MoveNext
    Loop
    ' Clean up after ourselves, this releases memory back to windows
    rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    End Function

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    OK....

    Now I'm getting an error:

    "Run-Time Error 3421 Data type conversion error"

    This is the line it says the problem is in:

    Set rs = qdf.OpenRecordset("File Copy")

    What cha think Hans?
    KST

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error for File Copy (2002 - SP1)

    I'm afraid we won't be able to solve this without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  12. #12
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    Here it is...

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error for File Copy (2002 - SP1)

    Thanks. I made a stupid mistake. The line

    Set rs = qdf.OpenRecordset("File Copy")

    is incorrect, it should be

    Set rs = qdf.OpenRecordset

    Sorry for that!

  14. #14
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    Hans, no problem buddy! I appreciate your effort! It works now....

    Thanks not only for you help this time, but your help in the past! You and Patt both have helped me alot!

    Isn't this a great forum? I recommend it to everyone!

    Thanks again!
    Have a good weekend!
    KST

  15. #15
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Compile Error for File Copy (2002 - SP1)

    Wrong Post Hans...

    It's the thread here.... It's the code posted here...

    My mistake....

    Anyway, I'm getting an error.... I've been working with it, and it looks like the the computer can't copy that fast.... There's 300 files. When I use old code we / you / I / Pat worked on a couple of years ago (MSAccess 97), it works exactly the same, only copying about 42 files before I get an error. But if I slow it down, using a form as a timer, all the files move, w/ no error.

    So, my theory is the computer can't copy them fast enough, and I get the "File Not Found" error.

    What do you think? Do you have a solution, other than using the form as a timer to slow the process down?

    Thanks!
    KST

Page 1 of 2 12 LastLast

Posting Permissions

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