ADO Error in Access calling from Excel

[omsoft]

I have the following code. I get an error as conn.open line.
This happens on various machines which run the app. This code is invoked
from an Excel front-end and gets data from Access back-end which is
referenced by \\server\folder.
Any help would be greatly appreciated.
The error is as below.
Error **************
Run-time error -2147467259 (80004005)
Cannot open database ''. It may not be a database that your application
recognizes, or the file may be corrupt.

Code ***************
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn, strDB, strSQL, strData, strIcao, strFbo As String
strDB = rulesPath
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";User Id=admin;Password=;"
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open (strConn)
conn.CursorLocation = adUseClient
rs.Open strSQL, conn, 1, 3
strSQL = "SELECT * FROM tbl_Volumes "
rs.Open strSQL, conn, 1, 3
histIcaoQty = 0
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
histQty = histQty + rs("Volume").Value
rs.MoveNext
Wend
Else
histQty = 0
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

 

[Douglas J. Steele]

Are you certain that rulesPath contains the full path to the MDB file?

Incidentally, your declaration statement isn't doing what you probably think
it is.

Dim strConn, strDB, strSQL, strData, strIcao, strFbo As String

declares strConn, strDB, strSQL, strData and strIcao as Variant, and only
strFbo As String. You can't "short circuit" declarations in VBA. You must
repeat the type for each declaration:

Dim strConn As String , strDB As String , strSQL As String
Dim strData As String , strIcao As String , strFbo As String

 

[omsoft]

Thanks Doug, Are you serious? I can not declare them all at once?

I am not a VBA Guru, but always thought it as a basic syntax.

 

[Douglas J. Steele]

Yes, I am serious.

Many other languages have the same restriction.

 

[omsoft]

Thanks Doug, but the thing is that the error does not happen when I run it
with both client and server on my machine (front and back end). Only happens
when it is running so that multiple users are accessing it concurrenntly with
back-end on server and front-end on each client. I have a feeling that it is
db or table or record lock issue. But not sure what or how to fix it.
Front-end is an app in Excel and back-end is 2-3 databases in Access.

Thanks.

 

[Douglas J. Steele]

Hmm. It's possible that you need to use a mapped drive, but I'd be
surprised.

 

[David W. Fenton]

It's possible that you need to use a mapped drive, but I'd be
surprised.

Access never needs a mapped drive. Ever. Other applications might,
but that's because they are incredibly out-of-date in their design
(i.e., they are built on assumptions that were valid in, say, 1993,
and that have changed massively since then).