Insert MS Access form data into another Access table

Question

I have 2 Access database files. Database no. 1 is in my computer and database no. 2 is in the network shared folder.

I create a form in database 1 (in my computer) that inserts form data to table "Tbl_Requests" with following VBA code:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Tbl_Requests")
With rst
.AddNew
.Fields("IDLeave") = Me.Text_IDLeave.Value
.Fields("PersonalCode") = Me.Text_CP.Value
.Fields("FullName") = Me.Text_FullName.Value
.Fields("RequestDate") = Me.Text_RequestDate.Value
.Fields("Section") = Me.Text_Section.Value
.Fields("SuperName") = Me.Text_SuperName.Value
.Fields("LeaveRemained") = Me.Text_LeaveRemained.Value
.Fields("Des") = Me.Text_Des.Value
.Fields("LeaveDate") = Me.Combo_LeaveDate.Value
.Fields("Email") = Me.Text_Email.Value
.Update
End With

Now I want save the same data form to another table in database 2 that is in the network. How can I do this?

Picture Of Question

Parfait · Accepted Answer · 2016-11-16 14:07:01Z

Simply switch out the CurrentDb object and point to networked database file:

Dim db As Database
Dim rst As Recordset 

Set db = OpenDatabase("C:\Path\To\Database.accdb")
Set rst = db.OpenRecordset("Tbl_Requests") 

With rst 
   .AddNew 
   .Fields("IDLeave") = Me.Text_IDLeave.Value 
   .Fields("PersonalCode") = Me.Text_CP.Value 
   .Fields("FullName") = Me.Text_FullName.Value 
   .Fields("RequestDate") = Me.Text_RequestDate.Value 
   .Fields("Section") = Me.Text_Section.Value 
   .Fields("SuperName") = Me.Text_SuperName.Value 
   .Fields("LeaveRemained") = Me.Text_LeaveRemained.Value 
   .Fields("Des") = Me.Text_Des.Value 
   .Fields("LeaveDate") = Me.Combo_LeaveDate.Value 
   .Fields("Email") = Me.Text_Email.Value 
   .Update 
End With

rst.Close
db.Close

Set rst = Nothing
Set db = Nothing

Andre · Accepted Answer · 2016-11-16 12:32:15Z

1

Link TableB from DB2 into DB1.

The linked table can be used (for most use cases) like a local table, your code works without changes, except perhaps the table name.

answered Nov 16, 2016 at 12:32

Andre

27.7k7 gold badges43 silver badges85 bronze badges

1 Comment

Manouchehr Tizkhan Over a year ago

can i do it with vba code and Such as the above codes insert record to table B?

Collectives™ on Stack Overflow

Insert MS Access form data into another Access table

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related