none
Connection from one database to another database

    Question

  • Hi All,

    I have 2 Access 2010 Databases one called "QA.accdb" this database holds data in a table called "LTM Form".

    in the second database I would like by pressing a button to connect to the "QA.accdb" database and from the form "LTM Form" I would like to retrieve conditionally the data.

    if I should wright it in SQL language it would be something like this:

    Select * from LTM Form (in the QA database) where [Agent Name] = Jesper Jespersen (and enter the data received in table in the current database called "LTM data")

    Hope some one understand what I am trying to do here and who know the answer.

    Thanks in advance.

    Jesper


    Thanks Jesper


    • Edited by cezar1308 Friday, July 05, 2013 5:33 PM
    Friday, July 05, 2013 5:29 PM

Answers

  • I have 2 Access 2010 Databases one called "QA.accdb" this database holds data in a table called "LTM Form".

    in the second database I would like by pressing a button to connect to the "QA.accdb" database and from the form "LTM Form" I would like to retrieve conditionally the data.

    if I should wright it in SQL language it would be something like this:

    Select * from LTM Form (in the QA database) where [Agent Name] = Jesper Jespersen (and enter the data received in table in the current database called "LTM data")

    Hope some one understand what I am trying to do here and who know the answer.

    Is "LTM Form" a form or a table?  You referred to it both ways.

    Assuming it's a table, you can refer to it and the database containing it in a query using a syntax like this:

        SELECT * FROM [C:\Your\Path\To\QA.accdb].[LTM Form]
        WHERE [Agent Name] = "Jesper Jesperson"

    Of course, you have to change the path in the example to the correct path to QA.accdb.

    If you want to append the selected records to the local table [LTM data] then you would make an append query like this:

        INSERT INTO [LTM data]
        SELECT * FROM [C:\Your\Path\To\QA.accdb].[LTM Form]
        WHERE [Agent Name] = "Jesper Jesperson"

    That assumes that the two tables have the same fields in the same order, and that you want to copy all the fields.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by cezar1308 Friday, July 05, 2013 8:18 PM
    Friday, July 05, 2013 5:53 PM

All replies

  • You cannot 'Select * from LTM Form', only from a table or query. 

    First thing is to link the second database table using EXTERNAL DATA - ACCESS - browse to find the database and table.  Clink on the button for linking.

    Post the table and field names where the data is to be copied from?  

    Post the table and field names where the data is to be copied to?  


    Build a little, test a little

    Friday, July 05, 2013 5:48 PM
  • I have 2 Access 2010 Databases one called "QA.accdb" this database holds data in a table called "LTM Form".

    in the second database I would like by pressing a button to connect to the "QA.accdb" database and from the form "LTM Form" I would like to retrieve conditionally the data.

    if I should wright it in SQL language it would be something like this:

    Select * from LTM Form (in the QA database) where [Agent Name] = Jesper Jespersen (and enter the data received in table in the current database called "LTM data")

    Hope some one understand what I am trying to do here and who know the answer.

    Is "LTM Form" a form or a table?  You referred to it both ways.

    Assuming it's a table, you can refer to it and the database containing it in a query using a syntax like this:

        SELECT * FROM [C:\Your\Path\To\QA.accdb].[LTM Form]
        WHERE [Agent Name] = "Jesper Jesperson"

    Of course, you have to change the path in the example to the correct path to QA.accdb.

    If you want to append the selected records to the local table [LTM data] then you would make an append query like this:

        INSERT INTO [LTM data]
        SELECT * FROM [C:\Your\Path\To\QA.accdb].[LTM Form]
        WHERE [Agent Name] = "Jesper Jesperson"

    That assumes that the two tables have the same fields in the same order, and that you want to copy all the fields.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by cezar1308 Friday, July 05, 2013 8:18 PM
    Friday, July 05, 2013 5:53 PM
  • Hi Dirk,

    You are right it is a little confusing with the name but it is a table and I thing that I have now found a way to solve my issue.

    Sometimes it is just a little push in the right direction that gets one all the way.

    I can make a query with the SQL that you mentioned:

    INSERT INTO [LTM data]
    SELECT * FROM [C:\Your\Path\To\QA.accdb].[LTM Form]
    WHERE [Agent Name] = "Jesper Jesperson"

    I just need a way to make an empty table with the correct fields in it and I know how to make this from SQL, and one SQL to delete the table incase it is already there.

    then I just need to run them in this order:

    Delete Table [LTM data]

    Create Table [LTM data], with all the correct fields in it.

    and last get data SQL (the one you gave me)

    Thanks Dirk  


    Thanks Jesper

    Friday, July 05, 2013 6:51 PM
  • I just need a way to make an empty table with the correct fields in it and I know how to make this from SQL, and one SQL to delete the table incase it is already there.

    then I just need to run them in this order:

    Delete Table [LTM data]

    Create Table [LTM data], with all the correct fields in it.

    and last get data SQL (the one you gave me)

    Why not simply empty the existing Table [LTM data] (DELETE * FROM [LTM data]) which retains the Table Structure and then run the INSERT INTO SQL (Dirk's) to add the refreshed records to the same Table?


    Van Dinh


    • Edited by Van DinhMVP Friday, July 05, 2013 7:12 PM Typos
    Friday, July 05, 2013 7:11 PM
  • Hi Van,

    Good idea this have been implemented.

    Thanks


    Thanks Jesper

    Friday, July 05, 2013 8:18 PM
  • Hi again,

    I can now pull data from the "server" database to the client database as follows:

    Private Sub CMDget_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("H:\desktop\QA db Client.accdb")
    dbs.Execute "INSERT INTO [OSTM form] SELECT * FROM [C:\QA.accdb].[OSTM Form] WHERE [Agent Name]=""Jesper Jespersen"";"
    dbs.Close

    End Sub

    My next problem is that when the person in this case Jesper Jespersen aka me have gotten the data from the "server" (C:\QA.accdb) in to the "client" ("H:\desktop\QA db Client.accdb") I would like Jesper Jespersen to acknowledge that the data is read and understood.

    So far I have the following but it is not working:

    Private Sub CMDsend_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\QA.accdb")
    dbs.Execute "INSERT INTO [C:\QA.accdb].[OSTM form] SELECT * FROM [OSTM Form];" 'WHERE [acknowledgement]=""yes"";"
    dbs.Close
       
    End Sub

    Acknowledgement is at this point a text field.

    Thanks in advance.


    Thanks Jesper

    Tuesday, July 09, 2013 7:33 AM
  • My next problem is that when the person in this case Jesper Jespersen aka me have gotten the data from the "server" (C:\QA.accdb) in to the "client" ("H:\desktop\QA db Client.accdb") I would like Jesper Jespersen to acknowledge that the data is read and understood.

    So far I have the following but it is not working:

    Private Sub CMDsend_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\QA.accdb")
    dbs.Execute "INSERT INTO [C:\QA.accdb].[OSTM form] SELECT * FROM [OSTM Form];" 'WHERE [acknowledgement]=""yes"";"
    dbs.Close
       
    End Sub

    If you already set the dbs as the "remote" server then you should not need to use the full path to the database. You can simply run the SQL as a normal Append SQL.

    Try:

    Private Sub CMDsend_Click()
      Dim dbs As Database
      Set dbs = OpenDatabase("C:\QA.accdb")
      dbs.Execute "INSERT INTO [OSTM form] (list of Fields) " & _
      " VALUES (list of values corresponding to the list of Field)", dbFailOnError
    dbs.Close
        
    End Sub

    I got no ideas what are the Fields & the values you want to insert to the generic lists above.  Note that you need to use approriate delimiters for each Fields.

    If you need help, please post the Fields + the data-types and the value you need to insert for each Field.


    Van Dinh


    • Edited by Van DinhMVP Tuesday, July 09, 2013 11:26 AM Typos
    Tuesday, July 09, 2013 11:25 AM
  • I can now pull data from the "server" database to the client database as follows:

    Private Sub CMDget_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("H:\desktop\QA db Client.accdb")
    dbs.Execute "INSERT INTO [OSTM form] SELECT * FROM [C:\QA.accdb].[OSTM Form] WHERE [Agent Name]=""Jesper Jespersen"";"
    dbs.Close

    End Sub

    Which of these databases are you considering to be the server, and which the client?  In which code is the code running?  If the code is running inside the database "H:\desktop\QA db Client.accdb", then there is no reason to open a separate Database object on that database, and you can simplify it to:

    Private Sub CMDget_Click()
    
        CurrentDb.Execute _
            "INSERT INTO [OSTM form] SELECT * FROM [C:\QA.accdb].[OSTM Form] WHERE [Agent Name]='Jesper Jespersen'", _
            dbFailOnError
    
    End Sub

    I recommend using the dbFailOnError option when executing action queries via the .Execute method, so that an error is raised if the SQL statement fails.

    My next problem is that when the person in this case Jesper Jespersen aka me have gotten the data from the "server" (C:\QA.accdb) in to the "client" ("H:\desktop\QA db Client.accdb") I would like Jesper Jespersen to acknowledge that the data is read and understood.

    So far I have the following but it is not working:

    Private Sub CMDsend_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\QA.accdb")
    dbs.Execute "INSERT INTO [C:\QA.accdb].[OSTM form] SELECT * FROM [OSTM Form];" 'WHERE [acknowledgement]=""yes"";"
    dbs.Close
       
    End Sub

    Like Van, I don't think there's any need to explicitly name QA.accdb if you are executing the statement from a database object that is open on that database.  On the other hand, if this code is executing in the client database and you just want to do a quick update of the server database QA.accdb, you can skip the business of explicitly opening a separate database object and write something like this:

    Private Sub CMDsend_Click()
    
        CurrentDb.Execute _
            "INSERT INTO [C:\QA.accdb].[OSTM form] (acknowledgement) VALUES('yes')", _
            dbFailOnError
    
    End Sub

    You may well have to include other fields in the field list that (in the code above) currently only includes the field [acknowledgement], and include values for those fields in the VALUES list.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Tuesday, July 09, 2013 1:20 PM
  • Hi Drk and Van,

    Slight correction I have changed the design a little as I think it would me easier to manage this way.

    The fields that I would like to "transfer" from the client ("H:\desktop\QA db Client.accdb") table [OSTM form] is:

    Field name {Data type}

    Case ID {Number}

    Acknowledgement {Yes/No}

    Comments {Memo} 

    When the user click button CMDsend I would like to send the above 3 fields value to the "server" database ("C:\QA.accdb") table [ClosedOSTM]

    At the time the user would click on the button all the 3 fields would be on the current open form.


    Thanks Jesper

    Tuesday, July 09, 2013 7:47 PM
  • In this case, change my statement to:

    dbs.Execute "INSERT INTO [OSTM form] " & _
      " ([Case ID], [Acknowledgement], [Comments]) " & _
      " VALUES (" & _
      Forms!YourForm!txtCaseID & ", " & _
      Forms!YourForm!txtCaseID & ", " & _
      """" & Forms!YourForm!txtComments & """)", _
      dbFailOnError


    Van Dinh

    Tuesday, July 09, 2013 11:43 PM
  • Hi again,


    I found that the only way I could get this to work was to first simplify the process by going back to the original syntax of INSERT INTO

    Private Sub CMDsend_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\QA.accdb")

    dbs.execute "INSERT INTO [ClosedOSTM] (CaseID, Acknowledgement, Comments) VALUES ('123','-1','Test');", dbFailOnError"

    End Sub

    And since this was working I found that I needed to make a string since I have 3 field values that needs to be concatenate with ' and , 's this was causing me a lot of head scratching.

    This is what I fond as a working solution:

    Private Sub CMDsend_Click()

    Dim dbs As Database
    Dim val As String

    val = " INSERT INTO [ClosedOSTM] (CaseID, Acknowledgement, Comments) VALUES (" & "'" & Me.TxtCaseID & "' ," & "'" & Me.ChkAck & "' ," & "'" & Me.txtCom & "'" & ") ;"

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

    dbs.Execute val, dbFailOnError

    End Sub

    This may not be the most elegant way of doing it but ti is working and since I only have 3 fields I can live with it.

    Thanks for the help and good ideas.


    Thanks Jesper

    Thursday, July 11, 2013 7:41 AM
  • val = " INSERT INTO [ClosedOSTM] (CaseID, Acknowledgement, Comments) VALUES (" & "'" & Me.TxtCaseID & "' ," & "'" & Me.ChkAck & "' ," & "'" & Me.txtCom & "'" & ") ;"

    This may not be the most elegant way of doing it but ti is working and since I only have 3 fields I can live with it.

    Thanks for the help and good ideas.


    Thanks Jesper

    Hi Jesper,

    I think it IS a elegant way to use a string variable. That is the way I normally work.

    To solve the problem of the quotes, I use a simple function for text-fields (another for dates and reals). Your variable would then read as:

     val = " INSERT INTO [ClosedOSTM] (CaseID, Acknowledgement, Comments) VALUES (" & As_text(Me.TxtCaseID) & "," & As_text(ChkAck) & "," & As_text(Me.txtCom) & ") ;"

    The function As_text is placed in a general module, and just adds a single quote before and after the text_value, and doubles eventual single quotes within the text value, as in:

    Function As_text(cur_text As String) As String
      As_text = "'" & Replace(cur_text,"'","''") & "'"
    End Function

    At least, it enhances the readability.

    Imb.

    Thursday, July 11, 2013 8:13 AM