May 18, 2004 at 7:21 am
I'm converting my VB application using ADO -> MS Access to ADO -> SQL Server and have a major difference in behavior. When selecting like named columns from different tables using the * (as opposed to naming columns), the field names returned in the recordset object are not qualified by the table name. For example:
select * from table1, table2
this should return field names: "table1.field1" and "table2.field1", which it does when using the MS Access provider, but not when using the SQL Server provider. I'm using the evaluation version of SQL Server. Any help is greatly appreciated.
May 18, 2004 at 1:43 pm
I don't know how or why Access returns the table name and SQL does not, but SQL returns all the fields from table1 then all the fields from table2. A possible reason for this is that using select * in SQL Server is not as efficient as specifying field names as it may cause a table scan in SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2004 at 9:03 am
I think the reason that table name is added in front of column name in Access is you can't have duplicate column names in the resultset by rule. So in order to return unique column names in your "select * " statement, table name is added. However, in SQL Server such rules do not exist. There is really no need for SQL to add table names in the front. If you want the column names to stay the same, you have to use alias in your SQL statement.
Hope this helps,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply