?
Solved

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

Posted on 2001-09-12
6
Medium Priority
?
1,324 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

765 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