Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Pascagoula, MS
    Posts
    6

    Unanswered: How Do I Automatically Create Table Fields?

    I am rather new to using Access. So I do not how to do things the 'easy' way yet. What I have is the following:

    1. I have a database created that has a table (call it Table A for simplicity).

    2. On the network is another database that has a table that i need data from (call it Table B). Table B is updated once a week from data it pulls from a mainframe system. This table has data for two specific Hull numbers, 0707 and 0808.

    3. The following day that Table B is updated, I want to update my Table A by deleteing the current Table A from my database, then creating a new Table A with the same fields. Then bring in data from Table B for only those Hull numbers that are 0808.

    If this involves using a Query, well, I have no idea how to do this. I am a VB programmer by trade and have always done things with 'lots' of code. As of now, I have a buttload of code to do this in Access, but I am thinking that there has to be a more efficient and simpler way of doing this. Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    This should work (you'll need the dao reference library).

    Code:
    Dim dbMainframeTables As Database
    Dim dbLocalTables As Database
    
    'open two database objects:
    ' one that holds the mainframe table
    ' and one that holds the local table
    
    Set dbMainframeTables = OpenDatabase( _
       "\\MyResource\MyFolder\MainframeTables.mdb")
    Set dbLocalTables = OpenDatabase( _
       "\\MyResource\MyFolder\LocalTables.mdb")
    
    'delete table from the local database
    '(don't bother with the error if the table doesn't exist)
    
    On Error Resume Next
    dbLocalTables.TableDefs.Delete "Table A"
    On Error GoTo 0
    dbLocalTables.TableDefs.Refresh
    
    'create a new table, and populate it
    'with data from the mainframe for hull 0707 or 0808
    
    dbLocalTables.Execute _
       "SELECT [Table B].* " & _
       "INTO [Table A] " & _
       "FROM [" & CStr(dbMainframeTables.Name) & "].[Table B] " & _
       "WHERE ((([Table B].[Hull]) In (0707, 0808)));"
    
    'close the database objects
    
    dbLocalTables.Close
    Set dbLocalTables = Nothing
    dbMainframeTables.Close
    Set dbMainframeTables = Nothing

Posting Permissions

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