Solved

Retrieving Access field names - in correct order

Posted on 2006-10-30
5
258 Views
Last Modified: 2013-12-25
Hi Gurus

I'm using what I believe to be a fairly standard method of retrieving Access field names:

    DbsConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
    strOpenMDb = "Data Source=" & FilePathAndName & ";Jet OLEDB:Database Password=" & Password
    DbsConnection.Open strOpenMDb

   ' Use OpenSchema and get the table names.
    Set rs = DbsConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty, tDef_Onnet_DSLAMs))
   
   rs.MoveFirst
    Do While Not rs.EOF
        ThisCol = rs!COLUMN_NAME
        rs.MoveNext
    Loop

However, the order in which it fetches the columns is different to how the columns are displayed within Access.

What do I need to do in order to fetch the column names in the same order as Access - this is most important.
0
Comment
Question by:BrianBeck
  • 4
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17832987
Does this help any?

    Dim i As Integer
   
    For i = 0 To rs.Fields.Count - 1
        Debug.Print i+1,rs.Fields(i).Name
    Next i


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17832998
urm, scrub that
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 300 total points
ID: 17833011
ok, here is an alternative way, it means u have to use DAO

    Dim db As DAO.Database
    Dim fld As DAO.Field
   
    Set db = DBEngine.Workspaces(0).OpenDatabase("C:\myremote.mdb"
    For Each fld In db.TableDefs("MyTable").Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next fld
    db.Close
    set db=Nothing

the OrdinalPosition gives the position in the table
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17833016
urm missing closing round bracket on OpenDatabase,
doh!
0
 

Author Comment

by:BrianBeck
ID: 17833066
Thanks rockiroads - on the button!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now