Import & export tables based on table data and docmd

  • Thread starter Thread starter CWH via AccessMonster.com
  • Start date Start date
C

CWH via AccessMonster.com

Hi;

I have a number of table transfers (import & export) I want to occur via code
when the fields in a master table are set specifically as such; (To execute
this I am using a command button on a form).

If Field Name “Number†=1 (data type = number) and a checkbox is set to yes
or -1 then

DoCmd.TransferDatabase export, Table1 etc…

If Field Name “Number†=2 and a checkbox is set to yes then

DoCmd.TransferDatabase export, Table2 etc…

And so forth…

This is what I have for code– but of course it doesn’t work

If [tbl_update]![Number] = 1 And [tbl_update]![Check] = -1 Then

DoCmd.TransferDatabase export, acTable, "TableName", "TableName"

Else
End if
Exit Sub
 
CWH,
this is the line that needs fixing:
If [tbl_update]![Number] = 1 And [tbl_update]![Check] = -1 Then

On your form put a textbox control for the field [TheNumberField] and a
textbox control for the field [TheCheckField]

Then change the code to:
If Me.[TheNumberFieldControlName] = 1 And Me.[TheCheckFieldControlName]
= -1 Then

Note: Number is a reserved word in access, and causes problems if used as a
field name in a table.
It is better to use a prefix or suffix with it - for example BeNumber or
NumberBe

Jeanette Cunningham
 
Hi Jeanette;

Thank you. Wondering,,, can I run the command from another form (lets say
Frm1) and if so would I still us the "me."? or simply substitute it with the
Form name that has the appropriate text field names.


I'm trying this but running an error;

If Frm1.[TheNumberFieldControlName] = 1 And Frm1.[TheCheckFieldControlName]
= -1 Then


Thanks
 
To run from another form.
use the syntax
Forms!Frm1
in place of the word
Me


Jeanette Cunningham
 
Hi Jeanette;

Thanks it works. I did once the code stops after the first record and I need
it to contiune through all the records. Can you recommand a simple loop?


Jeanette said:
To run from another form.
use the syntax
Forms!Frm1
in place of the word
Me

Jeanette Cunningham
Hi Jeanette;
Click to expand...
[quoted text clipped - 10 lines]
Thanks
Click to expand...
Click to expand...
 
CWH via AccessMonster.com said:
Hi Jeanette;

Thanks it works. I did once the code stops after the first record and I
need
it to contiune through all the records. Can you recommand a simple loop?


Jeanette said:
To run from another form.
use the syntax
Forms!Frm1
in place of the word
Me

Jeanette Cunningham
Hi Jeanette;
Click to expand...
[quoted text clipped - 10 lines]
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeanette;

Thanks it works. I did once the code stops after the first record and I need
it to contiune through all the records. Can you recommand a simple loop?


Jeanette said:
Hi Jeanette;
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
Create a new table called 'tblExportNames'
--with one text field called 'Tname' for the names of the tables to be
exported
--set allow zero length = No
--make this field required

The following is untested air code

Sub ExportManyTables()
On Error GoTo SubErr
Dim db As DAO.Database
Dim rstMain As DAO.Recordset
Dim strTName As String

Set db = CurrentDb
'Open a recordset on the table tblExportNames
Set rstMain = db.OpenRecordset("tblExportNames")
'make sure at start of table
rstMain.MoveFirst
' Use the recordset as a base
With rstMain
' Process until end of file
Do While Not .EOF

'get the name of the table to export
strTableName = rstMain("TName")
'do the export using strTableName as the object to export
DoCmd.TransferDatabase Export, acTable, "TableName", "TableName"
'etc

rstMain.MoveNext
Loop

SibExit:
' Destroy the recordset and database objects
rstMain.Close
If Not rstMain Is Nothing Then
Set rstMain = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If
Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume FunctionExit
End Sub

Jeanette Cunningham


CWH via AccessMonster.com said:
Hi Jeanette;

Thanks it works. I did once the code stops after the first record and I
need
it to contiune through all the records. Can you recommand a simple loop?


Jeanette said:
To run from another form.
use the syntax
Forms!Frm1
in place of the word
Me

Jeanette Cunningham
Hi Jeanette;
Click to expand...
[quoted text clipped - 10 lines]
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeanette;

As you can see I’ve made a few minor changes to the creation of the table you
mentioned but made sure the table name and fields correspond with the code.

I have a number of tables that should be deleted and replaced when the field
“tblupdate†and ID are equal. In the table I have ID set to “-1â€. When
tblupdate (checkbox) is clicked and indicates -1 in the table both fields are
the same.

Despite this the code is still deleting and replacing all the tables. It
should only delete the tables when ID & tblupdate are equal.

Also the loop continues and is not stopping after going through all the
records.

Thanks for any additional help.


Private Sub Command5_Click()
Dim strFilter As String
Dim strInputFileName As String
Dim Msg As String
Dim db As DAO.Database
Dim rstMain As DAO.Recordset
Dim strtblName As String
On Error GoTo SubErr


strFilter = ahtAddFilterItem(strFilter, "Access File (*.mdb)", "*.mdb")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True,
DialogTitle:="Select RMS Update File...", Flags:=ahtOFN_HIDEREADONLY)

DoCmd.TransferDatabase acImport, "Microsoft Access", strInputFileName,
acTable, "tbl_update", "tbl_update"

Set db = CurrentDb

'Open a recordset on the table tblExportNames
Set rstMain = db.OpenRecordset("tbl_update")
'make sure at start of table
rstMain.MoveFirst
' Use the recordset as a base
With rstMain
' Process until end of file
Do While Not .EOF

'get the name of the table to export
strTableName = rstMain("tblName")
'do the export using strTableName as the object to export etc'

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Certification Type"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Certification Type", "Certification Type"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training - Course List"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training - Course List", "Training - Course List"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "ActivityCodes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "ActivityCodes ", "ActivityCodes "
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Age"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Age", "Age"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Amount"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Amount", "Amount"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Area"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Area", "Area"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Behavior"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Behavior", "Behavior"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Canines"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Canines", "Canines"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Coordinate Datum"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Coordinate Datum", "Coordinate Datum"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Coordinate Display"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Coordinate Display", "Coordinate Display"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Coordinate Profile"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Coordinate Profile", "Coordinate Profile"

End If

'Delete Detection Type (ID) and Type (Detection ID)'
Dim rels As Relations
Dim rel As Relation
Set rels = CurrentDb.Relations
For Each rel In rels
If rel.Table = Detection Or rel.ForeignTable = "Type" Then
rels.Delete rel.Name
End If
Next rel

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Detection Type"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Detection Type", "Detection Type"
End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Distance"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Distance", "Distance"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Evidence Recovery Item Searched"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Evidence Recovery Item Searched", "Evidence
Recovery Item Searched"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Evidence Recovery Search Area"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Evidence Recovery Search Area", "Evidence
Recovery Search Area"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Expense Category"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Expense Category", "Expense Category"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Expense Description"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Expense Description", "Expense Description"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Geographic Areas"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Geographic Areas", "Geographic Areas"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Handlers"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Handlers", "Handlers"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Height"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Height", "Height"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Image Library"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Image Library ", "Image Library "


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "IncidentTypes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "IncidentTypes", "IncidentTypes"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Instructor Contact"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Instructor Contact", "Instructor Contact"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Resource Types"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Resource Types", "Resource Types"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "NameTypes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "NameTypes", "NameTypes"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Search Results - Incident"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Search Results - Incident", "Search Results -
Incident"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Search Results - Training"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Search Results - Training", "Search Results -
Training"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Search Tactics"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Search Tactics", "Search Tactics"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Special Teams"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Special Teams", "Special Teams"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_activity"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_activity", "st_activity"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_age"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_age", "st_age"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_area"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_area", "st_area"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "St_behavior"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "St_behavior", "St_behavior"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_contamination"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_contamination", "st_contamination"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_location"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_location", "st_location"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_profile"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_profile", "st_profile"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_subject"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_subject", "st_subject"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_time"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_time", "st_time"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_weather"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_weather", "st_weather"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_wind"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_wind", "st_wind"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "State"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "State", "State"

End If

'Delete Teams (LinkID) and Team Certification/Course History (LinkID)'
For Each rel In rels
If rel.Table = Teams Or rel.ForeignTable = "Team Certification/Course
History" Then
rels.Delete rel.Name
End If
Next rel

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Teams"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Teams", "Teams"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Towns"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Towns", "Towns"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Track Level"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Track Level", "Track Level"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Classification"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Classification", "Training
Classification"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Exercise Codes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Exercise Codes", "Training Exercise
Codes"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Location"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Location", "Training Location"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Profile"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Profile", "Training Profile"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "TreatmentCodes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "TreatmentCodes", "TreatmentCodes"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Type"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Type", "Type"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Units"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Units", "Units"


End If

rstMain.MoveNext

Loop

SibExit:
' Destroy the recordset and database objects
rstMain.Close
If Not rstMain Is Nothing Then
Set rstMain = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If

Exit Sub

SubErr:
MsgBox err.Description & " " & err.Number
Resume

End With
Exit Sub
End Sub
 
yes
CWH via AccessMonster.com said:
Hi Jeanette;

As you can see I've made a few minor changes to the creation of the table
you
mentioned but made sure the table name and fields correspond with the
code.

I have a number of tables that should be deleted and replaced when the
field
"tblupdate" and ID are equal. In the table I have ID set to "-1". When
tblupdate (checkbox) is clicked and indicates -1 in the table both fields
are
the same.

Despite this the code is still deleting and replacing all the tables. It
should only delete the tables when ID & tblupdate are equal.

Also the loop continues and is not stopping after going through all the
records.

Thanks for any additional help.


Private Sub Command5_Click()
Dim strFilter As String
Dim strInputFileName As String
Dim Msg As String
Dim db As DAO.Database
Dim rstMain As DAO.Recordset
Dim strtblName As String
On Error GoTo SubErr


strFilter = ahtAddFilterItem(strFilter, "Access File (*.mdb)", "*.mdb")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter,
OpenFile:=True,
DialogTitle:="Select RMS Update File...", Flags:=ahtOFN_HIDEREADONLY)

DoCmd.TransferDatabase acImport, "Microsoft Access", strInputFileName,
acTable, "tbl_update", "tbl_update"

Set db = CurrentDb

'Open a recordset on the table tblExportNames
Set rstMain = db.OpenRecordset("tbl_update")
'make sure at start of table
rstMain.MoveFirst
' Use the recordset as a base
With rstMain
' Process until end of file
Do While Not .EOF

'get the name of the table to export
strTableName = rstMain("tblName")
'do the export using strTableName as the object to export etc'

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Certification Type"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Certification Type", "Certification Type"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training - Course List"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training - Course List", "Training - Course
List"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "ActivityCodes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "ActivityCodes ", "ActivityCodes "
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Age"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Age", "Age"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Amount"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Amount", "Amount"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Area"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Area", "Area"
End If

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Behavior"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Behavior", "Behavior"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Canines"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Canines", "Canines"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Coordinate Datum"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Coordinate Datum", "Coordinate Datum"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Coordinate Display"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Coordinate Display", "Coordinate Display"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Coordinate Profile"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Coordinate Profile", "Coordinate Profile"

End If

'Delete Detection Type (ID) and Type (Detection ID)'
Dim rels As Relations
Dim rel As Relation
Set rels = CurrentDb.Relations
For Each rel In rels
If rel.Table = Detection Or rel.ForeignTable = "Type" Then
rels.Delete rel.Name
End If
Next rel

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Detection Type"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Detection Type", "Detection Type"
End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Distance"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Distance", "Distance"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Evidence Recovery Item Searched"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Evidence Recovery Item Searched", "Evidence
Recovery Item Searched"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Evidence Recovery Search Area"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Evidence Recovery Search Area", "Evidence
Recovery Search Area"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Expense Category"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Expense Category", "Expense Category"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Expense Description"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Expense Description", "Expense Description"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Geographic Areas"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Geographic Areas", "Geographic Areas"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Handlers"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Handlers", "Handlers"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Height"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Height", "Height"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Image Library"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Image Library ", "Image Library "


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "IncidentTypes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "IncidentTypes", "IncidentTypes"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Instructor Contact"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Instructor Contact", "Instructor Contact"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Resource Types"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Resource Types", "Resource Types"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "NameTypes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "NameTypes", "NameTypes"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Search Results - Incident"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Search Results - Incident", "Search Results -
Incident"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Search Results - Training"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Search Results - Training", "Search Results -
Training"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Search Tactics"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Search Tactics", "Search Tactics"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Special Teams"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Special Teams", "Special Teams"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_activity"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_activity", "st_activity"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_age"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_age", "st_age"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_area"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_area", "st_area"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "St_behavior"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "St_behavior", "St_behavior"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_contamination"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_contamination", "st_contamination"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_location"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_location", "st_location"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_profile"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_profile", "st_profile"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_subject"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_subject", "st_subject"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_time"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_time", "st_time"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_weather"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_weather", "st_weather"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "st_wind"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "st_wind", "st_wind"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "State"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "State", "State"

End If

'Delete Teams (LinkID) and Team Certification/Course History (LinkID)'
For Each rel In rels
If rel.Table = Teams Or rel.ForeignTable = "Team Certification/Course
History" Then
rels.Delete rel.Name
End If
Next rel

If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Teams"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Teams", "Teams"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Towns"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Towns", "Towns"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Track Level"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Track Level", "Track Level"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Classification"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Classification", "Training
Classification"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Exercise Codes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Exercise Codes", "Training Exercise
Codes"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Location"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Location", "Training Location"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Training Profile"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Training Profile", "Training Profile"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "TreatmentCodes"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "TreatmentCodes", "TreatmentCodes"


End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Type"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Type", "Type"

End If
If ID = tblupdate Then
DoCmd.DeleteObject acTable, "Units"
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, "Units", "Units"


End If

rstMain.MoveNext

Loop

SibExit:
' Destroy the recordset and database objects
rstMain.Close
If Not rstMain Is Nothing Then
Set rstMain = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If

Exit Sub

SubErr:
MsgBox err.Description & " " & err.Number
Resume

End With
Exit Sub
End Sub
Click to expand...
 
Jeanette;

Thats exactly correct. I'm using a form to check off which tables need to be
deleted. More up-to-date tables with the same names will replace them.

Thanks a ton...

Gary






Jeanette said:
CWH,
I'm trying to understand the process you are working on.
Here is my understanding, please correct me where I am wrong.

Using a form, when the checkbox is checked to true, delete the current table
and replace it with a more up to date table of the same name by importing a
table. Are you doing this one record at a time? Is this a continuous form?

To make this code shorter, I suggest going through a list of all the
relevant tables and marking which ones need to be deleted.
When there is a list of all tables to delete, delete them all in one loop.
Then import their replacement tables.

I see the process like this:
Mark all the tables that need deleting ( use the form )
You need a table with a field for name of tables to be checked and a Yes/No
field to mark which ones need to be deleted.
Sounds as if you have a table set up a bit like this already?
Process all the tables to see which ones need to be deleted.
Now you know which ones to delete you can set up a loop to delete all the
tables which are marked as out of date.
When all the tables from above have been deleted, set up a loop to import
the required tables. You can get the names from the list of tables which
were deleted - the table names would be the same.
Does this suit what you are trying to do?

Jeanette Cunningham

CWH via AccessMonster.com said:
Hi Jeanette;
Click to expand...
[quoted text clipped - 406 lines]
Exit Sub
End Sub
Click to expand...
Click to expand...
 
Gary,

Assuming a table with 2 fields called tblDeleteImport
field1: text, TName, Allow Zero Length = No Required = Yes
field2: Yes/No TDel
Warning: the following is untested air code

After your code has marked all the tables that need to be deleted and
replaced, you can use code like this:


Private Sub Command5_Click()
On Error GoTo SubErr
Dim db As DAO.Database
Dim rstMain As DAO.Recordset
Dim strFilter As String
Dim strInputFileName As String
Dim Msg As String
Dim strTableName As String


strFilter = ahtAddFilterItem(strFilter, "Access File (*.mdb)", "*.mdb")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True,
DialogTitle:="Select RMS Update File...", Flags:=ahtOFN_HIDEREADONLY)


Set db = CurrentDb
'Open a recordset to get the names of tables to delete
Set rstMain = db.OpenRecordset("SELECT TName FROM " _
& " tblDeleteImport " _
& "WHERE tblDeleteImport.Tdel = True"")

' Process until end of file
Do Until rstMain .EOF

'get the name of the table to delete
strTableName = rstMain("TName")

'delete the table
DoCmd.DeleteObject acTable, strTableName
'import the table
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, strTableName, strTableName

rstMain.MoveNext
Loop


SubExit:
' Destroy the recordset and database objects
rstMain.Close
If Not rstMain Is Nothing Then
Set rstMain = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If
Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub


Jeanette Cunningham


CWH via AccessMonster.com said:
Jeanette;

Thats exactly correct. I'm using a form to check off which tables need to
be
deleted. More up-to-date tables with the same names will replace them.

Thanks a ton...

Gary






Jeanette said:
CWH,
I'm trying to understand the process you are working on.
Here is my understanding, please correct me where I am wrong.

Using a form, when the checkbox is checked to true, delete the current
table
and replace it with a more up to date table of the same name by importing
a
table. Are you doing this one record at a time? Is this a continuous form?

To make this code shorter, I suggest going through a list of all the
relevant tables and marking which ones need to be deleted.
When there is a list of all tables to delete, delete them all in one loop.
Then import their replacement tables.

I see the process like this:
Mark all the tables that need deleting ( use the form )
You need a table with a field for name of tables to be checked and a
Yes/No
field to mark which ones need to be deleted.
Sounds as if you have a table set up a bit like this already?
Process all the tables to see which ones need to be deleted.
Now you know which ones to delete you can set up a loop to delete all the
tables which are marked as out of date.
When all the tables from above have been deleted, set up a loop to import
the required tables. You can get the names from the list of tables which
were deleted - the table names would be the same.
Does this suit what you are trying to do?

Jeanette Cunningham

CWH via AccessMonster.com said:
Hi Jeanette;
Click to expand...
[quoted text clipped - 406 lines]
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Gary,
there is an extra double quote character on the line marked with asterisk

Set db = CurrentDb
'Open a recordset to get the names of tables to delete
Set rstMain = db.OpenRecordset("SELECT TName FROM " _
& " tblDeleteImport " _
** & "WHERE tblDeleteImport.Tdel = True"") **

this line should be
& "WHERE tblDeleteImport.Tdel = True")

Jeanette Cunningham


Jeanette Cunningham said:
Gary,

Assuming a table with 2 fields called tblDeleteImport
field1: text, TName, Allow Zero Length = No Required = Yes
field2: Yes/No TDel
Warning: the following is untested air code

After your code has marked all the tables that need to be deleted and
replaced, you can use code like this:


Private Sub Command5_Click()
On Error GoTo SubErr
Dim db As DAO.Database
Dim rstMain As DAO.Recordset
Dim strFilter As String
Dim strInputFileName As String
Dim Msg As String
Dim strTableName As String


strFilter = ahtAddFilterItem(strFilter, "Access File (*.mdb)", "*.mdb")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter,
OpenFile:=True,
DialogTitle:="Select RMS Update File...", Flags:=ahtOFN_HIDEREADONLY)


Set db = CurrentDb
'Open a recordset to get the names of tables to delete
Set rstMain = db.OpenRecordset("SELECT TName FROM " _
& " tblDeleteImport " _
& "WHERE tblDeleteImport.Tdel = True"")

' Process until end of file
Do Until rstMain .EOF

'get the name of the table to delete
strTableName = rstMain("TName")

'delete the table
DoCmd.DeleteObject acTable, strTableName
'import the table
DoCmd.TransferDatabase acImport, "Microsoft Access",
strInputFileName, acTable, strTableName, strTableName

rstMain.MoveNext
Loop


SubExit:
' Destroy the recordset and database objects
rstMain.Close
If Not rstMain Is Nothing Then
Set rstMain = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If
Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub


Jeanette Cunningham


CWH via AccessMonster.com said:
Jeanette;

Thats exactly correct. I'm using a form to check off which tables need to
be
deleted. More up-to-date tables with the same names will replace them.

Thanks a ton...

Gary






Jeanette said:
CWH,
I'm trying to understand the process you are working on.
Here is my understanding, please correct me where I am wrong.

Using a form, when the checkbox is checked to true, delete the current
table
and replace it with a more up to date table of the same name by importing
a
table. Are you doing this one record at a time? Is this a continuous
form?

To make this code shorter, I suggest going through a list of all the
relevant tables and marking which ones need to be deleted.
When there is a list of all tables to delete, delete them all in one
loop.
Then import their replacement tables.

I see the process like this:
Mark all the tables that need deleting ( use the form )
You need a table with a field for name of tables to be checked and a
Yes/No
field to mark which ones need to be deleted.
Sounds as if you have a table set up a bit like this already?
Process all the tables to see which ones need to be deleted.
Now you know which ones to delete you can set up a loop to delete all the
tables which are marked as out of date.
When all the tables from above have been deleted, set up a loop to import
the required tables. You can get the names from the list of tables which
were deleted - the table names would be the same.
Does this suit what you are trying to do?

Jeanette Cunningham

Hi Jeanette;

[quoted text clipped - 406 lines]
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Jeanette;

I did notice the double quote & made the change. The code works great...
Thank you.

I run this from a form and would like it to close once the code is finished
running.

Docmd.Close "TheForm". Is it placed after the loop but before the End Sub.


Jeanette said:
Gary,
there is an extra double quote character on the line marked with asterisk

Set db = CurrentDb
'Open a recordset to get the names of tables to delete
Set rstMain = db.OpenRecordset("SELECT TName FROM " _
& " tblDeleteImport " _
** & "WHERE tblDeleteImport.Tdel = True"") **

this line should be
& "WHERE tblDeleteImport.Tdel = True")

Jeanette Cunningham
Gary,
Click to expand...
[quoted text clipped - 109 lines]
Click to expand...
 
Glad to hear you have it working.
To close the form:
DoCmd.Close acForm, Me.Name will close the form that the code is running in.
Place after the loop and before the exit sub

Jeanette

CWH via AccessMonster.com said:
Jeanette;

I did notice the double quote & made the change. The code works great...
Thank you.

I run this from a form and would like it to close once the code is
finished
running.

Docmd.Close "TheForm". Is it placed after the loop but before the End Sub.


Jeanette said:
Gary,
there is an extra double quote character on the line marked with asterisk

Set db = CurrentDb
'Open a recordset to get the names of tables to delete
Set rstMain = db.OpenRecordset("SELECT TName FROM " _
& " tblDeleteImport " _
** & "WHERE tblDeleteImport.Tdel = True"") **

this line should be
& "WHERE tblDeleteImport.Tdel = True")

Jeanette Cunningham
Gary,
Click to expand...
[quoted text clipped - 109 lines]
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top