Solved

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

Posted on 2001-09-12
6
1,267 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 8

Accepted Solution

by:
dovholuk earned 50 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
access to sql migration 5 24
Trying to figure out how to design a form 4 26
Calculate Weeks in A "Fiscal Month" 8 26
Error 438 6 16
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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 …

726 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