Dir statement throws a type mismatch

H

Hydra

It gets worse.

I took code right out of the help file and pasted it in the visual basic
editor under Excel on my home PC----And got the same error message: can't
find the file or it is opened exclusively by another user.

Here is the code:

Sub DisplayTable()

Dim strDB As String
Dim appAccess As Access.Application


Const strPath = "C:\Pct\"
strDB = strPath & "MSPData2"
Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.OpenCurrentDatabase strDB
appAccess.DoCmd.OpenTable "MSPData"

End Sub


Access window opens but the Opencurrentdatabase command fails.

Is there some other way to open a database and access the tables?
 
D

Douglas J. Steele

You're setting strDB to

strDB = strPath & "MSPData2"

That should probably be

strDB = strPath & "MSPData2.MDB"

(or maybe strDB = strPath & "MSPData2.mpp": I can't tell still I jumped in
halfway through the conversation!)
 
H

Hydra

Found the problem.

I had parens around the filename as in
expression.OpenCurrentDatabase(filepath, Exclusive, strPassword)

as described in the spec.

Grrr.
 
H

Hydra

This got very confused somewhere along the way.
I used the dir statement to make sure a file existed and then opened the
file using
the OpencurrentDatabase command, but I had a problem with the Dir statement
and then the opencurretndatabase command.

This command always threw an error saying the file did not exist or was
opened exclusively by another user. I tried all kinds of variation on a theme
and always got the same error. But, I could create a new database from
scratch every time by creating a new database and defining a new tabledef.

I'm reading that excel file and an MSProject file into the database so I can
run some comparison reports. I want to open the database and overwrite the
data in two tables so I can run the reports. I couldn't get the dtabase open
first because I had an error in the Dir check and then because the
opencurrentdatabase would not open the file.

But I finally found that opencurrent database only works without the parens
which appear to be called out in the help screen specification.
 
C

Clifford Bass

Hi,

The parantheses appear in the specification because that is how it
appears when coded. If the Application object class is written in VB, the
OpenCurrentDatabase method would probably look like this:

Public Sub OpenCurrentDatabase(filepath, Exclusive, bstrPassword)

' the code for doing this action

End Sub

When calling it, if you use the Call statement, you do have to include
the parantheses. When calling is without the Call statement you do not use
the parantheses.

Call appAccess.OpenCurrentDatabase(strDB)

appAccess.OpenCurrentDatabase strDB

I do not know why they made that distinction. It is odd, and can catch
you up. Plus, if you code in VB .NET, you will have to use the parantheses
all the time.

On top of that, to further confuse people, if you only use one
parameter and accidentally use the parantheses, it still will work. This is
because the parantheses around a single item evaluates to that one item,
which is then used as the first parameter.

appAccess.OpenCurrentDatabase (strDB)

However, if you try to use more than one item inside of parantheses, it
should give you a compile error. This because the comma is not a valid
operator. So with

appAccess.OpenCurrentDatabase (strDB, True)

it will try to use the "(strDB, True)" in its entirety as the first
parameter and it will determine that "strDB, True" cannot be evaluated as it
is not a valid expression.

Functions are similar, except you have to use the parantheses when
assigning the results of the function to a variable or when using those
results in some other way.

MsgBox "Hello"

intReturn = MsgBox("Hello")

Now, MS Project could have differing rules, although I would hope not.
Hopefully, the above is at least somewhat informative to you and/or others.

Clifford Bass
 
Joined
Apr 5, 2017
Messages
1
Reaction score
0
I would like tp point out another way in which the original problem may be prompted. If the form has a text box called "dir" (referred to on the vorm vba as me.dir then the same error is flagged when the function dir() is called. I hope this helps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top