Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check if directory exists in VBA + check if table exists in VBA

Posted on 2001-09-12
6
Medium Priority
?
1,359 Views
Last Modified: 2007-11-27
Hi,

anyone knows some clean code in VBA for following problems.

1. Check if a certain directory (for example: C:\test) exists, if not create this directory.

2. Check if a certain table in the current access database exists.

Tim
0
Comment
Question by:thimerion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6476208
just use this bit of code:
dir("c:\windows",vbDirectory)

it will return "" if the string is not a directory...

dovholuk
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 6476340
Tim, these functions might be usefull

Function IsDir(strPath As String) As Boolean
  IsDir = (Len(Dir(strPath)) > 0)
End Function

Function IsTable(strTable As String) As Boolean
  Dim db As Database
  Dim tdf As TableDef
  Dim fTable As Boolean
 
  Set db = CurrentDb
  For Each tdf In db.TableDefs
    If tdf.Name = strTable Then
      fTable = True
      Exit For
    End If
  Next tdf
 
ExitHere:
  Set tdf = Nothing
  Set db = Nothing
 
  IsTable = fTable
 
 
End Function


Mark
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6476399
man i must have been really tired when i read your question. (6:20 am local time)

1.) Here's a complete function:

Function DoesDirExist(Path as string) as boolean
     if dir(path, vbdirectory) <> "" then DoesDirExist = True
end function

2.) Personally, i like to select tables from the MSysObjects table instead of looping through the tables. but a tabledef loop should work.

Function DoesTableExist(TableName as string) as boolean

     Dim RS As DAO.Recordset

set rs = currentdb.openrecordset("SELECT Name " _
     & "FROM MSysObjects
     & "WHERE Name = " & chr(34) & TableName & chr(34) & " and (Type = 4 or Type = 6)

if NOT rs.eof then DoesTableExist = true
rs.close
set rs = nothing
end function

sorry for not answering completely the first time... i hadn't popped any coffee! :)

dovholuk
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 8

Accepted Solution

by:
dovholuk earned 200 total points
ID: 6476413
ok, so i STILL didn't read your question fully. geesh.

here's a revised #1:

Function DoesDirExist(Path as string) as boolean
    if dir(path, vbdirectory) <> "" then
          DoesDirExist = True
    else
        Dim i As Long
        i = 4 'initialize the variable PAST 0, to the first backslash "\"
        Do Until InStr(i, path, "\") = 0
           MkDir (Left(path, InStr(i, path, "\") - 1))
           i = InStr(i, path, "\") + 1
        Loop
    end if          
end function

that should work for you. if not, let me know...

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6476416
sorry. forgot the On Error Resume next... *sigh*

Function DoesDirExist(Path as string) as boolean
   if dir(path, vbdirectory) <> "" then
         DoesDirExist = True
   else
       Dim i As Long
       On Error Resume Next
       i = 4 'initialize the variable PAST 0, to the first backslash "\"
       Do Until InStr(i, path, "\") = 0
          MkDir (Left(path, InStr(i, path, "\") - 1))
          i = InStr(i, path, "\") + 1
       Loop
   end if          
end function

hopefully the last time,
dovholuk
0
 
LVL 1

Author Comment

by:thimerion
ID: 6476425
Ok Dovholuk, you gave me the complete solution, so you get the points!  

Also thanks to the others... (also good answers)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question