Author |
Topic |
cfoster
Starting Member
7 Posts |
Posted - 2006-04-11 : 20:10:08
|
Hi,I've created a simple MS-Access based database for a client. I don't have access to the database except through ASP/SQL.I needed to add a new column to a table and that was fine. But I must have mistyped the column name because if I try to select it, I can't. I can 'select *' and see that there is a new column full of null values, but I can't select it by name.I have since added a column with the correct name and that seems to be working fine, but I would like to clean up this other column without bothering them.Is there a way to get a description of a table via SQL if the database is just MS-Access?I have tried MANY variations to see if I could get something to work, but so far I usually just get the error:"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."which leads me to believe there is no way to do it, unless its in a stored procedure that I don't know about.Any thoughts on the matter would be greatly appreciated!Thanks,Colin. |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-11 : 21:02:13
|
try select [your column name] from yourtable KH |
|
|
cfoster
Starting Member
7 Posts |
Posted - 2006-04-11 : 21:33:21
|
quote: Originally posted by khtan try select [your column name] from yourtable
Unfortunately it's the 'your column name' that I don't have.If there was a way to refer to a column by number such as ALTER TABLE my_table DROP COLUMN 4that would also work for me. But if not, I need some way to find out what the column name actually is.Thanks,Colin. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-11 : 21:37:45
|
but you can see the name of the column?your problem is you can't drop that column? but can you drop other columns? --------------------keeping it simple... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-11 : 21:49:03
|
use sp_help yourtable and see weather the column is there KH |
|
|
cfoster
Starting Member
7 Posts |
Posted - 2006-04-11 : 23:42:50
|
Hi Jen:I created a column and I no longer know its name, so I would like to find some way to drop that column. I think I need to know its name to do that, but I don't know how to find the column names of a table in MS-SQL. (I can do it in mySQL with DESCRIBE, so I guess I'm looking for an equivalent to that.)Khtan:Thanks for the suggestions!I just triedprocedure sp_help my_table_name but got back "Microsoft JET Database Engine (0x80040E14)Syntax error in PARAMETER clause." Perhaps that stored procedure isn't supported by MS-Access?Can you think of anything else I can try? (Or was my syntax incorrect?)Thanks,Colin. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-11 : 23:50:37
|
Oh MS Access. Sorrytry select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name KH |
|
|
cfoster
Starting Member
7 Posts |
Posted - 2006-04-11 : 23:59:13
|
Hmmm... The error I get now is:Could not find file 'C:\WINNT\system32\INFORMATION_SCHEMA.mdb'. Does this mean their server isn't quite setup correctly? (It's certainly possible.)-Colin. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-12 : 00:18:49
|
MS Access might not support INFORMATION_SCHEMADoesn't select * from my_table_name gives you the column name ? KH |
|
|
cfoster
Starting Member
7 Posts |
Posted - 2006-04-12 : 00:23:44
|
Sadly, no. 'Select *' returns just the data.The fact that a path is given to the target file makes me think MS-Access knows what to do with that command, but is missing a file to do it. (But I could be wrong, it just seems odd that MS-Access would know the path of a service it doesn't have, if you see what I mean.)Well, I guess I'm stuck, unless there are any other suggestions?Thanks very much for all the feedback!-Colin. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-12 : 02:15:46
|
What's your front end?usually if do it in vb, and save the recordset in a an ado, the object will have the column names...not sure what you are using, there must be a way to identify the column unless it's array type...--------------------keeping it simple... |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-04-12 : 08:25:10
|
Try in ASP (U may need to define the record set, i and a connection object and connect to the DB, pass the Select top 0 * from ur_table and then do the following)For i = 0 To rs.RecordCount - 1 rs.fields (i)Next Srinika |
|
|
cfoster
Starting Member
7 Posts |
Posted - 2006-04-12 : 09:06:54
|
Jen:The front end is ASP/Javascript, but I could just as easily write something in ASP/VBScript.Currently I return the result in 'query_result' and loop through its .Fields(i) like so: rec_count = g_query_result.Fields.Count - 1; while( !g_query_result.EOF ) { for( i = 0; i <= rec_count; i++ ) { one_field = g_query_result.Fields(i); Response.Write( one_field + " " ); } Response.Write( "<br />" ); g_query_result.MoveNext; } -------------Srinika:I get an error when I try to select top 0, but I'll see what I can do in in VBScript since it handles objects differently.[time passes]Success! Thanks! Here's a code snippet to spill one's table's column names:<%@ Language="vbscript" %><% Dim objConn Dim objRec Set objConn = Server.CreateObject("ADODB.Connection") Set objRec = Server.CreateObject("ADODB.RecordSet") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=PATH_TO_MS_ACCESS_DATABASE" objRec.Open "MY_TABLE_NAME", objConn, 0, 1, 2 For Each fldF In objRec.Fields Response.Write fldF.Name Response.Write "<br />" Next objRec.Close objConn.Close Set objRec = Nothing Set objConn = Nothing%> It turned out I originally created the column with the name "f_account_type" but thought it was "f_user_type".Thanks again to everyone for their assistance!-Colin. |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-04-12 : 09:15:49
|
quote: Originally posted by cfoster Srinika:I get an error when I try to select top 0, but I'll see what I can do in in VBScript since it handles objects differently.
The reason is not the way VB Script handles it, but "top 0" is not understood by "Access"Following should work;<%@ Language="vbscript" %><% Dim objConn Dim objRec Set objConn = Server.CreateObject("ADODB.Connection") Set objRec = Server.CreateObject("ADODB.RecordSet") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=PATH_TO_MS_ACCESS_DATABASE" objRec.Open "Select Top 1 * from MY_TABLE_NAME", objConn For Each fldF In objRec.Fields Response.Write fldF.Name Response.Write "<br />" Next objRec.Close objConn.Close Set objRec = Nothing Set objConn = Nothing%> Srinika |
|
|
cfoster
Starting Member
7 Posts |
Posted - 2006-04-12 : 09:27:34
|
quote: Originally posted by Srinika objRec.Open "Select Top 1 * from MY_TABLE_NAME", objConn, 0, 1, 2
Hmmm... oddly enough that doesn't work. I get a syntax error. Yet if I copy/paste the same query into my javascript version, it works fine (but selects the first record, not the column headings).Anyway, the way I had it with just stating the table name seems to work fine.Thanks again!-Colin.EDIT: Sorry, I didn't notice you dropped the "0, 1, 2" from the end. It does work just as well. (I have no idea what those numbers specify, actually -- my code seems to run fine without them, too). |
|
|
wjbarton
Starting Member
3 Posts |
Posted - 2007-02-01 : 18:24:50
|
This works for SQL server, borrowed code from sp_help. Change the second line to reflect the column name you're looking for:declare @tblname varchar(100)select @tblName = 'tblUsers'declare @objid intdeclare @sysobj_type char(2)select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@tblName)select name from syscolumns where id = @objid |
|
|
wjbarton
Starting Member
3 Posts |
Posted - 2007-02-01 : 18:25:49
|
On the above post I meant change the second line to reflcet the name of the table you're trying to get column names for... |
|
|
wjbarton
Starting Member
3 Posts |
Posted - 2007-02-01 : 18:30:13
|
Changed the last line to sort properly (was sorting by name by default), not sure what 'number = 0' is for, grabbed it from sp_help also:select name from syscolumns where id = @objid and number = 0 order by colid |
|
|
Anthraxx
Starting Member
1 Post |
Posted - 2009-08-11 : 13:58:45
|
Use the following code to get the names of all columns in a table:Dim oConn As New System.Data.OleDb.OleDbConnectionDim oCmd As New System.Data.OleDb.OleDbCommandDim iClm As IntegeroCmd.Connection = oConnection~~Code to connect to the database~~With oCmd .CommandText = "SELECT * FROM TABLE_NAME" With .ExecuteReader 'Get the column names For iClm = 0 To .FieldCount - 1 'To get the name use: '.GetName(iClm) 'For example: lstColumns.Items.Add(.GetName(iClm)) Next 'Close the reader .Close() End WithEnd WithGreetz,Anthraxx |
|
|
|