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
)