Image of Navigational Panel mapped to Contents / Home / Search Aliasing with Access 2.0
Tech Support Question
Image of Line Break

Q. Is it possible to create a dynaset from within VB against an Access 2.0 database where the SQL string uses aliasing?

I have the following example that works with no error: 

  Buf="C:\data\mydata.mdb"
  Set DB = DBEngine.Workspaces(0).OpenDatabase(Buf)
  Set DSResult = DB.OpenRecordset("Select count(*)  from Table1", dbOpenDynaset)

But the following lines produce an error: 

  Buf="C:\data\mydata.mdb"
  Set DB = DBEngine.Workspaces(0).OpenDatabase(Buf)
  Set DSResult = DB.OpenRecordset("Select count(*) 
                                   xcount from Table1", dbOpenDynaset)

As you see the only difference is trying to show count(*) as xcount. Is there a way to get around this? I have tried using SQLPassThrough method also but I still get an error.

I'd appreciate your help. Thanks!

From: Jim
Email: 72253.1465@compuserve.com

A. Jim, the solution to your problem is very simple. Unfortunately, when one moves from one database product to another you have to get used to each variation in SQL syntax. Unfortunately, although Access SQL is very forgiving, it is not entirely ANSI standard. Most SQL engines will recognise the syntax you have tried to use above for aliasing the value. However, Access supports only one syntax for aliasing and that is the AS keyword. Here is your SQL statement correctly rewritten using this keyword: 

    SELECT Count(*) AS xcount FROM table1

Unfortunately this is one of those little gems that is pretty well hidden in the documentation, so it's pretty easy to miss. The reason why the example, with no attempted alias, works is because the JET engine figures out that the value is derived and therefore cannot inherit a value directly from a field in the base table, so it generates a name for the column itself and aliases it quietly. Sometimes the resultant names make sense, sometimes they don't. However, this does at least still allow you to reference that field via its index. As it is the only field in this case you could simply use DSResult.Fields(0) which gives you the value of the first field in the recordset.

By the way, the reason why your SQL passthrough made no difference is that when you are hitting an Access database from VB you are only using one database engine. When you are accessing another database via ODBC through VB's database engine (JET) you can have either engine execute the query (if it's in the appropriate syntax) by specifying SQLPassthrough (to have the back end process it) or not - which will have JET process the SQL. When you are directly opening an Access MDB there is no back-end database so there is no second engine to Pass Through to. Make sense?

Hope this helps you out.

Image of Arrow to Previous Article Image of Arrow to Next Article

[TECH SUPPORT TOC]
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]