@burrina Both FE and BE are on local drive (no external access possible). Can't delete .ldb file while I have my database open.
@John_G I kind of have BE and FE open at the same time - I use
OpenDatabase(db location)
in my code and then close it at the code's end. I don't change data structure, however, only data itself. At the FE start I use
to copy 2 tables from BE to FE but I don't suppose it might be meaningful.. I even checked it by disabling this part of code but I'm getting the same error..
Below is my code, maybe you'll be able to find the error I'm overlooking.. I get the error when i run "MainScript" (it calls other functions and subs during its performance)
Code:
Public Function dependentSteps(product As Long, step As Long) As Long()'zwraca tablice wszystkich krokow zaleznych, ktore spelniaja warunek:
'- nie sa jeszcze ZAKONCZONE
'-nie sa jeszcze AKTYWNE
'-sa zalezne od kroku "step"
Dim chosenSteps() As Long
Dim rs As DAO.Recordset
Dim index As Long
Dim db As DAO.Database
ReDim chosenSteps(1) As Long
Set db = OpenDatabase(backEndPath)
Set rs = db.OpenRecordset("SELECT * FROM tbProductSteps WHERE productId = " & product & " AND isFinished = False AND isActive = False")
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
If isDependent(step, rs.Fields("stepId")) Then
index = UBound(chosenSteps)
ReDim Preserve chosenSteps(index + 1)
chosenSteps(index + 1) = rs.Fields("stepId")
End If
rs.MoveNext
Loop
End If
dependentSteps = chosenSteps()
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function
Public Function superiorSteps(product As Long, step As Long) As Long()
'zwraca tablice wszystkich krokow nadrzednych, ktore spelniaja warunek:
'- nie sa jeszcze ZAKONCZONE
'-nie sa jeszcze AKTYWNE
'-sa nadrzedne do kroku "step"
Dim chosenSteps() As Long
Dim rs As DAO.Recordset
Dim index As Long
Dim db As DAO.Database
ReDim chosenSteps(1) As Long
Set db = OpenDatabase(backEndPath)
Set rs = db.OpenRecordset("SELECT * FROM tbProductSteps WHERE productId = " & product & " AND isFinished = False AND isActive = False")
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
If isDependent(rs.Fields("stepId"), step) Then
index = UBound(chosenSteps)
ReDim Preserve chosenSteps(index + 1)
chosenSteps(index + 1) = rs.Fields("stepId")
End If
rs.MoveNext
Loop
End If
superiorSteps = chosenSteps()
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function
Public Function isDependent(SuperiorStep As Long, InteriorStep As Long) As Boolean
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM tbStepDependenciesBp WHERE Step = " & InteriorStep & " AND dependentOfStep = " & SuperiorStep)
If rs.EOF Then
isDependent = False
Else
isDependent = True
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
Public Function isExclDependent(product As Long, SuperiorStep As Long, InteriorStep As Long) As Boolean
Dim i As Long
Dim counter As Long
Dim checkSteps() As Long
counter = 0
checkSteps = superiorSteps(product, InteriorStep)
For i = 2 To UBound(checkSteps)
'sprawdz wszystkie kroki nadrzedne
If Not checkSteps(i) = SuperiorStep Then
counter = counter + 1
End If
Next i
If counter = 0 Then
isExclDependent = True
Else
isExclDependent = False
End If
End Function
Sub mainScript()
Dim i As Integer
Dim product As Long
Dim step As Long
Dim str As String
Dim time1 As Date
Dim time2 As Date
Dim chosen() As Long
Dim superiors() As Long
time1 = Time
backEndName = "npdmanager_be.accdb"
backEndPath = "C:\Users\robert.roszak\Desktop\Projekty zawodowe\Access\NPDs\" & backEndName
step = 6
product = 25
chosen = dependentSteps(product, step)
str = "Możesz potwierdzić kroki "
For i = 2 To UBound(chosen)
superiors = superiorSteps(product, chosen(i))
For n = 2 To UBound(superiors)
If isExclDependent(product, superiors(n), chosen(i)) Then
str = str & ", " & chosen(i)
End If
Next n
Next i
time2 = Time
MsgBox str & vbNewLine & "Koniec w " & Format(time2 - time1, "Hh:Nn:Ss")
End Sub
Windows 7 64bit 
Access 2010 32bit
Reply With Quote
Thanks for your input, I've changed it to linked table and now it works painless (and twice as fast!)
