Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using txt box input in VBA

    I have a database (Access 2000) with two tables (tblComplexCase and tblDisenroll) and a query that selects records that are on both tables. I have a form with a button that runs the query, and two buttons that import data for each of the tables. I want to rename the tables when I import new data, so that the old data is not lost. I have text boxes on the form named txtTblName1 and txtTblName2 to receive the new table name. I want to use the text box input to rename the tables.

    The psuedocode would be (I think):
    Select the table (e.g. tlbComplexCase)
    Rename the table as txtTblName.text
    Import the new data into a table named tblComplexCase

    Although I know something about Visual Basic, VBA in Access always stumps me. Can someone point me in the right direction?



    <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using txt box input in VBA

    The first place to point you is away from what you're trying to do. Saving a bunch of tables so you know what was there before is spreadsheet design, not database.

    You should have fields in your tables that include a date created or some other means of telling when the data was added. If you both import data and enter it directly into the tables, you may need a field to tell you whether the record resulted from an import. That is all you need to get back to what was there before when you append new records to the table.

    Creating a bunch of "snapshot" tables will cause major bloat to your database and become impossible to manage, since no interface will be able to keep track of which table you need to look at. It will also prevent you from creating relationships between the tables and enforcing referential integrity, since the relationships migrate when you change table names. Creating a new table with the old name will NOT automagically duplicate the relationships.
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using txt box input in VBA

    Charlotte, thanks for your input. If I understand you correctly, you are advising me to append the new data to the old table with a field for the date or some other means of identifying the new data. These files I am importing are very large, so I think I will just keep them archived in Excel and forget about changing the table name. Again, thanks.

Posting Permissions

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