Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Create Folder in access 2003

Guys

I have a vbscript in access 2003 that apart from other things is supposed to create a folder before it imports the database.

It isnt doing this I have the script which I will attach I believe I am close but not quite there. Can someone please have a look and modify where necessary to allow a folder created before the transfer takes place.

Regards
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

    Dim stDocName As String
    Dim stDocName1 As String
    Dim appAccess
    Dim strDB As String
    
    strDB = Text52 & "\" & Combo58 & ".mdb"

    stDocName1 = "Deletetion_Ind.Master_Del"
    DoCmd.RunMacro stDocName1
    stDocName = "Master"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Set appAccess = CreateObject("Access.Application")
   appAccess.NewCurrentDatabase strDB

 appAccess.Quit
   Set appAccess = Nothing

Set objShell = CreateObject("Scripting.FileSystemObject")

Set ofolder = objShell.createfolder(strDB)

objShell.createfolder ofolder

DoCmd.TransferDatabase acExport, "Microsoft Access", strDB, acTable, "Master_tbl", Combo58, False

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    'MsgBox Err.Description
    MsgBox "Please double check that you havent already done this"
    
End Sub

Open in new window

0
DarrenJackson
Asked:
DarrenJackson
  • 11
  • 6
  • 5
2 Solutions
 
andrewssd3Commented:
Yes you are nearly there - the problem is that strDB contains the full file name for the new database, and you're trying to create a folder with the whole name.  You need to create a folder with just the folder name.  I'm not sure if you separately need to create the mdb file - I suspect TransferDatabase will do that.

Also bear in mind that CreateFolder won't create multiple levels of directories in one go - so if you want to create something like "c:\master\subdir1\subdir2" where master and subdir2 don't exist, it will fail.
0
 
DarrenJacksonAuthor Commented:
yeah there is a subfolder as well

So how would I go about this

Regards
0
 
andrewssd3Commented:
Of course I meant  'master and subdir1 dont exist' - subdir2 must not exist
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
andrewssd3Commented:
You just need to do CreateFolder one level at a time, so something like:
Set ofolder = objShell.createfolder("c:\master")
Set ofolder = objShell.createfolder("c:\master\subdir1")
Set ofolder = objShell.createfolder("c:\master\subdir1\subdir2")

Open in new window

That's using literals, but you get the idea
0
 
DarrenJacksonAuthor Commented:
Ahh ok thanks just going for dinner but will take a look at that in an hour

Thank you
0
 
NorieData ProcessorCommented:
Darren

What's the name of the folder you are trying to create?

Is it the value in strDB?

Aren't you using that to open a database?

Where are you trying to create it?

I think if you remove the On Error Resume Next then you'll probably find a few problems with the code to create a folder.
0
 
DarrenJacksonAuthor Commented:
imnorie
The folder is on a network share and the user selects from combo boxes to adjust the location which either exists or not
Im not opening the db just exporting the contents of a table to another db

I did remove the On Error and it says the the db already exists but as I cant seem to step through the code i cant even see where it is creating it

Attached is the code with some small changes as per andrewssd3
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

    Dim stDocName As String
    Dim stDocName1 As String
    Dim appAccess
    Dim strDB1 As String
    Dim strDB2 As String
    Dim strDB3 As String
    
    
    strDB1 = Text65 & "\" & Combo58 & "\"
    strDB2 = Text65 & "\" & Combo58 & "\" & Text50
    strDB3 = Text65 & "\" & Combo58 & "\" & Text50 & "\" & Combo58 & ".mdb"
    'strDB3 = Text52 & "\" & Combo58 & ".mdb"

    stDocName1 = "Deletetion_Ind.Master_Del"
    DoCmd.RunMacro stDocName1
    stDocName = "Master"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Set appAccess = CreateObject("Access.Application")
   appAccess.NewCurrentDatabase strDB

 appAccess.Quit
   Set appAccess = Nothing

Set objShell = CreateObject("Scripting.FileSystemObject")

Set ofolder = objShell.createfolder(strDB1)
Set ofolder = objShell.createfolder(strDB2)

objShell.createfolder ofolder

DoCmd.TransferDatabase acExport, "Microsoft Access", strDB3, acTable, "Master_tbl", Combo58, False

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    'MsgBox "Please double check that you havent already done this"
    
End Sub

Open in new window

0
 
DarrenJacksonAuthor Commented:
Above code still doesnt work anyone know what I'm doing wrong

Thanks
0
 
andrewssd3Commented:
I don't think you need line 33 any more.  Also are you deleting the created folder between tests - it will error if the subdirectory already exists
0
 
NorieData ProcessorCommented:
Darren

You should check if the directory does exist by navigating to it with Explorer or whatever.

It might be worth checking if any other directories have been created in unexpected places.

As for the code, you need to check the name and path for the folder you are trying to create before you create it.

You can do that by sticking a breakpoint(F9) on the CreateFolder line(s) and running the code.

When the code stops it should be just before the folder is going to be created.

That's when you should check the path and name by looking at the variables strDB1 and strDB2.
0
 
DarrenJacksonAuthor Commented:
Thanks andrew will remove line 33 but as I dont seem to know where the directory is being created I am struggling but I dont want it to error if the directory exists just carry on and then just overwrite the db if that exists

but that part of the code isnt important just yet

Thanks
0
 
NorieData ProcessorCommented:
Darren

You can step through every line of code using F8 if you put a breakpoint on the first line or just hit F8 to start the code.
0
 
DarrenJacksonAuthor Commented:
Yeah normally F8 i would use but it is not working which is odd in it self
0
 
andrewssd3Commented:
You could try
If Not (objShell.FolderExists(strDB1)) Then
Set ofolder = objShell.createfolder(strDB1)
End If
If Not (objShell.FolderExists(strDB2)) Then
Set ofolder = objShell.createfolder(strDB2)
End If

Open in new window

You could even stick in a msgbox for debugging to tell you if they exist or not
0
 
NorieData ProcessorCommented:
Have you tried a breakpoint?

I don't know where the command button is located but if it's on a form set a breakpoint on the button's click event.

Then open/run the form and click the button.

You should then be able to debug.

There are lots of alternative things you could try, the important thing is that the folder path/name needs to be checked to see
if you are using the right values.
0
 
DarrenJacksonAuthor Commented:
Guys when I run the code by itself I get an error saying object doesnt exist but the path is correct that I can see
Private Sub Command67_Click()

Dim stDocName As String
    Dim stDocName1 As String
    Dim appAccess
    Dim strDB1 As String
    Dim strDB2 As String
    Dim strDB3 As String
    
    strDB1 = Text65 & "\" & Combo58 & "\"
    strDB2 = Text65 & "\" & Combo58 & "\" & Text50
    strDB3 = Text65 & "\" & Combo58 & "\" & Text50 & "\" & Combo58 & ".mdb"
    'strDB3 = Text52 & "\" & Combo58 & ".mdb"
    
   If Not (objShell.FolderExists(strDB1)) Then
   Set ofolder = objShell.createfolder(strDB1)
   End If

   If Not (objShell.FolderExists(strDB2)) Then
   Set ofolder = objShell.createfolder(strDB2)
   End If
    

End Sub

Open in new window

0
 
DarrenJacksonAuthor Commented:
it errors at line 15
0
 
NorieData ProcessorCommented:
That error is caused by the objShell not existing.

What is in Text65 when you run this code?

is it an existing directory/path?

Have you considered using VBA's MkDir and Dir instead of the FileSystem Object?
0
 
DarrenJacksonAuthor Commented:
mmmm Text65 contains the path which is a valid path that doesnt exist but the code is supposed to look at this then create the path

VBA's MkDir actually no i havent do you have any code I could use to see if this will get over my probelm?

Regards
0
 
andrewssd3Commented:
Darren - as inmorie says - you have removed the creation of the FileSystemObject from you code - you need to add in this line again after line 14:
Set objShell = CreateObject("Scripting.FileSystemObject")

Open in new window

There should be no problems with the FileSystemObject way of doing things - Microsoft recommend you use that model rather than the old MkDir, Dir etc - they often don'y work well with recursive processing, etc.
0
 
DarrenJacksonAuthor Commented:
doh  thats worked

I blame fridays ;)

OK I will carry on testing

Thanks
0
 
DarrenJacksonAuthor Commented:
Amazingly it is working a treat thanks guys for your help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now