Is it possible to direct the placement/order new of fields when modifying an Ms Acces (1 Viewer)

[goJimH]

Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?

I need to modify the schema of an MS Access database via code – but I want to be able to direct the order or placement of the fields within the tables.

For example - if TableOne has 3 fields – Field10, Field20 and Field30. I would like to be able to add say Field15 between Field10 and Field20 – not just append it to the end of existing fields.

I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter – but it seems all of these offer no placement of the field within the table.

Any and all suggestions appreciated.

JimH

 

[Pat Hartman]

No. You can't do this with code or DDL. In fact. I don't even think it actually happens when you add a column with the GUI. I think Access still adds the column to the end of the column list. It just remembers where you placed it and reorders the columns for presentation when you open the table in design or datasheet view. Think about it. What would Access have to do to physically change the column order? I'll answer the question. It would have to update every single record and the only way to do it would be to copy the table to a new table with new column order and when the data was copied, delete the original table and rename the copy. Not to mention the work it would need to do to recreate indexes and drop and recreate relationships. Given that even with a million row table, there is no delay if you add a column and save the table, the structure is not physically being changed.

 

[goJimH]

Pat,

Thank you for your time – and clear response. I was very concerned about the order – as my lower code that accesses the database makes heavy use of object arrays to transport the data in and out my strong-typed net classes. And just one field out of order would be a problem.

Since I asked the question – I have been reviewing the lower code – to look for alternatives. But as it turns out 99% of my code specifically specifies the field list order via Sql and therefore field order to retrieve update or insert not a real problem with field order as I had imagined.

Still part of me would want to maintain that symmetry. But as your seasoned comment loudly suggest – leave it alone.

Once again, thank you! I am very appreciative.

JimH

 

[Pat Hartman]

As long as you stay away from Select *, you'll be fine because as you mentioned, you can control the column order in your query. This is best practice anyway and makes upsizing to SQL Server that much easier and more efficient.

I get your comment about symmetry. It unsettles me also but once you understand the cost of attaining symmery, you can understand why database engines work the way they do.

 

[goJimH]

Pat,

You nailed it. I use a database to define all my databases, tables, fields and even extend it to direct screen edits. All my lower db access code is machine generated from this database. And yes – there is one case where I use Select * which is easy enough to fix.

If you don’t mind, I would like to ask – what is the safest or best way / technology to use to alter / add / delete fields in an existing table? I have recently seen some suggesting that DAO is better.

Once again – Thank you

Jim H

 

[Pat Hartman]

I use different methods depending on the situation. Obviously when I am developing a new application, I just open the table in design view and make the change. If the application is an in-house app for a client, it depends on how extensive the change is. If it is small, I make it in the test system and test it. Then in the evening or on the weekend (unless it is an emergency, in which case I just kick everyone out), I make the change to the production database and test again. This is somewhat dangerous and you need good notes so you don't forget anything. So the better solution is to actually use DAO or run DDL queries to make the changes and this is what I do if the change is extensive. In this case I actually create an update database and put all the code and queries in there to manage the update. When the app is distributed to external clients, I always use the third method.

Access has no way to make this process easy but I discovered a SQL tool that works great. Since many of my databases actually use SQL Server rather than Jet/ACE, I use these tools. They take two databases and compare them. You can then ask them to generate DDL to make databaseA look like databaseB. This is so convenient, I frequently do it for my Jet/ACE databases. I upsize both versions, run the compare and then using a text editor, modify the generated script to something that Access likes better. It is called SQL Examiner by TulaSoft LLC. Sadly they only support Access for data compares.

 

[goJimH]

Pat,

First of all – thank you!!!!!

My situation will be updating remote locations. I think I understood everything you said up until the point “Sadly they only offer Access Support for Data compares”. And appreciate your suggestion of external tools. In the Dos world – I wrote my own. If I can side step that – Great! FYI – I am in the process of moving from Dos to Widows.

I like your idea of - putting all your code and queries in the update database. I guess this can be done yet still manage adjusting to different versions. Yet one more concept to digest.

You mention DLL or DAO. I have been using DDL for years to build the databases via code – but have yet to use it – especially at a remote location – to update. If DAO is an option and safer – I can learn it. My concern is absolute database health as much as possible. I can tell you – when the update is processed – all other users will be locked out – if that makes a difference.

Forgive my being perhaps overly cautious. I just read about JRO compact and repair failing to work – and want to do everything I possibly can to avoid that. Including redundant backups and weekly compact and repair.

I have had a smaller program out there in Windows (VB60) for several years – and so far – there has never been a problem. Which does give me confidence in Ms Access.

If I may – between DAO or DLL – is there much difference in results that I should choose one or the other?

Once again, your insight is greatly appreciated.

JimH

 

[goJimH]

Pat,

P.S.

I intend to have all the lower code – control the order of the fields. However, to be extra careful (belt and suspenders) – was considering changing the transport for ObjectArray (or itemarray in vb net) to DataRow – and the receiving class parse and accept by field name. Field order would not matter then.

This may be overkill – and I am not at all sure if there is a efficiency penalty to be paid.

 

[Pat Hartman]

Using classes to abstract and encapsulate makes an Access application sluggish and heavy. Leave that for the dot net environments. Access applications require a fraction of the code that a similar app developed in dot net does. That's why Access is called a Rapid Application Development environment. You wouldn't use RAD techniques in a dot net app. Don't use dot net techniques with Access. Think of it as trying to make tables that mimic spreadsheets.

I use DDL for whatever it can do since it is easier. Each version requires a separate conversion database. So V1->V2, V2->V3, etc.

I think I understood everything you said up until the point “Sadly they only offer Access Support for Data compares”.

The tool claims to support Jet but it won't do a structural compare between two databases which is why I have to upaise to do the compare and have the tool generate the DDL. It will only compare data in tables.

 

[gemma-the-husky]

i was messing around with inserting fields directly into tables, and remembered this post.

I think you may be able to get what you want with the attribute

"ordinalposition"

tdf.fields("somefield").ordinalposition= whatever

 

[goJimH]

i was messing around with inserting fields directly into tables, and remembered this post.

I think you may be able to get what you want with the attribute

"ordinalposition"

tdf.fields("somefield").ordinalposition= whatever

Thank you Dave. I will have to try that out as well.

I appreciate your input.

Jim

 

[Pat Hartman]

The OrdinalPosition is how Jet keeps track of where you want the column. That's why you can drag columns around in datasheet view and if you save, Access will remember the order. It doesn't actually move the column.

 

[goJimH]

Using classes to abstract and encapsulate makes an Access application sluggish and heavy. Leave that for the dot net environments. Access applications require a fraction of the code that a similar app developed in dot net does. That's why Access is called a Rapid Application Development environment. You wouldn't use RAD techniques in a dot net app. Don't use dot net techniques with Access. Think of it as trying to make tables that mimic spreadsheets.

I use DDL for whatever it can do since it is easier. Each version requires a separate conversion database. So V1->V2, V2->V3, etc.

Pat,

You have already been a huge help! Just your sharing about using a 3rd party tool has been a God-send for me.

I spent much of Friday searching and looking for such a tool to work with Access databases but really did not find anything – that is until I tried DbWeigher and DataWeigher at http://dbweigher.com . They are not nearly as robust SQL Examiner looked to be – but – they do work with Access databases – and do provide the scripts. So far – I am very pleased.

In my case – I use the DbWeigher to first to update the schema. Then – as I have a lengthy ‘xTableFields’ file embedded in each Db – that describes the database – I use the DataWeigher – pointed only at the xTableFields table – to get that in order. This seems to do well – and gives me the scripts I need for my updates (V1->V2, V2-V3, etc.).

Regarding using classes to abstract and encapsulate – all the changes I made – were on the dot net side. And made to the code – I machine generate from the ‘xTableFields’ file. Instead of accepting and parsing object arrays and assuming the order is always the same – I now parse by field name – just in case something is out of order or otherwise has changed.

 

[goJimH]

The OrdinalPosition is how Jet keeps track of where you want the column. That's why you can drag columns around in datasheet view and if you save, Access will remember the order. It doesn't actually move the column.

Pat,

Thanks for the heads up.

P.S. - After I make my schema changes via DbWeigher - and go back in and test it again - DbWeigher shows the schema's to be the same - but does report any ordinal differences. But as you and others have pointed out - that is just the way it is.

Jim H