Table Creation in Access with ADOX

[LegoPiece]

Hello!

Okay, so I managed to successfully create a new .mdb file with ADOX (yay!), and also managed to create tables and fields using the .Append method. All fine and dandy, HOWEVER, the field names in the Access file are now alphabetically ordered. Is there any way to do this, so the columns show up in the same way I ordered them, when I called the Append method?

Here's what my code looks like so far...

With LocationTable
'.............................................
.Name = "Location"
With .Columns
'.............................................
.Append "ID", adInteger
.Append "Name", adVarChar
.Append "X", adVarChar
.Append "Y", adVarChar
.Append "Z", adVarChar
.Append "T", adVarChar
.Append "Group", adVarChar
'.............................................
End With
'.............................................
End With

Now, when I open up the resulting Access file, guess what? 'Group' is now in my first column and 'T' comes before 'X', 'Y' and 'Z'! I know I'm missing something or doing something grossly incorrect! Can anybody please help me with my otherwise seemingly paltry dilemma? =)

Thanks!
Lego Piece

 

[MichaelRed]

Just as a curiosity, WHY do you care what he column order is? One of the so-called benefits of database is the abstraction of the physical layout of the database. To do (a lot of) extra work to re-introduce the concept of having control of the physical structure seems a little off the beaten path.

On the other hand, I believe that you CAN achieve the effect by doing the complete append transaction for each field as an independent operation.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[LegoPiece]

Hey Michael,

Thanks for the reply! You're absolutely right - it doesn't really make any difference to me what order the columns are in, because I can play with the recordsets and shuffle fields however I want - but there are deeper, darker - more sinister reasons why I'm trying to get the columns to be ordered.

"Just as a curiosity, WHY do you care what he column order is?"

Okay, here's the deal - I'm trying to make my software compatible with the existing application, which we use to teach locations / coordinate frames and matrices for robotic manipulation. Granted, the guys who designed the motion control software have done a reasonable job with it (the user interface is a horror story though) and their method of database storage and retrieval of coordinate info is scarier than the thought of a 4 hour Mariah Carey concert!

That said and done, the existing application relies on fields to be returned in a specific order, for it to work properly. This has all been hard-coded in Visual C++. My software will work in conjunction with the old stuff, however it will need to write to and read from the old database format from previous projects. I would therefore like to standardize a schema, which will work on both apps, and (from what I can tell thus far) this relies on specifically ordered columns.

Yeah, it's a bit of a mess, but I'm trying to plough through it! Like I said, I'm sure I'm missing something, but if you do know of other ways I can get around this - it would be greatly appreciated!

Thanks again for your help!
=)
Lego

 

[link9]

He offered the explanation --

"On the other hand, I believe that you CAN achieve the effect by doing the complete append transaction for each field as an independent operation."

I, too, believe this would work just fine.

Did you try it??

 

[LegoPiece]

Hey Guys,

"On the other hand, I believe that you CAN achieve the effect by doing the complete append transaction for each field as an independent operation."

I'm not quite sure what you exactly mean by that. I thought that each 'Append' method I invoked WAS in itself, an "independant operation", as the word 'Append' by definition means 'tack on'. Since this is not the case (and something out there is doing a 'sort' for whatever reason), Could you please elaborate on how to do a 'complete append transaction for each field as an independant operation?' A little code fragment perhaps?

I truly appreciate the help guys - I'm completely new to ADOX and I'm still trying to get my head around a few key concepts here!

Thanks again!
Lego

 

[LegoPiece]

Hey Guys,

I tried several ways to append columns to the table, but whatever I try to do, they ALWAYS get sorted alphabetically. I'm not sure if I understand what you guys are suggesting to fix that, but I really do want to comprehend the solution you've offered + try it out.

Thanks for your help + patience!
Lego

 

[MichaelRed]

declarations stuff not shown in Original
.
.
.
Other processing
.
'
With LocationTable
'.............................................
.Name = "Location"
With .Columns
'.............................................
.Append "ID", adInteger
'.............................................
End With
'.............................................
End With
.
.
.
'Finish this process Here
.
.
.
'______________________________________________________

'Repeat the above for each of these
.Append "Name", adVarChar
.Append "X", adVarChar
.Append "Y", adVarChar
.Append "Z", adVarChar
.Append "T", adVarChar
.Append "Group", adVarChar

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Sorry,

Late (at least for old geezers). Ran out of Caffine about an hour ago.

If this is just to make a one time database, You can:
A.[tab]Just build it Manually in Ms. Access (It is sort of like body parts, everybody has it)

B.[tab]Use the one =you have already built. From Ms. Access, open the table in design mode and "Drag annd drop" the fields into the order you desire.

"building" a database/table set in code is not often done for "static" apps, unless they are relatively large. I only do it to construct db/table sets for some apps which are relatively far from the main-stream. If you know the schema at design time, it is generally not worth the time to code the process for construction. Many "programmers" I know have NEVER built a database/tableset in code!

On a totally different tack, When I have done the build in code, I CERTAINLY never hard-coded the fields and their properties. You should at least build a table with the fields and their properties, and an additional one for the Indicies. Let your code append the fields as read from the table in a loop. Much easier IMHO. Especially for the inevitable changes. No code to pour through looking for the "problem", just a standard edit of the table. Also, once you have the code worknig the way you want, You can "build" as many databases/tables as you want - with NO NEW CODE! Just a new table to describe the db. I ssomewhere between often and always create the blank db for my apps this way. Installation just runs the db/table builder w/ the specifications.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[LegoPiece]

Good morning Mike!

It Worked! Thanks a million for your help! If you're ever in Southern California, the beers are on me!

Thanks Again!
Lego