ADODB Connection to two access database

Due to ms access 2GB limitations on space; i am considering splitting a database into two.

However I regularly rely on SQL statements such as the following:

INSERT INTO [...] SELECT [...]

Is there a way in ADODB to perform the above, ie INSERT INTO table in file 1, the SELECT data from a table in file 2 ?

Answer

Here’s an example of updating one accdb from another using VBA and ADO:

Sub AddDataFromAccessToAccess()
   Dim cn                As Object
   Dim sDatabase As String
   Dim sDatabase2 As String

   ' change these paths
   sDatabase = "C:Database1.accdb"
   sDatabase2 = "C:Database2.accdb"

   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source='" & sDatabase & "'"
      .Open
      ' change table and field names here
      .Execute "INSERT INTO Table1 ([Field1]) SELECT [Field2] FROM [MS Access;DATABASE=" & sDatabase2 & ";].[Table2]"
      .Close
   End With

End Sub