Big problem with duplicate field names when converting Access prog to work with SQL Server

From: Andrew Cutforth (andrewc_at_ajcsoft.com)
Date: 01/20/04 

Date: Tue, 20 Jan 2004 17:19:37 -0000

I am converting a large application that uses an Access database via ADO to
work with MS SQL Server also.

There are many places where it does "select table1.*, table2.* etc"

With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".

I have just discovered to my horror that SQL Server will not qualify the
names for you and returns these field names as "description" and
"description1".

Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?

I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.

Thanks for any help

Relevant Pages

  • Transmitting/Receiving Data via MS Access/ADO
    ... Can someone give me a brief primer on data encrytion/security when ... transmitting data between SQL Server and an MS Access database via ADO. ...
    (microsoft.public.sqlserver.security)
  • Re: tool to re-reference forms after usizing?
    ... Speed Ferret (which I hate to admit that I've never been able to get to ... tblCustomerAddress, remember that changing tblCustomer to something else ... >>> I am in the process of upsizing an access database to sql server. ...
    (microsoft.public.access.conversion)
  • Re: ODBC parameters and system resources
    ... Install MSDE or use an existing SQL Server. ... I connect to an Access database of about 500MB in size. ... there is the "Not enough space on temporary disk". ... What can a "System resource" be apart from disk space and RAM? ...
    (comp.databases.ms-access)
  • Re: Migrating away from MS-Access
    ... Ive just stepped into a role to migrate an access database to VB.Net. ... At some stage we have to migrate the access backend to SQL Server. ... A dot-net front end with an MDB file back end is the worst of all worlds. ...
    (comp.databases.ms-access)
  • Re: Connection between MDB and SQL Server
    ... According to your code,table2 is on Access database but I ... don't need to specify that it is Access table. ... How can ADO judge this is not a SQL table but Access ... >> data from Access into the table on SQL Server. ...
    (microsoft.public.access.modulesdaovba)