Best way to move tables to a new database?

[NearImpossible]

I currently have a FE/BE database, that was created by a former coworker, that has been modified over time and the maintenance was not maintained in the best manner.

When new "Features" were added, new tables were created in the BE, new Forms, Queries and Reports were created in the FE, but when these "Features" were determined to not be needed, all he did was just delete the buttons from the FE forms, resulting in several tables, forms, queries, macros and reports that are no longer in use.

I've tried to rifle through the code on each form and remove the unneeded code and objects, but it is proving to be a daunting task, not to mention the overall FE could use a redesign.

What I would like to do is make a new database in SQL and start from scratch on a new Access FE and just import the applicable tables, including their PKs, Indexes, Relationships and default values, to the new database as i'm working on that section of the new FE so I don't run the risk of accidentally deleting any production data.

Obviously these tables will be a current snapshot at the time they are Exported/Imported, so once the new FE is completed I would then delete all tables from the new DB and do a fresh "Export/Import of just the applicable tables so all the data is current.

Any thoughts / suggestions on the best way forward?

 

[June7]

SQL is not a db platform, it is a query language used by many platforms. Do you want to migrate to MySQL or SQLServer? Have you Googled this topic?

 

[CJ_London]

suspect you will just have to grind through it but suggest start at the top and work down. You can write code to assist you, but that might take longer than just grinding through it

I presume you have already used tools such as dependencies to identify those object that are no longer referenced (but take care re tables/queries referenced in code alone which won't be identified in this way)

The top is forms/reports

so scan through looking for 'openform', 'openreport','sourceobject' which will enable you to map out how your forms/reports are interrelated - you ideally also need to capture what event these commands are located in.

here is a link to scanning through code
https://stackoverflow.com/questions/10118061/how-to-search-through-vba-code-files

you can also scan through the form and report collection looking for disabled buttons or other controls that are used for navigation - events behind these can be ignored in your code scan. At the same time, collect the sourceobject values for subform/reports

here is a link to scan through forms, reports will be much the same
https://stackoverflow.com/questions...p-through-all-forms-and-controls-on-each-form

With some tidying up, this should give you a picture of the app forms/reports in current use.

You can now use this to identify form/report recordsource and combo/listbox rowsources - and from that the underlying tables

The above is just a suggestion as to where to start - it does not cover everything. For example use of dlookups, recordsets etc. But you only need to identify something as current once to include it in your 'required' list

Edit: Just understood BE is SQL server - so you will have other areas to look at around passthrough queries and stored procedures in your code scan

 

[NearImpossible]

June7, It will be from the existing DB to a new DB on the same SQL Server.

So far i've discovered that Tasks > Exports only moves the tables and data, no Schema

Unless I didn't do it correctly, the Tasks > Generate Scripts looks like it will give me what I was looking for, but it only let me make new tables in the same database

so therefore I am looking for some guidance

 

[NearImpossible]

CJ,

I started going through each form and identifying the unneeded code, queries and tables but half way through decided that it may not be a bad idea to redesign the front end all together, which is why I was leaning towards making a new database and making a new FE starting from scratch and only copying over the applicable tables.

I guess I could just make a copy of the current FE, convert all the tables to local tables and start hacking away and redesigning. Do you know if converting linked tables to local tables will bring the schema as well?, i'm guessing not so I would have to recreate the relationships in Access.

Once I get a new FE setup, I can just delete the tables, re-link only the tables I need from SQL Server, convert queries to pass-through and then remove the unneeded tables from SQL Server.

 

[CJ_London]

Do you know if converting linked tables to local tables will bring the schema as well?, i'm guessing not so I would have to recreate the relationships in Access.

if they were access linked tables, you can import relationships (I presume that is what you mean by schema) but don't know about importing relationships from SQL server. Check the import wizard.

I was leaning towards making a new database and making a new FE starting from scratch and only copying over the applicable tables.

only you can decide that

 

[NearImpossible]

I am completely fine with redesigning the whole thing, which is I why I made this post.

I am just unsure of the best way to copy a tables from 1 DB to another DB withing the same SQL server and maintain all the relationships, indexes and default values.

 

[theDBguy]

I am completely fine with redesigning the whole thing, which is I why I made this post.

I am just unsure of the best way to copy a tables from 1 DB to another DB withing the same SQL server and maintain all the relationships, indexes and default values.

Hi. Just a wild guess but how about a backup and restore?

 

[NearImpossible]

Hi. Just a wild guess but how about a backup and restore?

I thought about that too, but that wouldn't help me in the sense of only bringing over the tables I need, vs all tables, to the new DB.

 

[theDBguy]

I thought about that too, but that wouldn't help me in the sense of only bringing over the tables I need, vs all tables, to the new DB.

True, but I was also thinking it would be easier to just delete the unwanted tables afterwards. Maybe not...

 

[CJ_London]

I am just unsure of the best way to copy a tables from 1 DB to another DB withing the same SQL server and maintain all the relationships, indexes and default values.

more a question for a sql server forum. Regret I don't have an answer except that there are scripts that can be automatically generated if you right click on the db in SSMS. I've never had to go there so not sure what else is involved.

 

[isladogs]

If you want to copy tables from one SQL Server database to another new one, why not backup the database then restore that as the source of a new database. Then delete any tables you don't need in the new database

 

[NearImpossible]

Thanks to all for your input, think I figured it via scripts on SQL server.

So far everything looks good and appears to be working as intended, so I will have to have a few users get on the my test front end and verify it works on Monday.

 

[theDBguy]

Thanks to all for your input, think I figured it via scripts on SQL server.

So far everything looks good and appears to be working as intended, so I will have to have a few users get on the my test front end and verify it works on Monday.

Hi. Glad to hear you got it sorted out. Good luck with your project.