Backend conversion to sql server (1 Viewer)

Hi Guys,

I have a database built in MS Access so it has Access backend and Access frontend. Now we are thinking of converting Access backend to sql server. But there are few issues we are facing as below:

1. There are some tables in Access which has fields names with spaces in. So we will need to remove the spaces in the fields names as sql server doesn't accept field names with spaces.

2. If we remove the spaces in the field names then we will need to make changes in the code/queries etc where we are using field names with spaces .

It's a very long process . Is there any quick way to achieve this?

Any help will be much appreciated.

Thanks

Hi,

You could try this:

1. Rename the fields in the tables to remove the spaces.
2. Rename the tables to something like db_TableName
3. Upload the tables to SQL Server
4. Link to those tables in Access
5. Create a query for all tables to restore the original field and table names

Just a thought... Hope it helps...

What about the code that is using old field names with spaces and queries which are referring to old field names ???

Derek said:

What about the code that is using old field names with spaces and queries which are referring to old field names ???

Click to expand...

I am hoping they'll be fine too. For example, let's say you were using a recordset, then since the original name of your table is now referring to a query with field aliases with spaces, then the code should be fine. However, only testing can tell you for sure. As I said earlier, it's just a thought.

Whilst a long process, this is the ideal time to fix any issues including with naming.
To speed things up:
1. You could turn on name auto correct even if only temporarily but it won't alter any code. Then change the code manually - search & replace

2. More usefully, you could try downloading a deep search & replace utility such as V-Tools (free) or Rick Fisher's Find & Replace (if its still available)

Derek, the easiest is having removed spaces in table names in your sql server, link to these tables and then change the names of the linked files by renaming the linked files in the front end with the space. That way, no need to change any code, queries etc in the front end.

The downside is the potential problems for anyone in the future coming in cold and being confused about the data source.

SQL Server does allow names with embedded spaces although they are considered poor practice. You will run into problems if you have used SQL Server reserved words for column names since those it will reject. I'm not sure about other special characters but for future reference stick with letters, numbers, and the underscore and keep names under 33 characters. That should not cause a problem in any environment.

Although find & replace is the most efficient solution, I don't think it is available any longer so you may have to rely on NameAutoCorrect to help you. NAC is a very dangerous "feature" and most experts recommend that the standard setting be OFF. However, turning it on in a controlled situation can be useful. If it turns out that you need to use it, please post back and I'll give a more detailed explanation of steps to keep you and your app safe. It is not a straightforward process since you have to start by importing the tables so that they are all in the FE.

Try the upsizing wizard (if you are lucky enough to be using a version of Access that still contains it) or use SSMA if necessary just to see what happens. You will get a report of all errors and that will help you decide how to proceed.