You can use the TransferDatabase method to export a table, e.g. to export a
table Addresses as structure only:
DoCmd.TransferDatabase acExport,"Microsoft
Access","F:\SomeFolder\MyDb.mdb",acTable,"Addresses","Addresses",True
To export it with its data omit the final True argument.
However, to add an empty table to the back end you don't need the table in
the front end; you can define it in the code. The following is part of a
function which does this in a Bill of Materials application, creating the
table then linking to it. This also creates the back end database itself,
but it can just as easily be done with an existing back end:
Function ExplodeParts(Assembly As String) As Boolean
' creates parts explosion with aggregated quantities in BoM table.
' accepts name of table with column headings MajorPartNum,
' MinorPartNum and Quantity where MajorPartNum and MinorPartNum
' both reference primary key PartNum of Parts table.
' returns True if parts explosion successfully computed, False otherwise.
Dim dbs As DAO.Database, dbsTemp As DAO.Database, qdf As DAO.QueryDef,
tdf As DAO.TableDef
Dim varFld As Variant
Dim strSQL As String, strSQL1 As String, strSQL2 As String
Dim strSQL3 As String, strSQL4 As String, strSQL5 As String
Dim strSQL6 As String, strSQL7 As String, strSQL8 As String
Dim strSQL9 As String
Dim n As Integer
Set dbs = CurrentDb
' create temporary database in same folder as current database
' or return reference to database if already exists
strTempDb = Left$(dbs.Name, Len(dbs.Name) - Len(Dir(dbs.Name))) &
"BoMTemp.mdb"
On Error Resume Next
Set dbsTemp = CreateDatabase(strTempDb, dbLangGeneral)
If Err <> 0 Then
Set dbsTemp = OpenDatabase(strTempDb)
End If
On Error GoTo Err_Handler
' create BoM table in temporary database if doesn't exist
strSQL = "CREATE TABLE BoM (MajorPartNum LONG NOT NULL, " & _
"MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL, " & _
"CONSTRAINT MajorMinor PRIMARY KEY (MajorPartNum, MinorPartNum) )"
On Error Resume Next
dbsTemp.Execute (strSQL)
On Error GoTo Err_Handler
' create BoM_Temp table in temporary database.
' note that this one has no constraint as it will be
' necessary to add duplicates of major/minor part number
' values in separate rows
strSQL = "CREATE TABLE BoM_Temp (MajorPartNum LONG NOT NULL, " & _
"MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL)"
dbsTemp.Execute (strSQL)
' check if link to BoM_Temp table exists and if not create link
On Error Resume Next
Set tdf = dbs.TableDefs("BoM_Temp")
If Err <> 0 Then
DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb,
acTable, "BoM_Temp", "BoM_Temp"
Else
varFld = tdf.Fields(0)
If Err <> 0 Then
' refresh link if curent link invalid
tdf.Connect = ";DATABASE=" & strTempDb
tdf.RefreshLink
End If
End If
Err.Clear
' check if link to BoM table exists and if not create link
Set tdf = dbs.TableDefs("BoM")
If Err <> 0 Then
DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb,
acTable, "BoM", "BoM"
Else
varFld = tdf.Fields(0)
If Err <> 0 Then
' refresh link if current link invalid
tdf.Connect = ";DATABASE=" & strTempDb
tdf.RefreshLink
End If
End If
Err.Clear
On Error GoTo Err_Handler
'''code continues'''
You can add a field to a table with the ALTER TABLE statement, e.g. the
following adds a text field STREET of 50 characters maximumn length to a
table Addresses:
ALTER TABLE Addresses
ADD COLUMN Street TEXT(50);
To add a field to a table in the back end you'd execute the above statement
in the same way as is done in the above example for creating the tables.
Ken Sheridan
Stafford, England
