asked on 5/14/2011

Run second object in vba

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

'****

Last Comment

5/18/2011

5/14/2011

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

dqmq

5/14/2011

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.

PeterBaileyUk

5/15/2011

ASKER

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.

ASKER CERTIFIED SOLUTION

Bill Ross

5/15/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Bill Ross

5/15/2011

Also, what is the opurpose of

fileDatesRs.Close

PeterBaileyUk

5/15/2011

ASKER

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 Ross

5/15/2011

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

SOLUTION

Bill Ross

5/15/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

dqmq

5/15/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

PeterBaileyUk

5/15/2011

ASKER

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")

PeterBaileyUk

5/18/2011

ASKER

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.