RElationship issue

[bruggied]

Hello,

i have the following module created to create a relation between
table '_match' with column 'match' and table '05-as2910 = WrksheetName' , column 'as2910 - teamdat'

when running i receive this error :

Creating Relations...
The database engine could not lock table '_match' because it is already in use by another person or process. (_match_match__05-as2910_as2910)
False
0 Relationships created!

this is the code:

Sub relation()
'relationship _match - team
Dim db1 As DAO.Database
Dim totalRelations As Integer
Dim teamdat As String

Set db1 = CurrentDb()
totalRelations = db.Relations.Count
If totalRelations > 0 Then
For i = totalRelations - 1 To 0 Step -1
db.Relations.Delete (db.Relations(i).Name)
Next i
Debug.Print Trim(Str(totalRelations)) + " Relationships deleted!"
End If

teamdat = Left(Mid(CustomerFile, InStrRev(CustomerFile, "\") + 4), (InStrRev((file), ".") - 4))

Debug.Print "Creating Relations..."

''==========================
''Example
'Employee Master to Employee CheckIn
Debug.Print CreateRelation("_match", "match", WrksheetName, teamdat)
''==========================

totalRelations = db.Relations.Count
Set db1 = Nothing

Debug.Print Trim(Str(totalRelations)) + " Relationships created!"
Debug.Print "Completed!"

End Sub
Private Function CreateRelation(primaryTableName As String, _
primaryFieldName As String, _
foreignTableName As String, _
foreignFieldName As String) As Boolean
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim newRelation As DAO.Relation
Dim relatingField As DAO.Field
Dim relationUniqueName As String

relationUniqueName = primaryTableName + "_" + primaryFieldName + _
"__" + foreignTableName + "_" + foreignFieldName

Set db = CurrentDb()

'Arguments for CreateRelation(): any unique name,
'primary table, related table, attributes.
Set newRelation = db.CreateRelation(relationUniqueName, _
primaryTableName, foreignTableName)
'The field from the primary table.
Set relatingField = newRelation.CreateField(primaryFieldName)
'Matching field from the related table.
relatingField.ForeignName = foreignFieldName
'Add the field to the relation's Fields collection.
newRelation.Fields.Append relatingField
'Add the relation to the database.
db.Relations.Append newRelation

Set db = Nothing

CreateRelation = True

Exit Function
ErrHandler:
Debug.Print Err.Description + " (" + relationUniqueName + ")"
CreateRelation = False
End Function

 

[jdraw]

? Why are you creating relationships via vba? Just Curious.

see Allen Browne's stuff here for DAO
http://allenbrowne.com/func-dao.html

 

[bruggied]

hello, the issue remains : The database engine could not lock table "_match' because it is allready in use by another person or process

 

[bruggied]

unable to create relationship, not an unique id..

database included

why this create relationship module, i import multiple excel files in to mutiple tables

and each table need to be linked to the main table _match _skill _ player