Hi Mike -
I have attached an example bit of code which I use to work with a second
database. In my case, I do the same as you are looking to - use the
external database as an archive/backup. ( I also use it as a restore
facility to bail my users out of the Oh S**t! scenarios)
You will note (I hope, if you can decipher my code) that I also use a
table in the main database to index the archive tables, and that I use a
template table in the archive database, and just make copies of it.
(just for convenience, really - saved a bit of code).
I don't delete any archive tables (I should - they accumulate), but
using MS Access help I found that is can be done by something like this:
Dim db As database, dbname As String
dbname = "C:\data\msa_dat\db2.mdb"
Set db = DBEngine.Workspaces(0).OpenDatabase(dbname)
db.TableDefs.Delete "table1"
Set db = Nothing
I tested it - works fine.
Hope this helps
John
J. Goddard said:
Hi Mike -
Don't know where my brain was on my last response; I must have left it
at the office.
Anyway, the answer to your question is yes it most certainly is possible
to do what you are looking to do. I have an application at the office
that creates tables in another database all the time - it just connects
to the database when it needs to. And strangely enough, the database I
connect to is an archive/backup database. Sigh....
I don't delete tables in the connected database, but obviously if I can
create them, I can delete them too.
I gather from your requirement that the actual name of a table in the
archive database gives some indication if what is in it - perhaps an
archive date or something.
I will get back to you with some code examples on Monday.
John
Private Sub Save_Tasks_Click()
On Error GoTo Save_Error
Dim Wksp As Workspace, backupdb As Database
Dim datafiledirectory As String, Destination_Table As String
Dim Table_Name As String, rst As Recordset, db As Database
Dim next_sequence As Integer, Record_Count As Integer
Dim count_sql As String, K As Integer, SQL As String
Dim Records_Saved As Variant
'
' Open the backups database as well
'
backup_database = "C:\msa_dat\oa_backups.mdb"
Set Wksp = DBEngine.Workspaces(0)
Set db = CurrentDb
Set backupdb = Wksp.OpenDatabase(backup_database)
'
' From the backups table, get the next sequence number
'
count_sql = "SELECT COUNT (*) AS RECORD_COUNT FROM [BACKUP LIST] WHERE STUDY = '" & Forms![main menu form].LASTPROJ & "' AND T_S_K = 'T';"
Set rst = db.OpenRecordset(count_sql)
If rst!Record_Count = 0 Then
next_sequence = 1
Else
count_sql = "SELECT MAX([BACKUP NUMBER]) AS CNT FROM [BACKUP LIST] WHERE STUDY = '" & Forms![main menu form].LASTPROJ & _
"' AND T_S_K = 'T';"
Set rst = db.OpenRecordset(count_sql)
rst.MoveFirst
next_sequence = rst!cnt + 1
End If
Forms![please wait].Visible = True
Forms![please wait].SetFocus
Forms![please wait]!label3.Caption = "Backing Up Tasks..."
Forms![please wait].Repaint
DoCmd.Hourglass True
'
' Copy the TASK data and instructions into the local tables template1 and template2
'
K = clear_table("template1", ErrMsg)
K = clear_table("template2", ErrMsg)
SQL = "INSERT INTO template1 SELECT * FROM TASKS"
db.Execute SQL
'
' Make a backup copy of the TASK file in the backups database.
'
Table_Name = Forms![main menu form].LASTPROJ & "_T" & next_sequence
DoCmd.CopyObject backup_database, Table_Name, acTable, "tks_master"
'
' Now attach the table we just created as a linked table, so SQL insert can be used
'
DoCmd.TransferDatabase acLink, "Microsoft Access", backup_database, acTable, Table_Name, "x"
SQL = "INSERT INTO X SELECT * FROM TEMPLATE1"
db.Execute SQL
DoCmd.DeleteObject A_TABLE, "X"
'
' Add a record in the backups table, listing this backup
'
Set rst = db.OpenRecordset("backup list", dbOpenDynaset)
With rst
.AddNew
![backup number] = next_sequence
!study = Forms![main menu form]!LASTPROJ
!t_s_k = "T"
![backup date] = Date
![backup time] = Time$
![backup table name] = Table_Name
.Update
.Close
End With
'===============
'
' Make a backup copy of the Duty file in the backups database.
'
Table_Name = Forms![main menu form].LASTPROJ & "_DT" & next_sequence
DoCmd.CopyObject backup_database, Table_Name, acTable, "dutymaster"
'
' Now attach the table we just created as a linked table, so SQL insert can be used
'
DoCmd.TransferDatabase acLink, "Microsoft Access", backup_database, acTable, Table_Name, "x"
SQL = "INSERT INTO X SELECT * FROM duty"
db.Execute SQL
DoCmd.DeleteObject A_TABLE, "X"
'===============
'
' Save the tasks data back to the network directories
'
Forms![please wait]!label3.Caption = "Saving Tasks to Network..."
Forms![please wait].Repaint
datafiledirectory = Trim(Forms![main menu form].[full path name])
'
' Attach the two files for saving, data and duties
'
DoCmd.TransferDatabase acLink, "Microsoft Access", datafiledirectory, acTable, "TASKS", "SAVE DATA"
DoCmd.TransferDatabase acLink, "Microsoft Access", datafiledirectory, acTable, "DUTY", "SAVE DUTY"
'
' Clear the data from these two tables
'
K = clear_table("SAVE DATA", ErrMsg)
K = clear_table("SAVE DUTY", ErrMsg)
SQL = "INSERT INTO [SAVE DATA] SELECT * FROM TASKS"
db.Execute SQL
Records_Saved = db.RecordsAffected
SQL = "INSERT INTO [SAVE DUTY] SELECT * FROM [DUTY]"
db.Execute SQL
'
' Un-attach the two tables for saving
'
DoCmd.DeleteObject acTable, "SAVE DATA"
DoCmd.DeleteObject acTable, "SAVE DUTY"
'
' Update the control markers on the main menu form
'
Forms![main menu form].SAVED_T = True
Forms![main menu form].SAVEDATE_T = Date
Forms![main menu form].SAVETIME_T = Time$
Me![task saved] = True
'
' update the control table
'
control_table.MoveFirst
control_table.Edit
control_table!COUNT_T = Forms![main menu form].COUNT_T
control_table!SAVEDATE_T = Date
control_table!SAVETIME_T = Time$
If save_user_fields Then control_table!SAVEUSER_T = GetUserID(" ")
control_table.Update
'
' Add a record to the operations log database
'
Add_Log_record "Tasks", "Save", Records_Saved
Forms![please wait].Visible = False
DoCmd.Hourglass False
disable_t = -1
save_done = 1
Exit Sub
Save_Error:
If Err.Number = 3027 Or Err.Number = 3073 Then
FormattedMsgBox "Database is read only!@The network database is marked as read only - no updates can be made.@However, a backup copy of the current data has been made on your local C: drive"
Forms![please wait].Visible = False
DoCmd.Hourglass False
'
' Un-attach the three tables for saving
'
DoCmd.DeleteObject acTable, "SAVE DATA"
DoCmd.DeleteObject acTable, "SAVE DUTY"
Else
FormattedMsgBox "Error Number " & Err.Number & "@" & Err.Description & "@", vbCritical, "Error during attempted save"
Forms![please wait].Visible = False
DoCmd.Hourglass False
End If
End Sub
