Run-time error '3045'

[Kan D.]

1. I'm using the DCount function to access a value in a LINKED TABLE.

2. I have READ ONLY access to the database which the linked table is linked
to.

3. If the database that the table is linked to is currently open on another
machine, and when the line of code that uses the DCount function executes, I
get the following error message:

Run-time error '3045':

Could not use '\\myPath\myDatabase.mdb'; file already in use.


Does anybody know a way around this? Do I have to do the following to get a
Count of the records I specify in the criteria parameter of the DCount
function, or can somebody think of a better solution to solving Run-time
error '3045'?
-----
Dim db as Database, rs as Recordset
Dim myCounter as integer

Set db = CurentDb

Set rs = db.OpenRecordset ("SELECT * FROM MyTable WHERE MyField = '" &
myCriteria & "'")

With rs

If .RecordCount > 0 then

.MoveLast
.MoveFirst

Do Until .EOF

myCounter = myCounter + 1

.MoveNext
Loop

End If

End With

Set rs = Nothing
set db = Nothing
-----
Do I have to do the above code to get a Count of the records I specify in
the criteria parameter of the DCount function or can somebody think of a
better solution to solving Run-time error '3045'?


Please Help,

Thanks ---- Kan

 

[Jeff L]

How about:
Select Count(*)

From MyTable

Where MyField = MyCriteria

Hope that helps!

 

[Kan D.]

Thank you, Jeff...

but will you specify the exact syntax for this?

Thanks,
Kan

 

[Jeff L]

I did. That's how you would write it. You would substitute MyTable
with your table name, MyField with the field you wish to count, and
MyCriteria with whatever you want the criteria to be. Sorry if I
missed what you're asking.

 

[Kan D.]

I meant, where do you place those statements.... Like this?:

set rs = db.OpenRecordset("SELECT Count(*) FROM MyTable WHERE [My field] =
'" & myVariable & "'")

??

Kan

 

[Jeff L]

Yup, looks good to me. You will need to give the Count(*) a name so
you can refer to it, SELECT Count(*) As MyCount From......
If MyVariable is a field on a form, you'll need to put Me.YourFieldName
there.

I meant, where do you place those statements.... Like this?:

set rs = db.OpenRecordset("SELECT Count(*) FROM MyTable WHERE [My field] =
'" & myVariable & "'")

??

Kan

I did. That's how you would write it. You would substitute MyTable
with your table name, MyField with the field you wish to count, and
MyCriteria with whatever you want the criteria to be. Sorry if I
missed what you're asking.

 

[strive4peace]

Hi Kan,

you can do this:

'~~~~~~~~~~~~~~~~~
myCounter = 0

Set rs = db.OpenRecordset ( _
"SELECT * FROM MyTable WHERE MyField = '" _
myCriteria & "'")

if not rs.eof then
rs.movelast
myCounter = rs.recordcount
end if

'then close and release object variables
'be sure to close the recordset too since you OPENed it...

rs.close

set rs = nothing
set db = nothing

'~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*