Re: Returning Database design with wildcard

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 09/18/04 

Date: Sat, 18 Sep 2004 09:30:39 -0400

snoopy wrote:
> Im trying to fetch the details of the database design in this way :
>
> Set objRS = objConn.Execute("Select TOP 1,
> ColumnName,ColumnName,ColumnName,ColumnName, * from Table")
> For each fld in objRS.fields
> att= att& fld.name &","& fld.definedsize & "," & fld.Type &","
> Next

This is silly. You're retrieving data when you really only want to return
metadata (structure).
At the very least, add a WHERE clause to force the query not to return any
data:

WHERE 1=2

You can get rid of the "TOP 1" in your SELECT clause.

You will still get your metadata. Give it a try. You'll see.

>
> Works fine in Access, however, in MSSQL it returns all of the column
> names again.

It doesn't return all the columns again in Access?? I would expect it to,
which is why I avoid the use of * in my SELECT list
(http://www.aspfaq.com/show.asp?id=2096).

> Now the first 20 fields are always constant and I want them returned
> in that order so that the attributes array is correct.
> Users can add different custom fields which appear at the end of the
> list of fields (hence the *)
>
> Is there a way to make it work the same in MSSQL as it is in Access?
>
No. If it works as you say it does in Access, then Access is not behaving
"correctly". Hmm, I just tried it in Access (I tried it in both Access97 and
AccessXP), and the query I created did not behave as you say it does: I got
a resultset containing the two columns I explicitly specified, followed by
all the columns (including the two I specified, so they were repeated..)

There are many ways to return metadata from various databases. here are
some:
http://www.aspfaq.com/show.asp?id=2177

HTH,
Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: Change text box value?
    ... > Why in the world would I want to change these data bases that have ... > been in existence since 1982 to what you wrote? ... And how is the length of time a bad database design has been in use relevant ... This email account is my spam trap so I ...
    (microsoft.public.scripting.vbscript)
  • MSAccess to Web App
    ... I have an application written in Access97; ... Another possibility would be to keep the database design and business ... I'd lose all of that VBA code ...
    (comp.databases.ms-access)