Access Error Object Required

I'm using the following code to copy files from one folder to another and receive the error message object required on line:
objfso.CopyFile strSource & strFileName, strDest, OverWriteExisting

Open in new window


strSource = "C:\Users\vincent.shields\Documents\MATS\dbs\"
strDest = "\\itio-ARL-fs01.itsc.hhs-itsc.local\alj_arlington\MATS\Testdbs"
For Each strFileName In Array("matsdbarl.accdb", "matsdosarl.accdb")
objfso.CopyFile strSource & strFileName, strDest, OverWriteExisting
Next


Set objfso = Nothing

Open in new window

shieldscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You need to initialize objfso first:

Option Compare Database
Option Explicit

Public Sub Test()

  Dim objfso As Object
  
  Dim strSource As String
  Dim strDest As String
  Dim strFileName As Variant
  
  strSource = "C:\Users\vincent.shields\Documents\MATS\dbs\"
  strDest = "\\itio-ARL-fs01.itsc.hhs-itsc.local\alj_arlington\MATS\Testdbs"
  Set objfso = CreateObject("Scripting.FileSystemObject")
  For Each strFileName In Array("matsdbarl.accdb", "matsdosarl.accdb")
    objfso.CopyFile strSource & strFileName, strDest, True
  Next

  Set objfso = Nothing
  
End Sub

Open in new window


btw, in Office we use VBA (VisualBasic for Applications), which is a slightly different dialect then VBScript.
0
Paul JacksonSoftware EngineerCommented:
strDest definition is missing a \ at the end of the line.

strDest = "\\itio-ARL-fs01.itsc.hhs-itsc.local\alj_arlington\MATS\Testdbs\"

You also need to be appending the filename to the end of strDest when using the .CopyFile method :

objfso.CopyFile strSource & strFileName, strDest & strFileName, True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Thanks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
I disagree with the marked post as the answer, cause it is clearly not the reason for the error message "Access Error Object Required".
0
shieldscoAuthor Commented:
ste5an - I removed your suggested code below added the / that Paul Jackson suggested and everything ran ok.  I received a permission denied error when using your suggested code.

Option Explicit

Public Sub Test()

Dim objfso As Object

Open in new window

0
ste5anSenior DeveloperCommented:
Well, I still disagree..

Paul's solution:
Paul's solution.
My solution:
ste5an's solution.EE29116916.accdb
0
Paul JacksonSoftware EngineerCommented:
Hey ste5an for a start the code you present as my solution is the original code and does not include the changes I suggested.
0
shieldscoAuthor Commented:
Paul's solution that worked for me

Private Sub Command0_Click()


 ' Dim objfso As Object
  
  'Dim strSource As String
 ' Dim strDest As String
 ' Dim strFileName As Variant
  
  strSource = "C:\Users\vincent.shields\Documents\MATS\dbs\"
  strDest = "\\itio-ARL-fs01.itsc.hhs-itsc.local\alj_arlington\MATS\Testdbs\"
  Set objfso = CreateObject("Scripting.FileSystemObject")
  For Each strFileName In Array("matsdbarl.accdb", "matsdosarl.accdb")
  'objfso.CopyFile strSource & strFileName, strDest, True
    
   objfso.CopyFile strSource & strFileName, strDest & strFileName, True
  Next

  Set objfso = Nothing
  


End Sub

Open in new window

0
ste5anSenior DeveloperCommented:
btw, Option Explicit is THE tool to find undeclared variables and should/must be in every code module.

Set it as default in the VBA IDE under Tools/Options/Variable Declaration Required.
0
ste5anSenior DeveloperCommented:
hmm, nope..

paul2.PNG
0
shieldscoAuthor Commented:
Works for me .... running Access 2016
0
ste5anSenior DeveloperCommented:
@shieldsco: Paul does not set objfso. This is the important part from my post.
0
shieldscoAuthor Commented:
That was part of my original code
0
ste5anSenior DeveloperCommented:
It's not. So, in this case, you crippled the code snippets beyond recognition.

But I still have doubts, cause the database I've posted does not throw a 424 error, even when it does not have Paul's strDest & strFileName. In this case, the error would be different.

Post your entire original code.

Just to be clear, the error code would be different, when objfso is initialized.
Capture.PNG
0
shieldscoAuthor Commented:
Here it is again:


Private Sub Command0_Click()


 ' Dim objfso As Object
  
  'Dim strSource As String
 ' Dim strDest As String
 ' Dim strFileName As Variant
  
  strSource = "C:\Users\vincent.shields\Documents\MATS\dbs\"
  strDest = "\\itio-ARL-fs01.itsc.hhs-itsc.local\alj_arlington\MATS\Testdbs\"
  Set objfso = CreateObject("Scripting.FileSystemObject")
  For Each strFileName In Array("matsdbarl.accdb", "matsdosarl.accdb")
  'objfso.CopyFile strSource & strFileName, strDest, True
    
   objfso.CopyFile strSource & strFileName, strDest & strFileName, True
  Next

  Set objfso = Nothing
  


End Sub

Open in new window

0
shieldscoAuthor Commented:
Remove your option explicit
0
ste5anSenior DeveloperCommented:
Please read my comments again.

When Paul's post is the solution to your problem, then the original error was not "Object Required". In this case ignore my comments. But your original post does not contain a line

Set objfso = CreateObject("Scripting.FileSystemObject")

Open in new window


The code in the last post from you is already the mixed complete code which solves

a) the object requried error
and
b) a glitch in how you named the destination file (which I haven't seen)

That glitch resulted than in that permission denied error. So, yes, Paul's post was helpful to avoid this error. But it didn't solve the original error.
0
shieldscoAuthor Commented:
Again it worked for me.. no more time to go back and forth with you. Paul's solution worked for me period. I have posted the final code that includes Paul's suggestions. Please note that your suggested declarations statements have been commented out.
0
Paul JacksonSoftware EngineerCommented:
Just split the points it is obvious that both solutions are required in order for it to work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.