Converting from access to SQL

[ahhuang]

I was currently upgrading my program from usage of MS Access to MS SQL, however while trying to convert all reports to link to MS SQL, i run to the following problem:

In my Access DB, my tables & fields are named in the way that there are spaces in between. When i test to convert 1 report, crystal is unable to recognize my field names and table names. crystal is unable to append "[" and "]" into my table names and fields. Moreover those names do have "SYS" or "TYPE" words.
I noe the longest solution is to rename my table fields n names , then change my coding and relink the crystal report. I hate to do so because I have over 100 reports and 80 over tables...
Can anyone provide me with the shortest way to do so?

 

[synapsevampire]

Are you saying that the new column names in SQL Server.

It's unfortunate that you've inherited this sort of naming nightmare...

If you're going to maintain the current naming in SQL Server <full body shudder>, one solution would be to write a script that would create a View from every table, and strip out the superfluous and offending naming conditions/characters.

Now you can repoint your reports to those Views, or perhaps even rename the table to something prefixed with something like: crap_name__<the name>, and name the Views what the tables used to be named.

-k