Refer To Access Table's Column Name

ritag · June 23rd, 2004, 03:31 PM

I have 2 questions:

First:
Using VB Script in an ASP page, I've returned a recordset from an Access database. The columns from the table that I queried have spaces in the names e.g. [PPO Org].

I tried referencing the column names by rs("[PPO Org]") or rs([PPO Org]) or rs("PPO Org") but got an error with all of them. I was able to use rs.Fields(0) and that got me the first column but I would like to be able to use the column name with the space in it.

Is there some property for the recordset that will allow me to view the returned column names?

Any suggestions?

Second:
The Access database resides on the server where the ASP page resides. When I try to read an Access database on a different server I get a permissions error.

Is this something I need to correct via IIS or can I just add permissions to that server's folder for IUSR_MyServerName?

TIA.

Rita

Steven · June 23rd, 2004, 07:20 PM

Could you possibly change your recordset, so it's source is something like:

Code:

SELECT [PPO Org] as PPOOrg, [Some Other Field] as SomeOtherField From YourTable

Steven

I am a loud man with a very large hat. This means I am in charge

happygv · June 24th, 2004, 08:23 AM

Hi Rita,

You can directly us it refering as rs("PPO Org"). This works fine for me. I don't see any issues with it.

You should always keep in mind that, you don't use any reserve keywords of MS Access as column names. Otherwise, you can use it this way to get the values.

If that still does not work for you, you can try it this way.

Code:

set conn = Server.Createobject("ADODB.connection")
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & server.MapPath("./yourdb.mdb")
set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from yourtable", conn
for each x in rs.fields
   response.write(x.name)
   response.write(" = ")
   response.write(x.value)
   response.write("<br>")
next
conn.close

This should do the trick.

PS: You can follow as Steven suggested, but I would recommend, not to use SPACEs in your COLUMNNAME though it allows using SPACEs, IMO it is not a good practice.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

ritag · June 24th, 2004, 09:30 AM

Thanks Steven and Vijay for your help.

While working with Steven's suggestion I discovered that the Access table was using an alias for the column name. Hence when I viewed the data in the table I saw "PPO Org". However, when I started creating a query in Access, I noticed that the column name really is "PPOOrg". That explains why I kept on getting an error.

Vijay, your example was exactly what I was looking for. Thanks!