Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Revamped Data Base

    Old data and server out.

    New Data and server coming soon.

    I have VBA, complex queries, reports, forms in the double digit numbers.

    How do I fill the holes of the missing fields so everything still works?

    If the fields are not in the exact order everything comes apart and no longer computes.

    This is a monstrous undertaking I've been working on over a week with absolutely no success as of yet!

    Ideas . . . Rick

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why is it a monstrous undertaking
    why should the exact order of columns matter

    Assuming you have a split db all you should have to do is copy you data and application files onyo the new server, link the data to the application and awa y'go. If you dont have a split db then its even easier.

    Forms have no concept of the order columns appear in in a query, rows of data yes, but columns no.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Import Field Names

    I'm using Access 2003.

    I import a text file using the Access import spec window.

    I'm changing some of the field names on the import spec window to match my old database field names.

    The field types are the same type.

    ie.,
    New field name: DateClosedSale DateType

    Old field name: ClosingDate DateType

    All the field names on the original text import are the correct fields I need.

    However, if I change the field name on the import spec it does not change the fields names in the new table?

    What am I missing here?

    Thanks . . . Rick

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what are you missing.... please don't tempt me

    why are you using an import specification, just import the tables from the old db
    renaming columns is always tricky, I've yet to see a good reason why that needs to be doen, ever. especailly if you have lots of forms, reports and queries that use those columns. it a waste of design/development resource plain and simple.

    is changing
    Old field name: ClosingDate DateType
    to
    New field name: DateClosedSale DateType
    actually worth doing, does it mean anything. if you must rename columns then do the right thing and stop using spaces in table/column names whilst you are at it. its a seriously bad idea. if you require a human friendly name then set the caption/label property as part of the columjn definition in the table design.

    Id honestly suggest you ought to be dropping these dirty habits by now.

    if you do change things for better reason than a whim then you need to do an impact analysis of what the propsoed changes are, what they affect and get an idea of how long its going to take.


    but importing via an import specification is a crazy approach. import the table + date, then change the column names if you really really like a hard life and must do so. but don't be surprised or whine about the amount of effort required to fix your dabbling.

    by importing the table and change column names you may strike lucky and find that Access automatically ripples the changes through any queries, and possibly some forms or reports, but I wouldn't hold my breath on that
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    you said . . ."by importing the table and change column names you may strike lucky and find that Access automatically ripples the changes through any queries, and possibly some forms or reports, but I wouldn't hold my breath on that"

    I tried moving the data from the new table to the old table but the query failed because the column names are different even though the data type is the same.

    ie. ClosingDate would not accept data in the query from a field column named DateClosed.

    Now I'm trying a new approach by using a query where I can change the column name in the query, like SaleAgentOfficeKey: SO_Key.

    I'd rather use the new fields names as they are all smaller but this would take a serious amount of time but maybe it's the way to go.

    Like all the fields on the reports that have calculations based on the old field names, and SQL queries that wold all have to be changed.

    I just can't see a way around it however.

    Rick

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is no magic bullet fix for this

    moving the data is easy

    changing the queries, forms and reports wont be

    if you have a wirking system then leave well alone.

    if you must meddle with the design then do it over a long time but not at the same time as a server change. The server changeover can be stressful enough on its own,
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •