Creating a constraint in a table in the backend

[pacctono]

Hello!

I have a backend with the tables and the frontand with queries, forms, reports and link table. Now I would like to add a constraint in a table in the backend using vba:

DoCmd.RunSQL "ALTER TABLE diarios_det_2022 ADD CONSTRAINT RelacionCuenta FOREIGN KEY (cuenta) REFERENCES cuentas_2022 (cuenta);"

Please, any help, fellows

 

[June7]

Is backend Access? Why don't you just open backend and modify Relationships?

Is the foreign key field in diarios_det_2022 named cuenta? Why do you have table with a year in name - do you create a table for each year? If so, why?

Are you running this in frontend? Would have to identify filepath for backend db.

In my test, constraint was created but did not show up in Relationships window. I had to manually add tables to Relationships and link automatically displayed.

 

[pacctono]

Is backend Access? Why don't you just open backend and modify Relationships?

Is the foreign key field in diarios_det_2022 named cuenta? Why do you have table with a year in name - do you create a table for each year? If so, why?

Are you running this in frontend? Would have to identify filepath for backend db.

Is backend Access? yes Why don't you just open backend and modify Relationships? Can I do that in vba from the frontend?

Is the foreign key field in diarios_det_2022 named cuenta? yes Why do you have table with a year in name - do you create a table for each year? bad practice If so, why? I found it like that I,ll try to change it in a new version

Are you running this in frontend? yes Would have to identify filepath for backend db. : Split(CurrentDb.TableDefs("cuentas_2020").Connect, "=")(1)

 

[arnelgp]

open the Backend Exclusively and do it there.

 

[ebs17]

Application.DoCmd.RunSQL sSQL

The DoCmd object is an object of the Access application object. If nothing else is specified, it will be executed where the code is running, i.e. in the frontend. So it can't work that way.

Gültigkeitsregeln für Access Tabellen

Deklarative Gültigkeitsregeln in Access mit Validation Rules und Check Constraints.

codekabinett.com

Using VBA, constraints can only be created and removed via ADODB. So you would have to create an ADODB connection to the backend and use it to execute the SQL statement.

 

[Galaxiom]

The year being in the table name makes me think the whole need to add the constraint stems from some kind of archiving that you are expecting users to do at runtime. Wrong thinking. It is a dangerous process that should not be done routinely.

Why are you creating archive tables? If you have more data than Access backend can handle (2 GB) then you should be using an RDMS backend like SQL Server which will hold 10 GB in the free version or practically unlimited in the paid versions.

 

[June7]

ebs17, as already commented, I tested code and it works, no connection need be established. Just had to add filepath to SQL statement. Drawback noted was although constraint established, relationship not displayed in Relationships.

 

[561414]

Here's some VBA code to add constraints from VBA

Tables - Multiple Instance - Why do this?

When building relationships in the relationship window, it is possible to add multiple instances of a table. Generally they look something like this; Contacts_T_1, Contacts_T_2, Contacts_T_3., ect... Why add multiple instances of a table? Are these instances related. So for example if you have...

www.access-programmers.co.uk

EDIT
However, it's for creating tables with constraints already.
I tested this

Sub x()
DoCmd.RunSQL "ALTER TABLE diarios_det_2022 ADD CONSTRAINT RelacionCuenta FOREIGN KEY (cuenta) REFERENCES cuentas_2022 (cuenta);"
End Sub

it runs fine, it just assumes diarios_det_2022 table already has a cuenta field and cuentas_2022 table has a cuenta field as primary key

 

[ebs17]

add filepath to SQL statement

When using DDL, this is a SPECIAL design.

I don't understand the sticking to the DoCmd stuff (RunSQL, OpenQuery). DAO and ADODB are not foreign languages (for me).