Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Change field size in linked table

Discussion in 'Information Technology' started by WCDoan, Jul 28, 2006.

  1. WCDoan

    WCDoan
    Expand Collapse
    Guest

    I have just been informed that the database I created for entering yard sale
    license info has to be changed. This is going to involve increasing the size
    of a text field from 1 to 2. Data has already been entered with the field
    being 1. How do I make the required change and apply it to the data that's
    already been entered? Thanks in advance for any help on this.
    RandyM
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Try this kind of thing:

    Dim strSql As String
    strSql = "ALTER TABLE MyTable IN 'C:\MyFolder\MyFile.mdb' " & _
    "ALTER COLUMN MyField TEXT(2);"
    dbEngine(0)(0).Execute strSql, dbFailOnError

    If you can't get that to work you OpenDatabase() on the back end, and
    Execute on that.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    news:948E32C2-441C-4074-BD0A-78FF59C09C54@microsoft.com...
    >I have just been informed that the database I created for entering yard
    >sale
    > license info has to be changed. This is going to involve increasing the
    > size
    > of a text field from 1 to 2. Data has already been entered with the field
    > being 1. How do I make the required change and apply it to the data that's
    > already been entered? Thanks in advance for any help on this.
    > RandyM
     
  4. WCDoan

    WCDoan
    Expand Collapse
    Guest

    Allen, thanks for replying. While I am getting more at ease with Access, I
    still consider myself a 'newbie' and as such I'm not sure I understand how to
    use what you've given me. Is this something that's going to have to be
    executed each time the database is open, or is it a one time thing which is
    what I'm after? And, where would I put it? I'd like to be able to do some
    kind of conversion on the old data and make it reflect the field size change.
    Would have been so much easier had they told me upfront about this, but such
    is life. Thanks again for your reply and if you could explain it a bit
    further, I'd really appreciate it.

    RandyM

    "Allen Browne" wrote:

    > Try this kind of thing:
    >
    > Dim strSql As String
    > strSql = "ALTER TABLE MyTable IN 'C:\MyFolder\MyFile.mdb' " & _
    > "ALTER COLUMN MyField TEXT(2);"
    > dbEngine(0)(0).Execute strSql, dbFailOnError
    >
    > If you can't get that to work you OpenDatabase() on the back end, and
    > Execute on that.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > news:948E32C2-441C-4074-BD0A-78FF59C09C54@microsoft.com...
    > >I have just been informed that the database I created for entering yard
    > >sale
    > > license info has to be changed. This is going to involve increasing the
    > > size
    > > of a text field from 1 to 2. Data has already been entered with the field
    > > being 1. How do I make the required change and apply it to the data that's
    > > already been entered? Thanks in advance for any help on this.
    > > RandyM

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    This is a one-time execute that will change the size of the text field from
    what it is now to 2 characters. You say it is less than 2, so there will be
    no loss of data.

    If it seems too difficult to do by code, you could have them open the table
    in design view, select the field, and change the Field Size in the lower
    pane.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    news:EB602CD9-8823-4A42-9A71-B03A10BA5E9E@microsoft.com...
    > Allen, thanks for replying. While I am getting more at ease with Access, I
    > still consider myself a 'newbie' and as such I'm not sure I understand how
    > to
    > use what you've given me. Is this something that's going to have to be
    > executed each time the database is open, or is it a one time thing which
    > is
    > what I'm after? And, where would I put it? I'd like to be able to do some
    > kind of conversion on the old data and make it reflect the field size
    > change.
    > Would have been so much easier had they told me upfront about this, but
    > such
    > is life. Thanks again for your reply and if you could explain it a bit
    > further, I'd really appreciate it.
    >
    > RandyM
    >
    > "Allen Browne" wrote:
    >
    >> Try this kind of thing:
    >>
    >> Dim strSql As String
    >> strSql = "ALTER TABLE MyTable IN 'C:\MyFolder\MyFile.mdb' " & _
    >> "ALTER COLUMN MyField TEXT(2);"
    >> dbEngine(0)(0).Execute strSql, dbFailOnError
    >>
    >> If you can't get that to work you OpenDatabase() on the back end, and
    >> Execute on that.
    >>
    >> "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    >> news:948E32C2-441C-4074-BD0A-78FF59C09C54@microsoft.com...
    >> >I have just been informed that the database I created for entering yard
    >> >sale
    >> > license info has to be changed. This is going to involve increasing the
    >> > size
    >> > of a text field from 1 to 2. Data has already been entered with the
    >> > field
    >> > being 1. How do I make the required change and apply it to the data
    >> > that's
    >> > already been entered? Thanks in advance for any help on this.
    >> > RandyM
     
  6. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    Allen Browne showed you a way to increase the field size. You could also just
    kick everyone out of the database and open up the table in design view to
    increase it it manually.

    Increasing a text field to a larger size does not require any conversion. In
    your example going from 1 to 2 is not a problem. Now going from 2 down to 1
    can get a little ugly.

    Now after increasing the field size you also need to change the already
    entered data from something like "A" to "AA", you'll need to tell us more. If
    nothing else you need a very good idea of what the data looked like before
    and after. You might even need a table that lists the before and after if
    there are a lot of changes or existing records to make doing an update query
    easier.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "WCDoan" wrote:

    > I have just been informed that the database I created for entering yard sale
    > license info has to be changed. This is going to involve increasing the size
    > of a text field from 1 to 2. Data has already been entered with the field
    > being 1. How do I make the required change and apply it to the data that's
    > already been entered? Thanks in advance for any help on this.
    > RandyM
     
  7. WCDoan

    WCDoan
    Expand Collapse
    Guest

    Thank you all for answering. I owe you an apology. After looking at my
    initial post, I realized that I had left out what is a problem to me. This is
    a linked table. It only involves one table of the database and one field in
    that table. Is it possible to delete the links to the tables, then import the
    tables, make the change, and then split the database again? Again, I
    apologize for omitting the main issue of linked tables and again thank you
    all for your replies.

    RandyM

    "Allen Browne" wrote:

    > This is a one-time execute that will change the size of the text field from
    > what it is now to 2 characters. You say it is less than 2, so there will be
    > no loss of data.
    >
    > If it seems too difficult to do by code, you could have them open the table
    > in design view, select the field, and change the Field Size in the lower
    > pane.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > news:EB602CD9-8823-4A42-9A71-B03A10BA5E9E@microsoft.com...
    > > Allen, thanks for replying. While I am getting more at ease with Access, I
    > > still consider myself a 'newbie' and as such I'm not sure I understand how
    > > to
    > > use what you've given me. Is this something that's going to have to be
    > > executed each time the database is open, or is it a one time thing which
    > > is
    > > what I'm after? And, where would I put it? I'd like to be able to do some
    > > kind of conversion on the old data and make it reflect the field size
    > > change.
    > > Would have been so much easier had they told me upfront about this, but
    > > such
    > > is life. Thanks again for your reply and if you could explain it a bit
    > > further, I'd really appreciate it.
    > >
    > > RandyM
    > >
    > > "Allen Browne" wrote:
    > >
    > >> Try this kind of thing:
    > >>
    > >> Dim strSql As String
    > >> strSql = "ALTER TABLE MyTable IN 'C:\MyFolder\MyFile.mdb' " & _
    > >> "ALTER COLUMN MyField TEXT(2);"
    > >> dbEngine(0)(0).Execute strSql, dbFailOnError
    > >>
    > >> If you can't get that to work you OpenDatabase() on the back end, and
    > >> Execute on that.
    > >>
    > >> "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > >> news:948E32C2-441C-4074-BD0A-78FF59C09C54@microsoft.com...
    > >> >I have just been informed that the database I created for entering yard
    > >> >sale
    > >> > license info has to be changed. This is going to involve increasing the
    > >> > size
    > >> > of a text field from 1 to 2. Data has already been entered with the
    > >> > field
    > >> > being 1. How do I make the required change and apply it to the data
    > >> > that's
    > >> > already been entered? Thanks in advance for any help on this.
    > >> > RandyM

    >
    >
    >
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Open the back-end database and make the change there.

    There's definitely no reason to import the tables back into your current
    front-end. Worst case, you'll have to delete the linked tables from your
    front-end and recreate them, but usually even that's not necessary.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    news:8C627C90-7978-4111-9CCC-F8E6456D6621@microsoft.com...
    > Thank you all for answering. I owe you an apology. After looking at my
    > initial post, I realized that I had left out what is a problem to me. This

    is
    > a linked table. It only involves one table of the database and one field

    in
    > that table. Is it possible to delete the links to the tables, then import

    the
    > tables, make the change, and then split the database again? Again, I
    > apologize for omitting the main issue of linked tables and again thank you
    > all for your replies.
    >
    > RandyM
    >
    > "Allen Browne" wrote:
    >
    > > This is a one-time execute that will change the size of the text field

    from
    > > what it is now to 2 characters. You say it is less than 2, so there will

    be
    > > no loss of data.
    > >
    > > If it seems too difficult to do by code, you could have them open the

    table
    > > in design view, select the field, and change the Field Size in the lower
    > > pane.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > > news:EB602CD9-8823-4A42-9A71-B03A10BA5E9E@microsoft.com...
    > > > Allen, thanks for replying. While I am getting more at ease with

    Access, I
    > > > still consider myself a 'newbie' and as such I'm not sure I understand

    how
    > > > to
    > > > use what you've given me. Is this something that's going to have to be
    > > > executed each time the database is open, or is it a one time thing

    which
    > > > is
    > > > what I'm after? And, where would I put it? I'd like to be able to do

    some
    > > > kind of conversion on the old data and make it reflect the field size
    > > > change.
    > > > Would have been so much easier had they told me upfront about this,

    but
    > > > such
    > > > is life. Thanks again for your reply and if you could explain it a bit
    > > > further, I'd really appreciate it.
    > > >
    > > > RandyM
    > > >
    > > > "Allen Browne" wrote:
    > > >
    > > >> Try this kind of thing:
    > > >>
    > > >> Dim strSql As String
    > > >> strSql = "ALTER TABLE MyTable IN 'C:\MyFolder\MyFile.mdb' " & _
    > > >> "ALTER COLUMN MyField TEXT(2);"
    > > >> dbEngine(0)(0).Execute strSql, dbFailOnError
    > > >>
    > > >> If you can't get that to work you OpenDatabase() on the back end, and
    > > >> Execute on that.
    > > >>
    > > >> "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > > >> news:948E32C2-441C-4074-BD0A-78FF59C09C54@microsoft.com...
    > > >> >I have just been informed that the database I created for entering

    yard
    > > >> >sale
    > > >> > license info has to be changed. This is going to involve increasing

    the
    > > >> > size
    > > >> > of a text field from 1 to 2. Data has already been entered with the
    > > >> > field
    > > >> > being 1. How do I make the required change and apply it to the data
    > > >> > that's
    > > >> > already been entered? Thanks in advance for any help on this.
    > > >> > RandyM

    > >
    > >
    > >
     
  9. WCDoan

    WCDoan
    Expand Collapse
    Guest

    Thanks Douglas, that did it.:)
    RandyM

    "Douglas J Steele" wrote:

    > Open the back-end database and make the change there.
    >
    > There's definitely no reason to import the tables back into your current
    > front-end. Worst case, you'll have to delete the linked tables from your
    > front-end and recreate them, but usually even that's not necessary.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > news:8C627C90-7978-4111-9CCC-F8E6456D6621@microsoft.com...
    > > Thank you all for answering. I owe you an apology. After looking at my
    > > initial post, I realized that I had left out what is a problem to me. This

    > is
    > > a linked table. It only involves one table of the database and one field

    > in
    > > that table. Is it possible to delete the links to the tables, then import

    > the
    > > tables, make the change, and then split the database again? Again, I
    > > apologize for omitting the main issue of linked tables and again thank you
    > > all for your replies.
    > >
    > > RandyM
    > >
    > > "Allen Browne" wrote:
    > >
    > > > This is a one-time execute that will change the size of the text field

    > from
    > > > what it is now to 2 characters. You say it is less than 2, so there will

    > be
    > > > no loss of data.
    > > >
    > > > If it seems too difficult to do by code, you could have them open the

    > table
    > > > in design view, select the field, and change the Field Size in the lower
    > > > pane.
    > > >
    > > > --
    > > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > > Tips for Access users - http://allenbrowne.com/tips.html
    > > > Reply to group, rather than allenbrowne at mvps dot org.
    > > >
    > > > "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > > > news:EB602CD9-8823-4A42-9A71-B03A10BA5E9E@microsoft.com...
    > > > > Allen, thanks for replying. While I am getting more at ease with

    > Access, I
    > > > > still consider myself a 'newbie' and as such I'm not sure I understand

    > how
    > > > > to
    > > > > use what you've given me. Is this something that's going to have to be
    > > > > executed each time the database is open, or is it a one time thing

    > which
    > > > > is
    > > > > what I'm after? And, where would I put it? I'd like to be able to do

    > some
    > > > > kind of conversion on the old data and make it reflect the field size
    > > > > change.
    > > > > Would have been so much easier had they told me upfront about this,

    > but
    > > > > such
    > > > > is life. Thanks again for your reply and if you could explain it a bit
    > > > > further, I'd really appreciate it.
    > > > >
    > > > > RandyM
    > > > >
    > > > > "Allen Browne" wrote:
    > > > >
    > > > >> Try this kind of thing:
    > > > >>
    > > > >> Dim strSql As String
    > > > >> strSql = "ALTER TABLE MyTable IN 'C:\MyFolder\MyFile.mdb' " & _
    > > > >> "ALTER COLUMN MyField TEXT(2);"
    > > > >> dbEngine(0)(0).Execute strSql, dbFailOnError
    > > > >>
    > > > >> If you can't get that to work you OpenDatabase() on the back end, and
    > > > >> Execute on that.
    > > > >>
    > > > >> "WCDoan" <WCDoan@discussions.microsoft.com> wrote in message
    > > > >> news:948E32C2-441C-4074-BD0A-78FF59C09C54@microsoft.com...
    > > > >> >I have just been informed that the database I created for entering

    > yard
    > > > >> >sale
    > > > >> > license info has to be changed. This is going to involve increasing

    > the
    > > > >> > size
    > > > >> > of a text field from 1 to 2. Data has already been entered with the
    > > > >> > field
    > > > >> > being 1. How do I make the required change and apply it to the data
    > > > >> > that's
    > > > >> > already been entered? Thanks in advance for any help on this.
    > > > >> > RandyM
    > > >
    > > >
    > > >

    >
    >
    >
     

Share This Page