Undefined Function "DIR" in expression

[Ronald W. Roberts]

I'm not sure where this routine came from,
but here is the problem. On certian computers
this error occurs, but it does not on others.
The error is: "Undefined Function "DIR" in expression".

The form open event calls the CheckandRepairLinks
without a varable being passed it it.

CheckandRepairLinks

The instruction that fails is the Set instruction
pointing to the DIR instruction.

Set RecordSetOfLinks = dbs.OpenRecordset(LinksToPathsSQL)

I really don't know what to do with this because it
is saying it can't find the DOS command "DIR'. In other
forms I have the DIR command and I don't get the error.
Also, I checked and there are no missing references.

Below is a snapshot of the Checkand RepairLinks procedure.

Private Sub CheckandRepairLinks(Optional ByRef dbs As
Database)

' An SQL String To Return a List of Linked DataBases Files
' which cannot be found
Const LinksToPathsSQL As String = _
"SELECT TOP 1 CStr(DataBase) AS DatabaseFile " _

& "FROM MSysObjects " _
& "WHERE Connect Is Null " _
& "AND Left(Name,1) <> '~' " _
& "AND Type = 6 " _
& "AND Len(Dir(CStr(DataBase))) = 0 " _
& "ORDER BY CStr(DataBase);"

' Message to be sent on Cancel or Exit being chosen from
File Dialog Box
Const FileNotChosenMessage As String = _
"No File was Chosen!" _
& vbCrLf _
& "Click OK to Continue " _
& vbCrLf _
& "Click Cancel to Exit Link Checking"

Dim DbsWasPassed As Boolean
Dim NameNotIn As String
Dim RecordSetOfLinks As Recordset
Dim OldPath As String
Dim ButtonSelected As Long
Dim NewPath As String
Dim TableDefinition As TableDef

' display relinking message
'lblVerifyingLocationofLinkedTables.Visible = True
DoEvents

If dbs Is Nothing Then
Set dbs = DBEngine(0)(0)
Else
' note if the DataBase was passed
DbsWasPassed = True
End If

' Dummy NameNotIn
NameNotIn = "'" & Chr(255) & "'"

' refresh the tabledefs collection
dbs.TableDefs.Refresh

Do

' Get the first linked lost mdb
Set RecordSetOfLinks = Nothing
' MsgBox LinksToPathsSQL, vbCritical, "Link To Path
SQL"
Set RecordSetOfLinks =
dbs.OpenRecordset(LinksToPathsSQL)

-- Cut
Thanks,
Ron
--
Ronald W. Roberts
Roberts Communication
rw*******************@robcom.com
To reply remove "_at_robcom_dot_com"

[Lyle Fairfield]

"Ronald W. Roberts" <rw*@robcom.com> wrote in
news:3F***************@robcom.com:

I'm not sure where this routine came from,
but here is the problem. On certian computers
this error occurs, but it does not on others.
The error is: "Undefined Function "DIR" in expression".

The form open event calls the CheckandRepairLinks
without a varable being passed it it.

CheckandRepairLinks

The instruction that fails is the Set instruction
pointing to the DIR instruction.

Set RecordSetOfLinks = dbs.OpenRecordset(LinksToPathsSQL)

I really don't know what to do with this because it
is saying it can't find the DOS command "DIR'. In other
forms I have the DIR command and I don't get the error.
Also, I checked and there are no missing references.

Below is a snapshot of the Checkand RepairLinks procedure.

Private Sub CheckandRepairLinks(Optional ByRef dbs As
Database)

' An SQL String To Return a List of Linked DataBases Files
' which cannot be found
Const LinksToPathsSQL As String = _
"SELECT TOP 1 CStr(DataBase) AS DatabaseFile " _

& "FROM MSysObjects " _
& "WHERE Connect Is Null " _
& "AND Left(Name,1) <> '~' " _
& "AND Type = 6 " _
& "AND Len(Dir(CStr(DataBase))) = 0 " _
& "ORDER BY CStr(DataBase);"

' Message to be sent on Cancel or Exit being chosen from
File Dialog Box
Const FileNotChosenMessage As String = _
"No File was Chosen!" _
& vbCrLf _
& "Click OK to Continue " _
& vbCrLf _
& "Click Cancel to Exit Link Checking"

Dim DbsWasPassed As Boolean
Dim NameNotIn As String
Dim RecordSetOfLinks As Recordset
Dim OldPath As String
Dim ButtonSelected As Long
Dim NewPath As String
Dim TableDefinition As TableDef

' display relinking message
'lblVerifyingLocationofLinkedTables.Visible = True
DoEvents

If dbs Is Nothing Then
Set dbs = DBEngine(0)(0)
Else
' note if the DataBase was passed
DbsWasPassed = True
End If

' Dummy NameNotIn
NameNotIn = "'" & Chr(255) & "'"

' refresh the tabledefs collection
dbs.TableDefs.Refresh

Do

' Get the first linked lost mdb
Set RecordSetOfLinks = Nothing
' MsgBox LinksToPathsSQL, vbCritical, "Link To Path
SQL"
Set RecordSetOfLinks =
dbs.OpenRecordset(LinksToPathsSQL)

-- Cut
Thanks,
Ron
--
Ronald W. Roberts
Roberts Communication
rw*******************@robcom.com
To reply remove "_at_robcom_dot_com"

Perhaps, you have a missing or broken reference on those machines where
"Dir" is raising an error?

The origin of the code ... do you think it might be your responsibility to
find out what it is? ... and as a courtesy credit the author in your code
with a comment?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Ronald W. Roberts]

Lyle Fairfield wrote:

"Ronald W. Roberts" <rw*@robcom.com> wrote in
news:3F***************@robcom.com:

I'm not sure where this routine came from,
but here is the problem. On certian computers
this error occurs, but it does not on others.
The error is: "Undefined Function "DIR" in expression".

The form open event calls the CheckandRepairLinks
without a varable being passed it it.

CheckandRepairLinks

The instruction that fails is the Set instruction
pointing to the DIR instruction.

Set RecordSetOfLinks = dbs.OpenRecordset(LinksToPathsSQL)

I really don't know what to do with this because it
is saying it can't find the DOS command "DIR'. In other
forms I have the DIR command and I don't get the error.
Also, I checked and there are no missing references.

Below is a snapshot of the Checkand RepairLinks procedure.

Private Sub CheckandRepairLinks(Optional ByRef dbs As
Database)

' An SQL String To Return a List of Linked DataBases Files
' which cannot be found
Const LinksToPathsSQL As String = _
"SELECT TOP 1 CStr(DataBase) AS DatabaseFile " _

& "FROM MSysObjects " _
& "WHERE Connect Is Null " _
& "AND Left(Name,1) <> '~' " _
& "AND Type = 6 " _
& "AND Len(Dir(CStr(DataBase))) = 0 " _
& "ORDER BY CStr(DataBase);"

' Message to be sent on Cancel or Exit being chosen from
File Dialog Box
Const FileNotChosenMessage As String = _
"No File was Chosen!" _
& vbCrLf _
& "Click OK to Continue " _
& vbCrLf _
& "Click Cancel to Exit Link Checking"

Dim DbsWasPassed As Boolean
Dim NameNotIn As String
Dim RecordSetOfLinks As Recordset
Dim OldPath As String
Dim ButtonSelected As Long
Dim NewPath As String
Dim TableDefinition As TableDef

' display relinking message
'lblVerifyingLocationofLinkedTables.Visible = True
DoEvents

If dbs Is Nothing Then
Set dbs = DBEngine(0)(0)
Else
' note if the DataBase was passed
DbsWasPassed = True
End If

' Dummy NameNotIn
NameNotIn = "'" & Chr(255) & "'"

' refresh the tabledefs collection
dbs.TableDefs.Refresh

Do

' Get the first linked lost mdb
Set RecordSetOfLinks = Nothing
' MsgBox LinksToPathsSQL, vbCritical, "Link To Path
SQL"
Set RecordSetOfLinks =
dbs.OpenRecordset(LinksToPathsSQL)

-- Cut
Thanks,
Ron
--
Ronald W. Roberts
Roberts Communication
rw*******************@robcom.com
To reply remove "_at_robcom_dot_com"

Perhaps, you have a missing or broken reference on those machines where
"Dir" is raising an error?

The origin of the code ... do you think it might be your responsibility to
find out what it is? ... and as a courtesy credit the author in your code
with a comment?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

It's not a broken reference, that has already bee checked.

Ron
--
Ronald W. Roberts
Roberts Communication
rw*******************@robcom.com
To reply remove "_at_robcom_dot_com"

[Tom Wickerath]

Hi Ronald,

I am able to reproduce the error message you show if I have the registry setting for the
sandbox mode set to either 1 or 3. If the setting is at the default value of 2, I do not
generate this error.

Microsoft Jet 4.0 Expression Can Execute Unsafe Visual Basic for Applications Functions
http://support.microsoft.com/default.aspx?kbid=294698

At the end of your example, you indicated: -- Cut
Would you be so kind as to supply the rest of the code. I am very much interested in
receiving a sample database with this functionality.

Tom
____________________________________________

"Ronald W. Roberts" <rw*@robcom.com> wrote in message news:3F***************@robcom.com...

I'm not sure where this routine came from,
but here is the problem. On certian computers
this error occurs, but it does not on others.
The error is: "Undefined Function "DIR" in expression".

The form open event calls the CheckandRepairLinks
without a varable being passed it it.

CheckandRepairLinks

The instruction that fails is the Set instruction
pointing to the DIR instruction.

Set RecordSetOfLinks = dbs.OpenRecordset(LinksToPathsSQL)

I really don't know what to do with this because it
is saying it can't find the DOS command "DIR'. In other
forms I have the DIR command and I don't get the error.
Also, I checked and there are no missing references.

Below is a snapshot of the Checkand RepairLinks procedure.

Private Sub CheckandRepairLinks(Optional ByRef dbs As
Database)

' An SQL String To Return a List of Linked DataBases Files
' which cannot be found
Const LinksToPathsSQL As String = _
"SELECT TOP 1 CStr(DataBase) AS DatabaseFile " _

& "FROM MSysObjects " _
& "WHERE Connect Is Null " _
& "AND Left(Name,1) <> '~' " _
& "AND Type = 6 " _
& "AND Len(Dir(CStr(DataBase))) = 0 " _
& "ORDER BY CStr(DataBase);"

' Message to be sent on Cancel or Exit being chosen from
File Dialog Box
Const FileNotChosenMessage As String = _
"No File was Chosen!" _
& vbCrLf _
& "Click OK to Continue " _
& vbCrLf _
& "Click Cancel to Exit Link Checking"

Dim DbsWasPassed As Boolean
Dim NameNotIn As String
Dim RecordSetOfLinks As Recordset
Dim OldPath As String
Dim ButtonSelected As Long
Dim NewPath As String
Dim TableDefinition As TableDef

' display relinking message
'lblVerifyingLocationofLinkedTables.Visible = True
DoEvents

If dbs Is Nothing Then
Set dbs = DBEngine(0)(0)
Else
' note if the DataBase was passed
DbsWasPassed = True
End If

' Dummy NameNotIn
NameNotIn = "'" & Chr(255) & "'"

' refresh the tabledefs collection
dbs.TableDefs.Refresh

Do

' Get the first linked lost mdb
Set RecordSetOfLinks = Nothing
' MsgBox LinksToPathsSQL, vbCritical, "Link To Path
SQL"
Set RecordSetOfLinks =
dbs.OpenRecordset(LinksToPathsSQL)

-- Cut
Thanks,
Ron
--
Ronald W. Roberts
Roberts Communication
rw*******************@robcom.com
To reply remove "_at_robcom_dot_com"