Creating a constraint in a table in the backend (1 Viewer)

[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).

 

[Pat Hartman]

This db is for one of the applications I sell to the public. Occasionally, I make upgrades. When the client is using the SQL Server BE, I send them a script for their DBA to run to "convert" the BE to a new version. For ACE BE's, the clients run this attached DB. It converts a ver36 BE to ver37.

The code uses both the currentdb() object as well as a named be. All of the DDL queries are in the FE. The FE copies each querydef into an sql string one at a time and then runs them against the named BE. The tables in the named BE are not linked.

To create the DDL, I used a product called SQL Examiner. It compares two SQL server databases and generates a script to convert one database to the other format. I use the script as is for my SQL server clients. For my ACE clients, I convert the script to individual DDL queries.

@pacctono I'm pretty sure that you have other problems that need to be corrected and so you probably don't actually need this type of code but I'm including it because we see this request occasionally and there are valid reasons to run code in dbA to update the schema in dbB. This is the simplest non-code solution. I could have used DAO to do this but why when I had a tool that would generate perfectly good DDL?

Just FYI, in case you don't want to open the attachment, here's a couple of examples of what DDL looks like:

DROP INDEX [tblStatestblAuditParms] on [tblAuditParms]
---
CREATE UNIQUE INDEX [UniqueIDX] ON [tblListValues]([AuditParmsID], [ListName], [ItemName])
---
ALTER TABLE [tblListValues] DROP CONSTRAINT [DF__tblListVa__ITOnl__33D4B598]
---
CREATE TABLE [tmp_tblListValues]
(
[ListValueID] COUNTER Primary Key,
[AuditParmsID] Long NOT NULL,
[ListName] text(50) NOT NULL,
[ItemName] text(50) NOT NULL,
[Seqnum] Integer NULL,
[ITOnly] Byte NOT NULL,
[UpdatedBy] text(20) NULL,
[UpdatedDT] DateTime NOT NULL,
[upsize_ts] timestamp NULL
)