Prev: I am using VBA, how do you test for new record in a form
Next: Date picker not updating text box in A2007
From: Jonathan Wood on 5 Mar 2010 01:54
Greetings,

I've written some code to create a relationship between table A and table B.
The code looks something like this:

Set rel = db.CreateRelation("A_B")
rel.Table = "A"
rel.ForeignTable = "B"
Set fld = rel.CreateField("AID")
fld.ForeignName = "BID"
rel.Fields.Append fld
db.Relations.Append rel

This code works just fine except that a few lines later, I use DLookup() to
find a row in table B, but I then get an error that table B is exclusively
locked and cannot be accessed.

Can anyone clarify why table B has been locked and how to unlock it?

Thanks.

Jon


From: Tom van Stiphout on 5 Mar 2010 09:04
On Thu, 4 Mar 2010 23:54:17 -0700, "Jonathan Wood"
<jwood(a)softcircuits.com> wrote:

You may need to add:
db.Tabledefs.Refresh

You know you can create relationships at design time as well, right?

-Tom.
Microsoft Access MVP


>Greetings,
>
>I've written some code to create a relationship between table A and table B.
>The code looks something like this:
>
>Set rel = db.CreateRelation("A_B")
>rel.Table = "A"
>rel.ForeignTable = "B"
>Set fld = rel.CreateField("AID")
>fld.ForeignName = "BID"
>rel.Fields.Append fld
>db.Relations.Append rel
>
>This code works just fine except that a few lines later, I use DLookup() to
>find a row in table B, but I then get an error that table B is exclusively
>locked and cannot be accessed.
>
>Can anyone clarify why table B has been locked and how to unlock it?
>
>Thanks.
>
>Jon
>
From: Jonathan Wood on 5 Mar 2010 09:49
Tom van Stiphout wrote:

> You may need to add:
> db.Tabledefs.Refresh

I tried that before the offending code but still get the error: Run-time
error '3008': The table 'B' is already opened exclusively by another user,
or it is already open through the user interface and cannot be manipulated
programmatically.

The table is not opened in Access.

> You know you can create relationships at design time as well, right?

Yes, of course.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

>>Greetings,
>>
>>I've written some code to create a relationship between table A and table
>>B.
>>The code looks something like this:
>>
>>Set rel = db.CreateRelation("A_B")
>>rel.Table = "A"
>>rel.ForeignTable = "B"
>>Set fld = rel.CreateField("AID")
>>fld.ForeignName = "BID"
>>rel.Fields.Append fld
>>db.Relations.Append rel
>>
>>This code works just fine except that a few lines later, I use DLookup()
>>to
>>find a row in table B, but I then get an error that table B is exclusively
>>locked and cannot be accessed.
>>
>>Can anyone clarify why table B has been locked and how to unlock it?
>>
>>Thanks.
>>
>>Jon
>>
From: Tom van Stiphout on 5 Mar 2010 22:19
On Fri, 5 Mar 2010 07:49:32 -0700, "Jonathan Wood"
<jwood(a)softcircuits.com> wrote:

OK, this peeks my interest. Can you email me a sample program,
stripped down to the bare essentials?
My spam trap should be easily avoidable by humans.

-Tom.
Microsoft Access MVP


>Tom van Stiphout wrote:
>
>> You may need to add:
>> db.Tabledefs.Refresh
>
>I tried that before the offending code but still get the error: Run-time
>error '3008': The table 'B' is already opened exclusively by another user,
>or it is already open through the user interface and cannot be manipulated
>programmatically.
>
>The table is not opened in Access.
>
>> You know you can create relationships at design time as well, right?
>
>Yes, of course.
From: Paul Shapiro on 6 Mar 2010 09:09
"Jonathan Wood" <jwood(a)softcircuits.com> wrote in message
news:e0uUwCDvKHA.6124(a)TK2MSFTNGP04.phx.gbl...
> I've written some code to create a relationship between table A and table
> B. The code looks something like this:
>
> Set rel = db.CreateRelation("A_B")
> rel.Table = "A"
> rel.ForeignTable = "B"
> Set fld = rel.CreateField("AID")
> fld.ForeignName = "BID"
> rel.Fields.Append fld
> db.Relations.Append rel
>
> This code works just fine except that a few lines later, I use DLookup()
> to find a row in table B, but I then get an error that table B is
> exclusively locked and cannot be accessed.

Here is the function I use for creating new relationships. I've always been
able to continue working in the db after running this code.

Public Function pjsCreateRelationship( _
dbData As DAO.Database, _
strRelationName As String, _
strParentTable As String, _
strChildTable As String, _
astrFieldNamesPrimary() As String, _
astrFieldNamesForeign() As String, _
Optional fCascadeDelete As Boolean = False, _
Optional fCascadeUpdate As Boolean = False _
) As Boolean
On Error GoTo ErrorHandler
Dim fSuccess As Boolean, relTemp As DAO.Relation, _
lngLoop As Long, lngAttributes As Long

'Make sure we were passed compatible field name arrays
If LBound(astrFieldNamesPrimary) = 0 And LBound(astrFieldNamesForeign) =
0 _
And UBound(astrFieldNamesPrimary) = UBound(astrFieldNamesForeign) Then
fSuccess = True 'Assume we succeed
'See if relationship name already exists
Set relTemp = dbData.Relations(strRelationName)
Else 'Bad calling parameters
Err.Raise Number:=9999, Description:="Programmer Error: Invalid
calling parameters"
End If

If relTemp Is Nothing Then 'Return here from error handler
With dbData
lngAttributes = IIf(fCascadeDelete, dbRelationDeleteCascade, 0)
+ _
IIf(fCascadeUpdate, dbRelationUpdateCascade, 0)
Set relTemp = .CreateRelation(strRelationName, strParentTable,
strChildTable, lngAttributes)

With relTemp
For lngLoop = 0 To UBound(astrFieldNamesPrimary)
.Fields.Append
..CreateField(astrFieldNamesPrimary(lngLoop))
.Fields(lngLoop).ForeignName =
astrFieldNamesForeign(lngLoop)
Next lngLoop
End With
.Relations.Append relTemp
.Relations.Refresh
End With
CurrentDb.Relations.Refresh
Else 'Could verify the existing relationship has the same attributes
and fields
End If

ExitHandler:
On Error Resume Next
pjsCreateRelationship = fSuccess
Set relTemp = Nothing
Exit Function

ErrorHandler:
Select Case Err.Number
Case 3265 'Name doesn't exist in this collection
Resume Next
Case Else
'Your error handling code
fSuccess = False
End Select
Resume ExitHandler
Resume
End Function


 |  Next  |  Last
Pages: 1 2
Prev: I am using VBA, how do you test for new record in a form
Next: Date picker not updating text box in A2007