vbdirectory Run-time error '13':

I have copied this code from an existing database to a new test base on my PC and changed the location where each database record will create a folder when the on current record does not have a folder assigned. The field "directory" will have a hyperlink which will open a database folder.  The test database is different from the exiting database and also have a SQL backend just for background.

However I get an error related for VBDirectory.  Am I missing some statements for the test database such as Public Sub OpenLinkedDB() that are failing the VB directory line from being performed?  The database  I am not sure what VBDirectory does.

folder = "C:\Users\USERA\Documents\_sqlTEST\SQLTestDB_Folders\" & Format(Me.P_ID, "\C\A\S\E0000")

If Len(DIR(folder, vbDirectory)) = 0 Then
   MkDir (folder)
   MsgBox "new folder created here: " & folder, vbInformation, "NEW FOLDER"
End If

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
from your VBA window
Tools > References
check if you have references with MISSING prefix.
uncheck it, and find the available version of the reference and select


correct any error/s raised..

also, change your folder path

folder = "C:\Users\USERA\Documents\_sqlTEST\SQLTestDB_Folders\" & Format(Me.P_ID, "\C\A\S\E0000")


folder = environ("userProfile") & "\Documents\_sqlTEST\SQLTestDB_Folders\" & Format(Me.P_ID, "\C\A\S\E0000")

yasanthaxAuthor Commented:

Still have issues

Thanks for the very useful code change .  I am still getting errors when calculating the below code.  The paramaters /variables seem to be OK.  Is this some sort of data base corruuption as the DIR command is what fails.

DIR(folder, vbDirectory)
Rey Obrero (Capricorn1)Commented:
post the string created by this

folder = environ("userProfile") & "\Documents\_sqlTEST\SQLTestDB_Folders\" & Format(Me.P_ID, "\C\A\S\E0000")
debug.print folder

also be aware that the format part of your "folder" path creates more directories.
does the folders "\C\A\S\" already exists?

in creating folders i normally use this codes

place this code in the top of a regular module

Option Compare Database
Option Explicit
Public Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long

Open in new window

'you can then use the function like this

folder = environ("userProfile") & "\Documents\_sqlTEST\SQLTestDB_Folders\" & Format(Me.P_ID, "\C\A\S\E0000")
if right(folder,1)="\" then
   'do nothing
    folder=folder & "\"   ' ADD the trailing slash
end if

MakeSureDirectoryPathExists folder

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

yasanthaxAuthor Commented:

Still have issues with the new database only.  I ran the following code in the immediate window of both databases, and failed only in my new database. I think it is related to the database missing setups/declarations or corruption

Rey Obrero (Capricorn1)Commented:
do a Compact and Repair if you haven't done it yet.

if that is still unsuccessful, create a New blank db and import the objects from the erring db.
Rey Obrero (Capricorn1)Commented:
btw, are the two PC have the same OS, Office version?
yasanthaxAuthor Commented:
same computer and Access (version 12) different sessions/windows.
I tried the compact repair and debug again  and still no change

However think I know the problem as on the new database I accidentally used a table field name call DIR and hence it does not recognise the DIR as a function when the database is opened. This is noticeable when wriiting DIR in code where the previous database highlights a pointer with the syntax but not the new one.
Therefore do you recommend I start a new database from scratch and ensure field names are not related to functions?

Rey Obrero (Capricorn1)Commented:
....and ensure field names are not related to functions?

see this A Naming Scheme for Database Tables and Fields

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yasanthaxAuthor Commented:
The issue  with code was not because the code was wrong but a unrelated table field name is the database did not follow best practice   naming conventions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.