data migration from Access to sql server (1 Viewer)

[Derek]

Hi Guys,

I got the database built in MS Access so both front-end and back-end are in MS Access . Now we have to migrate data from MS Access to sql server so we will be having MS Access frontend and sql server backend.

I am facing few issues in this as below:

1. In MS access back-end, there are few tables which has spaces in the field names. How can we migrate those tables ?

2. Is it good to change the data structure first before migration so basically changing the field names (taking off the spaces from the field names) or can we migrate tables to SQL server and then change the data structure?

3. Do we need to change data structure or can we just leave as it is ?

4. If I need to change the table structure then it requires a lot of changes in queries forms and reports so what is the best way to achieve this?

Any help will be much appreciated .

Thanks

 

[Minty]

SQL Server is much fussier about naming of things, so you will have to rename certain things. Spaces in Table names is a no no , as is any table or field beginning with a number. Special characters are deprecated to a point as well.

You can get some tools to help you with your migration. MzTools and Vtools both have deep search and replace functionality.

Another often overlooked method , which needs to be used with care is the Name AutoCorrect in built in Access.

Take a back up of your database. In a copy of your front end Import / change your tables to local tables. (Access will do this for you from memory)
Switch Name AutoCorrect on (Assuming you have it switched off) , change one of your table names, let it do it's thing. Compact and repair. Switch it back off, see if things have broken.

If this works for you make the changes in Access then do the migration.

 

[deletedT]

Watch this.

https://youtu.be/Qlz1vQXCd9Y

You may want to watch part 5 too.

 

[bastanu]

Actually SQL server will allow spaces in table names, so I would say try to upsize the back-end first and see what you get before spending time trying to rename everything and consider it a good lesson on naming conventions for the future .

Cheers,
Vlad