Finding Field Names

[TidyTrax]

Is there a piece of code where i can get a list of each of the field names in a table???

 

[Stretchwickster]

You could create a query in the following form to list the fields of a table:

describe tablename ' works for MySQL server

sp_help tablename  ' works for MS SQL server

Hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TidyTrax]

WHat i was actually hoping to be able to do was, to take an output of a list of the field names and read them into an array along the lines

For x = 1 to last field_name
FieldNames(x) = currentFieldName
next x

If you see where im coming from!

 

[Stretchwickster]

What are you using for your database access at the moment? Are you using the Database and Recordset objects (DAO)?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TidyTrax]

Yeah thats exactly that im using!

 

[Stretchwickster]

Are you using Excel or Access?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

If you're using Excel then here is the code:

  ...  
  Dim db as Database
  Dim rs as RecordSet
  Dim counter as Integer
  Dim FieldNames() As String
  ...
  Set db = OpenDatabase(...)
  Set rs = db.OpenRecordset(...)
  ReDim FieldNames(rs.Fields.Count)
  ...
  For counter = 0 To rs.Fields.Count - 1
    FieldNames(counter) = rs.Fields(counter).Name
  Next counter
  Set rs = Nothing
  Set db = Nothing

Hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TidyTrax]

Im actually using access and not excel!!!

 

[Stretchwickster]

I'm sorry but I haven't played with Access VBA - it should still be the same code perhaps with some minor changes.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TonyJollans]

Hi TidyTrax,

This ought to do it for you ...

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!Table5

For Each tjFld In tjTab.Fields
    MsgBox tjFld.Name

' Do what you want with the name(s) here

Next

tjDb.Close

Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

Enjoy,
Tony