Creating Tables with ADOX

[Lisa]

Hi,

I am using ADOX to create tables in a database. My users are creating the
field names so I won't know what they are until I open the new table. I
am using the following code:

Dim NewTable As New Table
Dim catTable As New ADOX.Catalog

Set NewTable = New Table
catTable.ActiveConnection = "myDB.mdb"

NewTable.Name = "Customers"
catTable.Tables.Append NewTable

NewTable.Columns.Append "FirstName", adVarWChar, 50
NewTable.Columns.Append "LastName", adVarWChar, 50
NewTable.Columns.Append "City", adVarWChar, 50

Set catTable = Nothing
Set NewTable = Nothing

I want to be able to use a bound data grid to display the recordset. The
problem I am running into is that ADO is creating the table with the fields
in alphabetical order instead of the order I pass in.

So, for instance, instead of First Name, Last Name, City, I get City, First
Name, Last Name. This is the order in which the grid shows the columns.
Definitely not what I want.

DAO set the ordinal postion of the fields as they were created. Apparently,
ADO does not do that. I am unable to find an ordinal postion property for
ADO.

Any suggestions would be greatly appreciated. My only alternative is to
make an unbound data grid which is major headache.

Thanks - Lisa

[Shawn]

Lisa, you didn't mention to what you are binding your grid control. I am assuming
you are using the "table" as the recordsource. To solve your problem, build
a query with the columns in the order you want and use the query as your
recordsource.

[Lisa]

Hi Shawn,

I am using DBGrid bound to the VB6 Data control. I am setting the database
name and recordsource in code to the database and table selected by the user
on a previous form. The grid is populated on form load. It is currently
populated with the columns in alphabetical order which I do not want.

I have never built a query and passed that as the recordsource. Could you
elaborate please? Does the query reside inside the database or somewhere?

>Lisa, you didn't mention to what you are binding your grid control. I am
assuming
>you are using the "table" as the recordsource. To solve your problem, build
>a query with the columns in the order you want and use the query as your
>recordsource.

[Lisa]

Hi Shawn,

I was able to pass a SQL query as the recordsource for the data control.
When I used the ORDER BY clause, it ordered the rows and not the columns.
I have not been able to find a SQL statement that orders the columns.

Lisa

>>Lisa, you didn't mention to what you are binding your grid control. I am
>assuming
>>you are using the "table" as the recordsource. To solve your problem, build
>>a query with the columns in the order you want and use the query as your
>>recordsource.

[Jman]

Are you passing the query as something like "SELECT * FROM Customers ..." ?
In that case, you'll get the columns in the same order as in the table. What
you'd have to pass as recordsource should be

SELECT FirstName, LastName, City FROM Customers ...

Doing that you define the column order for the query and also for the grid.

Lisa escribió en mensaje <39d3cfa0$1@news.devx.com>...
>
>Hi Shawn,
>
>I was able to pass a SQL query as the recordsource for the data control.
> When I used the ORDER BY clause, it ordered the rows and not the columns.
> I have not been able to find a SQL statement that orders the columns.
>
>Lisa
>
>>>Lisa, you didn't mention to what you are binding your grid control. I am
>>assuming
>>>you are using the "table" as the recordsource. To solve your problem,
build
>>>a query with the columns in the order you want and use the query as your
>>>recordsource.
>