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.
This should work (you'll need the dao reference library).
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( _
Set dbLocalTables = OpenDatabase( _
'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
'create a new table, and populate it
'with data from the mainframe for hull 0707 or 0808
"SELECT [Table B].* " & _
"INTO [Table A] " & _
"FROM [" & CStr(dbMainframeTables.Name) & "].[Table B] " & _
"WHERE ((([Table B].[Hull]) In (0707, 0808)));"
'close the database objects
Set dbLocalTables = Nothing
Set dbMainframeTables = Nothing