A
aceavl via AccessMonster.com
i have a db that every time a new Company is added to the table it generates
another table
with it's respective primary keys and adds the primary key of the first table
(to have a reference of to whom this employee belongs too)
the problem i'm having is that when i try to make the relations between the
two tables it tells me
that i can't because that table is being used, so i have to put the code into
a (module or something?) and call it (when i close the form?)
i have no idea how to do this, please HELP!
this is the code:
Private Sub AddCompany()
On Error GoTo Err_AddCompany_Click
Dim tblName As String
Dim db As Database
Dim tdfNew As TableDef
Dim fldNew1 As Field
Dim fldNew2 As Field
Dim valID As Integer
Dim strSql As String
Dim strSql1 As String
Dim strSql2 As String
'--- get the name of the new table and add _Empleados to it
tblName = (Me.RS.Value + "_Empleados")
'--- set a reference to the database
Set db = CurrentDb
'--- create the table
Set tdfNew = db.CreateTableDef(tblName)
'--- add integer field
Set fldNew2 = tdfNew.CreateField("ID_Empr", dbInteger)
'--- add autonumber field
Set fldNew1 = tdfNew.CreateField("ID", dbLong)
fldNew1.Attributes = dbAutoIncrField
'--- save the new fields
tdfNew.Fields.Append fldNew1
tdfNew.Fields.Append fldNew2
'--- save the new table design
db.TableDefs.Append tdfNew
'- refresh the tables
db.TableDefs.Refresh
'--- get the ID
valID = Me.ID.Value
'--- put the sql into a variable
strSql = "INSERT INTO [" & tblName & "](ID_Empr) VALUES (" & valID & ")"
'--- run the sql
DoCmd.RunSQL strSql
'--- set the primary key for the new table
strSql1 = "ALTER TABLE [" & tblName & "] ADD CONSTRAINT [ID] PRIMARY KEY ([ID]
)"
'--- run the sql
DoCmd.RunSQL strSql1
***---------------------------------------
'--- add a relationship between Empresa and the new table
'--- by the foreign and the primary key
strSql2 = "ALTER TABLE [" & Name & "] ADD CONSTRAINT [" & Name & "] FOREIGN
KEY (ID_Empr) REFERENCES Empresa (ID);"
'--- run the Sql
DoCmd.RunSQL strSql2
***---------------------------------------
'--- release the memory
Set fldNew1 = Nothing
Set fldNew2 = Nothing
Set tblNew = Nothing
Set db = Nothing
Exit_AddCompany_Click:
Exit Sub
Err_AddCompany_Click:
MsgBox Err.Description
Resume Exit_AddCompany_Click
End Sub
this part is the problem, everything else works fine:
***---------------------------------------
'--- add a relationship between Empresa and the new table
'--- by the foreign and the primary key
strSql2 = "ALTER TABLE [" & Name & "] ADD CONSTRAINT [" & Name & "] FOREIGN
KEY (ID_Empr) REFERENCES Empresa (ID);"
'--- run the Sql
DoCmd.RunSQL strSql2
***---------------------------------------
another table
with it's respective primary keys and adds the primary key of the first table
(to have a reference of to whom this employee belongs too)
the problem i'm having is that when i try to make the relations between the
two tables it tells me
that i can't because that table is being used, so i have to put the code into
a (module or something?) and call it (when i close the form?)
i have no idea how to do this, please HELP!
this is the code:
Private Sub AddCompany()
On Error GoTo Err_AddCompany_Click
Dim tblName As String
Dim db As Database
Dim tdfNew As TableDef
Dim fldNew1 As Field
Dim fldNew2 As Field
Dim valID As Integer
Dim strSql As String
Dim strSql1 As String
Dim strSql2 As String
'--- get the name of the new table and add _Empleados to it
tblName = (Me.RS.Value + "_Empleados")
'--- set a reference to the database
Set db = CurrentDb
'--- create the table
Set tdfNew = db.CreateTableDef(tblName)
'--- add integer field
Set fldNew2 = tdfNew.CreateField("ID_Empr", dbInteger)
'--- add autonumber field
Set fldNew1 = tdfNew.CreateField("ID", dbLong)
fldNew1.Attributes = dbAutoIncrField
'--- save the new fields
tdfNew.Fields.Append fldNew1
tdfNew.Fields.Append fldNew2
'--- save the new table design
db.TableDefs.Append tdfNew
'- refresh the tables
db.TableDefs.Refresh
'--- get the ID
valID = Me.ID.Value
'--- put the sql into a variable
strSql = "INSERT INTO [" & tblName & "](ID_Empr) VALUES (" & valID & ")"
'--- run the sql
DoCmd.RunSQL strSql
'--- set the primary key for the new table
strSql1 = "ALTER TABLE [" & tblName & "] ADD CONSTRAINT [ID] PRIMARY KEY ([ID]
)"
'--- run the sql
DoCmd.RunSQL strSql1
***---------------------------------------
'--- add a relationship between Empresa and the new table
'--- by the foreign and the primary key
strSql2 = "ALTER TABLE [" & Name & "] ADD CONSTRAINT [" & Name & "] FOREIGN
KEY (ID_Empr) REFERENCES Empresa (ID);"
'--- run the Sql
DoCmd.RunSQL strSql2
***---------------------------------------
'--- release the memory
Set fldNew1 = Nothing
Set fldNew2 = Nothing
Set tblNew = Nothing
Set db = Nothing
Exit_AddCompany_Click:
Exit Sub
Err_AddCompany_Click:
MsgBox Err.Description
Resume Exit_AddCompany_Click
End Sub
this part is the problem, everything else works fine:
***---------------------------------------
'--- add a relationship between Empresa and the new table
'--- by the foreign and the primary key
strSql2 = "ALTER TABLE [" & Name & "] ADD CONSTRAINT [" & Name & "] FOREIGN
KEY (ID_Empr) REFERENCES Empresa (ID);"
'--- run the Sql
DoCmd.RunSQL strSql2
***---------------------------------------