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

Thanks
yasanthaxAsked:
Who is Participating?
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

do a DEBUG > COMPILE

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")

with

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


.
1
yasanthaxAuthor Commented:
Hi

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)
0
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
   else
    folder=folder & "\"   ' ADD the trailing slash
end if

MakeSureDirectoryPathExists folder


.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

yasanthaxAuthor Commented:
Hi

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

?DIR("C:\Users\USER\Documents\_sqlTEST\SQLTestDB_Folders\CASE0003",vbDirectory)
CASE0003
0
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.
0
Rey Obrero (Capricorn1)Commented:
btw, are the two PC have the same OS, Office version?
0
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?

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

see this A Naming Scheme for Database Tables and Fields
0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.