Changing a table in user test (1 Viewer)

ryetee · Mar 20, 2019

I've been making some changing to tables in development, adding and deleting fields changing names etc etc. I now want to copy these into the user test environment. How do I do this. I don't want to lose the data just copy the table definition if you like.
I tried using the import database and selected definition only but it creates a new tale with a 1 appended to the end. I just want the definition amending.

MajP · Mar 20, 2019

You can alter a table def in DAO or in SQL
https://docs.microsoft.com/en-us/of...ce/alter-table-statement-microsoft-access-sql

If this is a lot of alterations. Then just append the existing records into your new table.

Pat Hartman · Mar 20, 2019

This needs to be a repeatable process so creating a DDL query that alters the schema as MajP suggested is the best solution.
Copy the current BE to your test location and rename it to avoid confusion.
Link the test FE to the copy of the current BE
Run the alter query to change the structure of the table.

You will need to reproduce the three steps at the end to convert the current BE when you are ready to release your changes.

ryetee · Mar 20, 2019

MajP said:
You can alter a table def in DAO or in SQL
https://docs.microsoft.com/en-us/of...ce/alter-table-statement-microsoft-access-sql

If this is a lot of alterations. Then just append the existing records into your new table.

OK thanks. The problem I have at the moment is that as I've been developing I've made changes to several tables over the the past week or so. I can see what tables I've changed by sorting the objects into modified date order but not sure what changes I've made. I've also changed relationships that were missing.
I was hoping there was something that could take Table A from the live BE and apply the changes that was made to Table A in the dev BE. I guess I have to do it "long hand".

ryetee · Mar 20, 2019

Pat Hartman said:
This needs to be a repeatable process so creating a DDL query that alters the schema as MajP suggested is the best solution.
Copy the current BE to your test location and rename it to avoid confusion.
Link the test FE to the copy of the current BE
Run the alter query to change the structure of the table.

You will need to reproduce the three steps at the end to convert the current BE when you are ready to release your changes.

Thanks if you see my response to MajP I'm thinking that this is a long winded exercise as I firstly have to work out what changes I've made over the past week or 2. I should have probably kept track but haven't. Reading yours I get the sense I'm over complicating things again. Can I create the DDL from the development database? I thought I could use the prod BE and import the definition only. but it just creates another empty table with tablename suffixed by a 1.

theDBguy · Mar 20, 2019

Hi. If you don’t know what you’ve changed, then the safest way is to move the data, as already mentioned because you’ll run into the same issue when you’re ready to update the live database.

Cronk · Mar 21, 2019

@ ryetee, I learned the hard way a long time ago, as you are doing now, to document table changes being made in design mode so as to implement both FE changes with changes to table structures.

One way to assist you now is to copy both your production and dev tables to a new database and open both in design mode with the fields of the two tables aligned side by side. This makes it easier to identify field additions, no so for name and type changes.

Pat Hartman · Apr 6, 2019

If you have a test version of SQL server that you can play with, you can use the method I used. I have a tool called SQL Examiner by TulaSoft, LLC. http://www.sqlaccessories.com/sql-examiner/#editions

I use SSMA to convert each Access database to SQL Server. Then I use this tool to generate scrips to take v1 to v2. The DDL that is generated must be modified slightly to work with Access but it isn't difficult. Then I build an Access database to run the DDL that I generated. I came up with this method when I developed an app for a client who sold the app. I needed to support the app as it was installed on systems I had no control over except to specify minimum Office versions and SQL Server versions. When the client's client wanted to upgrade to a new version, I would send them the Access app that did the conversion.

Changing a table in user test (1 Viewer)

ryetee

Registered User.

MajP

You've got your good things, and you've got mine.

Pat Hartman

Super Moderator

ryetee

Registered User.

ryetee

Registered User.

theDBguy

I’m here to help

Cronk

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread