Can't open a Database for a Recordset

[Guest]

When I execute the following code, everything runs fine.

Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim dbNet As DAO.Database
Dim tdf As TableDef
Dim SourceFile As String
Dim LinkedTable As String
Dim CurrentPath As String
Dim prp As Property
Dim strName As String
Dim intPtr As Integer
Dim Path As String
Dim chrCurrentErrorVersion
Dim rs As DAO.Recordset
Dim strSourceFile As String
Dim strDestFile As String
Dim dapObject As AccessObject
Dim lngResult As String

Set wrk = CreateWorkspace("", "admin", "", dbUseJet)
Set dbNet =
wrk.OpenDatabase("\\Cpnvfs04\DEPTS\AccessFiles\Accuracy\AccuracyData.mdb")
Set rs = dbNet.OpenRecordset("tblDataUpdate", dbOpenDynaset)

Here is where I do some stuff…………

rs.Close
Set rs = Nothing
dbNet.Close
Set dbNet = Nothing
wrk.Close
Set wrk = Nothing


If I then try to make any changes to Forms, Reports, Modules etc. I get the
error message:

“Microsoft Access can’t save design changes or save to a new database object
because another user has the file open.â€

The file is on my “C†drive and I am the only one using it.

If I comment out the above lines, the rest of the code runs and I can make
any changes I want.

Can someone please show me how to open a database for a recordset and not
have this issue?

Thanks in advance.

 

[Douglas J. Steele]

Are you saying that \\Cpnvfs04\DEPTS\AccessFiles\Accuracy\AccuracyData.mdb
corresponds to a location on your C: drive?

From where are you executing this code? If it's inside of AccuracyData, then
you're the "other user"!

 

[Tim Ferguson]

Can someone please show me how to open a database for a recordset and
not have this issue?

I am not completely clear what you are trying to achieve.

Is the AccuracyData.mdb the same as the CurrentDB() or different? If
different, which database contains the 'changes to Forms, Reports and
Modules'?

Ways to avoid doing what you are doing at the moment:

You can make changes to the tblDataUpdate without explicitly opening a new
workspace and database vis

UPDATE tblDataUpdate
IN "\\Cpnvfs04\DEPTS\AccessFiles\Accuracy\AccuracyData.mdb"
SET SomeField = NewValue
WHERE SomeCriterion = TRUE;


There is lots on the IN clause in the help files.


Is there any reason you can't open the database in the current workspace?

Set myNewDB = Application.OpenDatabase(etc, etc)

Open the database in multiuser mode rather than exclusive (instead of
relying on your default startup options)

Set myNewDB = newWorkspace.OpenDatabase(etc, False, False)

Hope that helps


Tim F

 

[Guest]

Are you saying that \\Cpnvfs04\DEPTS\AccessFiles\Accuracy\AccuracyData.mdb
corresponds to a location on your C: drive?

From where are you executing this code? If it's inside of AccuracyData, then
you're the "other user"!

 

[Guest]

No, that file is on our lan, the file I have the code in is on my "C" drive.