Solved

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

Posted on 2001-09-12
6
1,202 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
  • 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now