MS Access Column names

[Fuma]

In Oracle/SQL Server you can say something like:
Select column_name from sysobjects where table_name = 'order'

How do I do that in MS Access?
The MSysObjects doesn't seem to help much.

 

[guest2013-1]

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

In Oracle/SQL Server you can say something like:
Select column_name from sysobjects where table_name = 'order'

How do I do that in MS Access?
The MSysObjects doesn't seem to help much.

Doesn't help much with what exactly. What are you trying to do? And what error are you getting?

 

[Fuma]

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));



Doesn't help much with what exactly. What are you trying to do? And what error are you getting?

I should have said " table field names". My bad.
So i want to see a list of my order table's field names
Order_no
Order_description
Order_date
etc

So the sql statement should be something like:
Select filed_name from MSysColumns where table_name = 'Order'

 

[guest2013-1]

I should have said " table field names". My bad.
So i want to see a list of my order table's field names
Order_no
Order_description
Order_date
etc

So the sql statement should be something like:
Select filed_name from MSysColumns where table_name = 'Order'

try

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name

 

[Fuma]

try

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name

I tried that and it gave me this error:

Could not find file "C:\Documents and settings\heitahola\my documents\INFORMATION_SCHEMA.mdb

 

[guest2013-1]

I tried that and it gave me this error:

Erf... why do you need to use MS Access anyway? How are you using it? Other alternative is to loop through and build up the column names list using ADO if you're accessing it via a program then use dynamic sql within the program to query it....?

Otherwise I'd recommend several other stand-alone (not server based) databases you could use that acts more like MS SQL and MySQL than MS Access will ever do... without the need for propriety software or servers.

 

[Fuma]

Erf... why do you need to use MS Access anyway?

Otherwise I'd recommend several other stand-alone (not server based) databases you could use that acts more like MS SQL and MySQL than MS Access will ever do... without the need for propriety software or servers.

LOL. When a programmer can't seem to find a solution, they always say why use MS Access. I wish I could use another database. Haven't used MS Access in a long time.

I just wanted to know if there is a simple sql statement to do that, without using other programs. I guess it's not possible with MS Access query. Maybe Access 07 has this ability.

Edit: I managed to get it, though I didn't want to do it through the code.

Dim oleConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB & ";User Id=admin;Password=;")

oleConn.Open()
Dim schemaTable As DataTable
Dim i As Integer
schemaTable = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "order", Nothing})
For i = 0 To schemaTable.Columns.Count - 1
Debug.Print(schemaTable.Rows(i)!COLUMN_NAME.ToString)
Next i
oleConn.Close()

link

 

[guest2013-1]

LOL. When a programmer can't seem to find a solution, they always say why use MS Access. I wish I could use another database. Haven't used MS Access in a long time.

I just wanted to know if there is a simple sql statement to do that, without using other programs. I guess it's not possible with MS Access query. Maybe Access 07 has this ability.

Edit: I managed to get it, though I didn't want to do it through the code.

link

Could've given you that code ages ago, felt like I knew there was a SQL statement for that.

The reason why programmers shy away from MS Access is exactly this reason (and several others). Figured I'd be more help trying to get it to work knowing full well if I just told you to use something else you would've gone "aaaaah BOOO!!!"