Access Database
 

So I can access the Access Database using the following:

Dim myDB As ADODB.Connection
Dim myRS As ADODB.Recordset
Set myDB = New ADODB.Connection
Set myRS = New ADODB.Recordset


myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Work
Log\Work Log .mdb;USER ID=Facilities;Password=;"
myRS.Open "SELECT DISTINCT `Jamis #` FROM `Work Log` `Work Log` WHERE
status < 'Complete' or status < 'Canceled' or status < 'On Hold'",
myDB, adOpenDynamic, adLockReadOnly


and it works fine if nobody has the database open, however if someone
has the database open, I get an error. Anyway around this?

Access Database
 

I presume you have the default open mode set to 'Shared'........

Given that, and that your database is user-level secured, have you thought
of setting up another account and password for secured 'read-only' access to
the database?

Incidentally, why don't you have to reference your workgroup information
file to access your database?

Regards,
VBA Dabbler

" wrote:

So I can access the Access Database using the following:

Dim myDB As ADODB.Connection
Dim myRS As ADODB.Recordset
Set myDB = New ADODB.Connection
Set myRS = New ADODB.Recordset


myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Work
Log\Work Log .mdb;USER ID=Facilities;Password=;"
myRS.Open "SELECT DISTINCT `Jamis #` FROM `Work Log` `Work Log` WHERE
status < 'Complete' or status < 'Canceled' or status < 'On Hold'",
myDB, adOpenDynamic, adLockReadOnly


and it works fine if nobody has the database open, however if someone
has the database open, I get an error. Anyway around this?

Access Database
 

In the grander scheme of things of things no one should have the database
open. If at all possible set up the database with a front end/back end
architecture. The back end is all of the tables. The front end is all of the
forms queries and reports. The front end is linked to the back end via links
(Get External Data - Link Data). This way no one will have the database
exclusively open. There is also the added benfit of having multiple front
ends for one back end.

HTH

" wrote:

So I can access the Access Database using the following:

Dim myDB As ADODB.Connection
Dim myRS As ADODB.Recordset
Set myDB = New ADODB.Connection
Set myRS = New ADODB.Recordset


myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Work
Log\Work Log .mdb;USER ID=Facilities;Password=;"
myRS.Open "SELECT DISTINCT `Jamis #` FROM `Work Log` `Work Log` WHERE
status < 'Complete' or status < 'Canceled' or status < 'On Hold'",
myDB, adOpenDynamic, adLockReadOnly


and it works fine if nobody has the database open, however if someone
has the database open, I get an error. Anyway around this?