Re: How to copy a table from one db to another in code.

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Unicorn (unicorn_at_somewhere.com)
Date: 10/28/04

  • Next message: Paul Clement: "Re: Memo field Truncated" 
    Date: Thu, 28 Oct 2004 23:51:27 +0930

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%2392osSNvEHA.3152@TK2MSFTNGP14.phx.gbl...
    > "Unicorn" <unicorn@somewhere.com> wrote in message
    > news:eSUiVNNvEHA.4028@TK2MSFTNGP15.phx.gbl...
    > >
    > > "Ed Staffin" <Ed Staffin@discussions.microsoft.com> wrote in message
    > > news:41C30A34-1434-41B6-9715-2AFE50036DD4@microsoft.com...
    > > > Hi,
    > > > I need to copy a table from one access mdb to another. Is there a
    quick
    > > and
    > > > easy way to do this in code? I need to drop the existing tables from
    the
    > > > target db and replace them with the new ones. The really important
    part
    > is
    > > to
    > > > not mess up the autonumber fields.
    > > > Thanks ... Ed
    > > >
    > >
    > > The AutoNumber bit is a worry. There is NO way that I am aware of that
    > you
    > > can insert numbers into AutoNumber fields..... Once data is in the
    table
    > > you can't make it an AutoNumber field.
    > >
    > > You might be able to use an update query.... depends on what result
    you
    > > are trying to get
    >
    > Actually, there's no problem inserting complete rows, including the
    > Autonumber field, into a table. In fact, that's the standard approach to
    > making the Autonumber start at a particular value: insert a row where the
    > Autonumber field's one less than what you want to start at, then delete
    that
    > row.

    I bow to your superior knowledge.
    I have always avoided Autonumber for anything more meaningful that a unique
    key for a table. Always found them to be more trouble than they were worth.

    >
    > However, I have to question the wisdom of doing it, especially since it
    > sounds as though only a single table is being imported, so that there
    can't
    > possible be RI to worry about.

    I think the O/P might be using the autonumber field as lookup value from
    other tables. If that is the case the there are RI issues. However an
    update query would resolve any such problems but updating existing data
    insitu
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    >

  • Next message: Paul Clement: "Re: Memo field Truncated"

    Relevant Pages

    • Re: How to copy a table from one db to another in code.
      ... >> I need to copy a table from one access mdb to another. ... >> not mess up the autonumber fields. ... > The AutoNumber bit is a worry. ... Autonumber field, into a table. ...
      (microsoft.public.vb.database.dao)
    • Re: do all primary keys use autonumber
      ... that you won't have any duplicate values, and an Autonumber field occupies only about 4 bytes per record, much shorter than many other fields you might choose to use. ... choice of first resort, because it satisfies the requirements for a primary key, is small, and is not bound to anything else in the model, making it practically immune to any need for revision). ... Say, a table with just two columns, a random integer column named ID ...
      (microsoft.public.access.tablesdbdesign)
    • Re: "Indexed or Primary Key cannot contain a Null value" / Autonum
      ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ... >> c) Is there any Default Value in the text box for the autonumber field>> on ...
      (microsoft.public.access.modulesdaovba)
    • Re: "Indexed or Primary Key cannot contain a Null value" / Autonum
      ... CaseAutonumber ... Allen Browne - Microsoft MVP. ... post the exact error message you are receiving. ... Is there any Default Value in the text box for the autonumber field ...
      (microsoft.public.access.modulesdaovba)
    • Re: Creating an Orders Form with multiple order types
      ... autonumber function, but I need sequential number for each type of order. ... the DMAX function. ... you can simply use an autonumber field and make the order number unique ...
      (microsoft.public.access.forms)