?
Solved

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

Posted on 2001-09-12
6
Medium Priority
?
1,392 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

862 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