Creating a record in another database

[Guest]

Hello,

I would like to know how tomodify the following code so that instead of writing to the specified table in the current database it would write the information to an external database c:\delete.mdb containing a table of the same name. I want tomove the table so that is not included as part of the main database.

**********
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
On Error GoTo Deal_Error:
Dim User As String, Table As String, Record As String, Dwg As String, Issue As String
Dim When As Date

User = CurrentUser()
When = Date
Record = Me.Id
Dwg = Me.Drawing_Number
Issue = Me.Issue
Table = "Drawing History Tbl"

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("CoordMemo Tbl")
rst.AddNew
rst("Who") = User
rst("When") = When
rst("Table") = Table
rst("Record") = Record
rst("Dwg") = Dwg
rst("Issue") = Issue
rst.Update
rst.Close

Exit_Error:
Exit Sub

Deal_Error:
GoTo Exit_Error

End Sub
***************

Thank you as always,

Daniel

 

[Marshall Barton]

I would like to know how tomodify the following code so that instead of writing to the specified table in the current database it would write the information to an external database c:\delete.mdb containing a table of the same name. I want tomove the table so that is not included as part of the main database.

**********
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
On Error GoTo Deal_Error:
Dim User As String, Table As String, Record As String, Dwg As String, Issue As String
Dim When As Date

User = CurrentUser()
When = Date
Record = Me.Id
Dwg = Me.Drawing_Number
Issue = Me.Issue
Table = "Drawing History Tbl"

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("CoordMemo Tbl")
rst.AddNew
rst("Who") = User
rst("When") = When
rst("Table") = Table
rst("Record") = Record
rst("Dwg") = Dwg
rst("Issue") = Issue
rst.Update
rst.Close

Why not just link to the table in the archive mdb file?

If necessary, you can open the recordset on an SQL statement
that uses an IN clause (see Help for details) to specify the
archive MDB file:

strSQL = "SELECT * " _
& "FROM [CoordMemo Tbl] IN ""C:\path\file"" " _
& "WHERE Record = 0"
Set rst = CurrentDb.OpenRecordset(strSQL)

I added the WHERE clause to the query so that you don't
waste time and resources retrieving all the existing records
in the archive file. Check to make sure that I used the
right name for the PK field.