Run second object in vba

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
Ok I have used the technique in the related question perfectly. I now need to run another db and merely changed the name of the object but it fails.

i HAVE MARKED THE CODE WHERE ASSISTANCE IS NEEDED
Ooops left caps on :(

not sure why.

it says error 7866 I dont have exclusive access

It could be becuase i am linked to a table in that external db, so if thats the case can I create the link in vba after the process has run?

Private Sub Form_Open(Cancel As Integer)
'setup vars for first frm
Dim objAccess As Object
Dim frm As Object
'setup vars for 2nd frm
Dim objAccessPMV As Object
Dim frmPMV As Object




Dim db As DAO.Database
Dim DateValue As Date





DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000

DoCmd.SetWarnings False



'Stop
    Dim mydb As Database
    Set mydb = CurrentDb
    Set fileDatesRs = mydb.OpenRecordset("fileDates", dbOpenDynaset)
    localDataFileDate1 = fileDatesRs.code44_mdb.Value
    localDataFileDate2 = fileDatesRs.Data_dictionary_mdb.Value

    networkDataFile1 = "N:\data\ABI\code44.mdb"
    networkDataFile2 = "N:\data\ABI\Data_dictionary.mdb"
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    If fs.FileExists(networkDataFile1) Then networkDataFileDate1 = FileDateTime(networkDataFile1)
    If fs.FileExists(networkDataFile2) Then networkDataFileDate2 = FileDateTime(networkDataFile2)
    
    If networkDataFileDate1 > localDataFileDate1 Or networkDataFileDate2 > localDataFileDate2 Then
        x = MsgBox("Newer data file exists. Do you wish to update ABICodes? ", vbYesNo + vbExclamation, "Newer File Exists")
        If x = vbYes Then
        'create form object and remotely run the click event
        Set objAccess = CreateObject("Access.Application")
        objAccess.OpenCurrentDatabase "N:\data\abi\AbiComparitor.mdb"
        objAccess.DoCmd.OpenForm "FrmMenu"
        Set frm = objAccess.Forms("FrmMenu")
            
            
            

            
            
            '**********************************
            DoCmd.SetWarnings False
            
            'backupdifferencetables
            Call frm.BtnBackUpDifferenceTable_Click
      
            'Delete rows from existing prev table
            DoCmd.RunSQL "DELETE *" _
            & " FROM AbiCodesPrevious;"
         
            'Populate Prev table from current Table
            DoCmd.RunSQL "INSERT INTO AbiCodesPrevious" _
            & " SELECT AbiCurrentCodes.*" _
            & " FROM AbiCurrentCodes;"

            'Delete rows from existing current table
            DoCmd.RunSQL "DELETE *" _
            & " FROM AbiCurrentCodes;"
'
                DoCmd.RunSQL "DELETE [ABICodes].* FROM ABICodes;"
       

                fileDatesRs.Edit
                fileDatesRs.code44_mdb.Value = networkDataFileDate1
                fileDatesRs.Data_dictionary_mdb.Value = networkDataFileDate2
                fileDatesRs.Update
        
            'Populate current table from local Table
            DoCmd.RunSQL "INSERT INTO AbiCurrentCodes" _
            & " SELECT Tclient.*" _
            & " FROM Tclient;"
            
            'calculate differences
            Call frm.BtnCalculateDifferencesandAppendToTables_Click
            DoCmd.SetWarnings True
        
    'populate PostMatchValidator tables
        'Delete from tClient_PMV
        DoCmd.RunSQL "DELETE *" _
        & " FROM tClient_PMV;"
        
        'Populate tClient_PMV from local Table
        DoCmd.RunSQL "INSERT INTO tClient_PMV" _
        & " SELECT Tclient.*" _
        & " FROM Tclient;"
        
        'Delete from AbiToMvris_PMV
        DoCmd.RunSQL "DELETE *" _
        & " FROM AbiToMvris_PMV;"
        
        'Populate AbiToMvris_PMV from local Table
        DoCmd.RunSQL "INSERT INTO AbiToMvris_PMV" _
        & " SELECT AbiToMvris.*" _
        & " FROM AbiToMvris;"
        
        'Delete from tClientAlias_PMV
        DoCmd.RunSQL "DELETE *" _
        & " FROM tClientAlias_PMV;"
        
        'Populate tClientAlias_PMV from local Table
        DoCmd.RunSQL "INSERT INTO tClientAlias_PMV" _
        & " SELECT tClientAlias.*" _
        & " FROM tClientAlias;"
        
        'Delete from tCWAlias_PMV
        DoCmd.RunSQL "DELETE *" _
        & " FROM tCWAlias_PMV;"
        
        'Populate tCWAlias_PMV from local Table
        DoCmd.RunSQL "INSERT INTO tCWAlias_PMV" _
        & " SELECT tCWAlias.*" _
        & " FROM tCWAlias;"
        
        'Delete from SMMT_PMV
        DoCmd.RunSQL "DELETE *" _
        & " FROM SMMT_PMV;"
        
        'Populate SMMT_PMV from local Table
        DoCmd.RunSQL "INSERT INTO SMMT_PMV" _
        & " SELECT SMMT.*" _
        & " FROM SMMT;"
        
    'populate Comparitor tables
        'Delete from AbiToMvris_comparitor
        DoCmd.RunSQL "DELETE *" _
        & " FROM AbiToMvris_comparitor;"
        
        'Populate AbiToMvris_comparitor from local Table
        DoCmd.RunSQL "INSERT INTO AbiToMvris_comparitor" _
        & " SELECT AbiToMvris.*" _
        & " FROM AbiToMvris;"
        
        'Delete from SMMT_comparitor
        DoCmd.RunSQL "DELETE *" _
        & " FROM SMMT_comparitor;"
        
        'Populate SMMT_comparitor from local Table
        DoCmd.RunSQL "INSERT INTO SMMT_comparitor" _
        & " SELECT SMMT.*" _
        & " FROM SMMT;"
        
        'Delete from FileDates_comparitor
        DoCmd.RunSQL "DELETE *" _
        & " FROM FileDates_comparitor;"
        
        'Populate FileDates_comparitor from local Table
        DoCmd.RunSQL "INSERT INTO FileDates_comparitor" _
        & " SELECT FileDates.*" _
        & " FROM FileDates;"
        
        'close first db
        objAccess.CloseCurrentDatabase
        
'******************** HELP HERE **********************************

        'now set up 2nd db and run click event
        
        Set objAccessPMV = CreateObject("Access.Application")
        'objAccessPMV.OpenCurrentDatabase "d:\data\abi\AbiPostValidator.mdb"
        objAccessPMV.OpenCurrentDatabase "N:\data\abi\AbiPostValidator.mdb"
        objAccessPMV.DoCmd.OpenForm "FrmMenu"
        Set frmPMV = objAccessPMV.Forms("FrmMenu")
        Call frmPMV.BtnGetDerivedData_Click
               
               
        'close db
        objAccessPMV.CloseCurrentDatabase
        'Set objAccess = Nothing
        'Set objAccessPMV = Nothing
               
               
        End If
    End If
    
    fileDatesRs.Close

'****

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill RossProgrammer

Commented:
Hi Peter,

This must be a nightmare to debug since the code resides in a separate form in another database.  Why not bring the code from the "Call frmPMV.BtnGetDerivedData_Click" into this procedure?  That would save a lot of overhead.

Regards,

Bill
 

Commented:
I don't think the problem is related to the linked table.  

At face value the error means someone has the database open in exclusive mode, possibly even you in another window.  In that case, the solution is to "eliminate the competition", by either waiting till they are finished or tracking down the other user and addressing the issue.  Exclusive access is one of the Access startup options.

That said, sometime that error pops up when there are no other users.  If you run out of patience with the first option, shutdown the app and attempt to delete:  N:\data\abi\AbiPostValidator.ldb.  If the delete works, then the problem is fixed. If not, then use all means available to figure out what is preventing the lock file from being deleted.



Author

Commented:
I looked for lock files but they were not there i checked the tasks to see how many instances of access were open and found only the one so no unknown instances appeared to be open.

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Programmer
Commented:
Hi Peter,

Just as a test if you open "N:\data\abi\AbiPostValidator.mdb" and the form and run it does the process work?  I'm sure you've tested that.  

What table/query is the recordsource of "FrmMenu" in "N:\data\abi\AbiPostValidator.mdb".  I would track that all the way through.  If you have Combo boxes, etc. that rely on data from the db or even another linked db within that db...

Regards,

Bill
Bill RossProgrammer

Commented:
Also, what is the opurpose of

 fileDatesRs.Close

Author

Commented:
I had a main form with subform in a db. I created a process in another db abicomparitor to see what data had changed in the clients data set by comparing two months. The postmatch validator much in the same way as the comparitor is looking for errors between my pk code and the client code where the employees have matched our data to theres. the comparitor and postmatch validator work separately fine.

I then created links to the comparitor and a results table to highlight the errors and data differences.

I then thought ok I will get the comparitor to run when a new data set is identified (this is what the recrodset is doing, building the new data). then after the comparitor has run I will create the second object application and run the post match validator. The comparitor runs fine remotely. its a shame i cannot shutdown the main db once new data has been built and run the comparitor and validator.
Bill RossProgrammer

Commented:
Hi,

Is fileDatesRs opened somewhere else prior to this procedure?  Maybe that is where the conflict lies.  I do not see where it is used in this process.  Am I missing something?

Bill  
Bill RossProgrammer
Commented:
Hi Peter,

add

 fileDatesRs.Close before  the
Set objAccessPMV = CreateObject("Access.Application")

or after you've compelted the updates as soon as you've finished with the recordset.

See if that helps.

Bill
Commented:
Open the database directly by d-clicking on:  N:\data\abi\AbiPostValidator.mdb
Now the .ldb lock file should appear
Close the database
Now the .ldb file should disappear.
Now you have confirmed noone else has the db open

If you still have the problem, try different settings (0 or 3) for "Sandbox mode" at the following registry key:

\\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\engines\SandboxMode


http://support.microsoft.com/kb/239482/en-us

Author

Commented:
cannot find the directory jet am in win 7 64

got to here in regedit

'
\\HKEY_LOCAL_MACHINE\Software\Microsoft

this failed still but better position for the rs anyway

 fileDatesRs.Close before  the
Set objAccessPMV = CreateObject("Access.Application")

Author

Commented:
couldnt get to the bottom of this so took out the calls to the other processes as they run separately fine but thank you anyway, have shared the points.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial