Dir statement throws a type mismatch

[Hydra]

If Len(Dir("C:\Cobra Conversion\MSP-CobraConversion.mdb")) > 0 Then

or

If Not Dir("C:\Cobra Conversion\MSP-CobraConversion.mdb") is Nothing

both throw an error that says type mismatch.

I don't see a problem with either one.


????

 

[Jack Leach]

With the space in the pathname you will need to enclose the path itself
inside another set of quotes. I'm not positive but think this will work:

If Len(Dir("""C:\......ion.mdb""")) > 0

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Hydra]

Weird, because this code used to work.

With the space in the pathname you will need to enclose the path itself
inside another set of quotes. I'm not positive but think this will work:

If Len(Dir("""C:\......ion.mdb""")) > 0

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

So... did that work with the double quotes?
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Hydra]

Nope, took the space out and renamed the directory, and took out the hyphen
too.
Still throws a type mismatch. also Trid it with the extra quotes and it
won't compile. Throws an error that says" expected list separator or )"

 

[John Spencer]

The statement
If Len(Dir("C:\Cobra Conversion\MSP-CobraConversion.mdb")) > 0 Then
works for me.

Check your library references.
Check that you don't have a function named Len or Dir that you have created in
your VBA code

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hydra]

visual basic for applications
MS Project 12.0 object library
OLE automation
Microsoft Office 12.0 library
Microsoft Access 11.o Object library
Microsoft Excel 11.0 Object library

I must have the "Gremlins 100+X library", too.
I frequently have code copied right out of the help screen or users group
that won;t work as advertised.

 

[Hydra]

If I just skip the if statement (since I know the ifle exists)
then

appAccess.OpenCurrentDatabase strDB

Where strDB = "C:\Cobra Conversion\MSP-CobraConversion.mdb"

Throws an error that says thte database is missing or opened exclusively by
another user, when I know it is there and not open.

 

[John Spencer]

Sounds like your VBA may be suffering from some corruption or confusion.

Have you tried to decompile/recompile your code?

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command
prompt while Access is not running. It is all one line, and includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the
database for you. Follow the steps for the first symptom in this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hydra]

I had a misspelled DIM statement that tried to DIM Dir to a variable, hence
the type mismatch.

But now Opencurrentdatabase cannot find the file or claims it is open
exclusively.

 

[Clifford Bass]

Hi,

It may well be indeed open. Especially if you opened it in code and
the code subsequently failed without the database being closed. I would
suggest that if you are not already doing so, that you always use error
trapping and object clean up to avoid this problem. Try a reboot of your
computer to make sure any hidden instances of the database have been closed.

Clifford Bass

 

[Hydra]

Tried Rebooting, still get the same error.

Anyway, if it was open exclusive, then I would not be able to open it
manually either, right?

I doublechecked the spelling of the path and filename, and made a new path
and filename, with the same result.

 

[Clifford Bass]

Hi,

Curious! You are right, if it was opened exclusively, you should not
be able to open it manually. For that reason and because it failed after
rebooting, I then would conclude the issue is not with it being already
opened. You mentioned that you have other issues on and off with Access.
Have you tested it on a different computer to see if the problem is specific
to your computer? If so, a complete uninstall of Access (or maybe Office)
and a reinstall may be in order.

Clifford Bass

 

[Hydra]

I have not been through John Spencer's clean up drill yet. otherwise my
computer is controlled by IT. I don't see a whole new install happening.

Here is another curious thing. My code hangs up and throws an error message.
As long as that code is still in debug, I cannot open any Access files.
anything else willopen, but Icannot open any access file until I rest the VB
code (which is running under MSProject).

Is there some other way to open a database other than opencurrentDB?

If I report out the value of

Len(Dir("C:\CobraConversion\MSPCobraConversion.mdb"))

I get a value of 22, which is correct for the filename, minus the path.

 

[Clifford Bass]

Hi,

Okay, this is really odd. You stated earlier "Weird, because this code
used to work.". I have a mantra that if something has changed (no longer
works), then something has changed. So, has your IT changed some setting(s)
on your computer recently? Maybe that limits how many instances of Access
you can be running? Also, let us know if it works fine on another computer.
Any chance you can try it at home on a non-IT controlled computer?

Clifford Bass

 

[Tony Toews [MVP]]

visual basic for applications
MS Project 12.0 object library
OLE automation
Microsoft Office 12.0 library
Microsoft Access 11.o Object library
Microsoft Excel 11.0 Object library

That's an interesting set of references. Do you really need Project,
Office and Excel? Do you really have Excel 11 but Office 12 on your
system?

For the future the following code will get the references

Sub ViewMoreReferenceDetails()

Dim refIDE As Object

For Each refIDE In
Access.Application.VBE.ActiveVBProject.References
Debug.Print refIDE.Description & " " & _
IIf(refIDE.IsBroken, "Broken", "") & vbCrLf & _
" " & refIDE.name & " - " & refIDE.Major & "." & _
refIDE.Minor & " " & refIDE.FullPath
Next refIDE

End Sub

Tony

 

[Hydra]

Can't Run it at home since I do not have MSProject there.
For now, I got around the problem by using

Set dbs = OpenDatabase("C:\Database\MSPData.mdb")
Set rstResources = dbs.OpenRecordset("Resources")

Then I run some loops that read the resouces and tasks from the project file
into the DB.

Still don't know why the other code won't work.
Thanks for the help, though.

 

[Clifford Bass]

Hi,

This makes sense that it would work because it is not firing up another
instance of Access. Which gives a possible clue as to what is happening. Do
you perchance have multiple versions of Access on your computer? If you do,
it may be trying to open the file with an older version of Access than the
database itself. Even if you are running a newer version of Access. In such
an instance, you would get the incorrect error that says the databse is
either already opened exclusively or that it cannot be found.

Clifford Bass

 

[Hydra]

No, I only have one version of Access. This code is located in and attached
to an MSProject file, so there are no instances of Access open. All I'm
trying to do is open a single instance of access and then open an existing
file as the current database. Then I will either append to or (more likely)
overwrite the existing data in one table.

I use the DIR function to make sure it exists, and if it does not, build a
new file from scratch.

 

[Clifford Bass]

Hi,

I am afraid I am at a loss. I do not have MSProject and so cannot test
to see what that is doing. Perhaps MSProject sometimes places exclusive
locks on the Access files? And maybe does not release them, or if it does,
maybe it sometimes takes its merry time about it? Perhaps if you feel a need
to pursue it further you might post to the MSProject discussion group--see if
anyone on that group has encountered the issue in the past.

Good Luck,

Clifford Bass