none
Access to another database RRS feed

  • Question

  • Hello,

    how do i have to adapt this code, when New Customer is a table of a database in another path and database.

    Thank you!

    Martin

    Sub InsertIntoX1() 
     
        Dim dbs As Database 
     
        ' Modify this line to include the path to Northwind 
        ' on your computer. 
        Set dbs = OpenDatabase("Northwind.mdb") 
         
        ' Select all records in the New Customers table  
        ' and add them to the Customers table. 
        dbs.Execute " INSERT INTO Customers " _ 
            & "SELECT * " _ 
            & "FROM [New Customers];" 
             
        dbs.Close 
     
    End Sub

    Friday, March 23, 2018 10:29 AM

Answers

  •     dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
            & "SELECT * " _
            & "FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Hi krugi,

    Spaces at the end of the line are very hard to recognize. You can better place them at the beginning of the line for far better readablity:

    dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
              & " SELECT *" _
              & " FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Imb.

    • Marked as answer by krugi Monday, March 26, 2018 7:30 AM
    Friday, March 23, 2018 4:19 PM

All replies

  • Let's say your database is named MyDatabase.accdb, residing in the folder C:\Access.

    Change the line

        Set dbs = OpenDatabase("Northwind.mdb")

    to

        Set dbs = OpenDatabase("C:\Access\MyDatabase.accdb")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, March 23, 2018 1:23 PM
  • Try the following (you will need to change the path):

        dbs.Execute " INSERT INTO Customers " _ 
            & "SELECT * " _ 
            & "FROM [MS Access;DATABASE=C:\My Documents\DatabaseName.mdb;].[New Customers]" 
    
    I'm assuming the "other" database is Microsoft Access, but if not then the connection string will need to be changed.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 23, 2018 1:31 PM
  • Sorry, there is still an syntax error in the sql statement

        dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
            & "SELECT * " _
            & "FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Any idea?

    Thank you!

    Friday, March 23, 2018 1:43 PM
  • Yes, you are missing a space between the INSERT INTO table name and "SELECT". See my example.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 23, 2018 2:07 PM
  •     dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
            & "SELECT * " _
            & "FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Hi krugi,

    Spaces at the end of the line are very hard to recognize. You can better place them at the beginning of the line for far better readablity:

    dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
              & " SELECT *" _
              & " FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Imb.

    • Marked as answer by krugi Monday, March 26, 2018 7:30 AM
    Friday, March 23, 2018 4:19 PM
  •     dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
            & "SELECT * " _
            & "FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Hi krugi,

    Spaces at the end of the line are very hard to recognize. You can better place them at the beginning of the line for far better readablity:

    dbs.Execute " INSERT INTO tblDA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV" _
              & " SELECT *" _
              & " FROM [MS Access;DATABASE=C:\AZ_DATEN\FIP2018\DatenExport\FIPExport.accdb].[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV]

    Imb.

    Of course the example I posted, which was based upon the example in the OPs original question, would have worked just fine if had been copied properly to begin with.  

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 26, 2018 2:28 PM