CODEWITHSUNDEEP
sqlexcelvbams-accessadodb
Kelaref
Kelaref

Reputation: 517

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 ?

Upvotes: 0

Views: 3711

Answers (2)

Rory
Rory

Reputation: 34045

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

Upvotes: 4

geeFlo
geeFlo

Reputation: 375

Instead of setting your db to CurrentDB, use OpenDatabase. 

eg.
    set DB = opendatabase("fkdslfjslj")

Upvotes: 0

Related Questions