Results 1 to 4 of 4

Thread: export data

  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Answered: export data

    good afternoon, i'm a new user and trying to learn as I build. I have a DB set up for daily operations and another set up to run reports for the management. I tried linking these together but I had some problems with slow networks and such. is there a way to set up a saved export to happen automatically when a form is opened? my thought is have a middle DB for nothing but data transfer, when the daily operations database is closing down, export to the transfer DB and when the management wants to view the data and open their DB they can open a page that would have the same action but an import instead of an export. is this possible and can someone point me in a direction to get this accomplished? thanks

  2. Best Answer
    Posted by Sinndho

    "You're welcome!

    There are several good books about programming with Access (VBA and SQL) as well as about databases in general. For a start, search in the catalogs of Microsoft Press (https://www.microsoftpressstore.com/...access&x=0&y=0) and Sybex (http://eu.wiley.com/WileyCDA/Section...soft%20access). MSDN also is an invaluable source of knowledge (https://msdn.microsoft.com/en-us/lib...fice.14).aspx).

    As far as the specific problem of exporting data to another Access database is concerned, try to reproduce the example I provided (all the necessary queries are included in my post), see how they works and what results they yield. Once you have understood the principles, you'll be able to transpose the solution to your own databases."


  3. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use queries referencing table(s) in an external database. Let's call the original database Source_Db and the intermediate database Middle_Db.

    You'll need to perform 3 kinds of operations:
    a) Insert row(s) into Middle_DB that are in Source_Db but not in Middle_DB (new rows).
    b) Update row(s) in Middle_DB that are different from the matching row(s) in Source_Db.
    c) Delete row(s) from Middle_DB that do not have matching row(s) in Source_Db.

    To be able to perform such operations, you'll need to have a PRIMARY KEY or an IDENTITY (AutoNumber) column. I used an IDENTITY column (Col0) in the following example.

    Note: All queries are run in Source_Db, except the one that creates the table Tbl_1 in the database Middle_Db.

    1 In the Source_Db database, let's create a table Tbl_1 with data in it:

    Code:
    Tbl_1|    Col0  |    Col1     |    Col2     |    Col3
    -----+----------+-------------+-------------+-----------
    Row0 |    1     |    V_R0C1   |    V_R0C2   |    V_R0C3
    Row1 |    2     |    V_R1C1   |    V_R1C2   |    V_R1C3
    Row2 |    3     |    V_R3C1   |    V_R3C2   |    V_R3C3
    Row3 |    4     |    V_R4C1   |    V_R4C2   |    V_R4C3
    a) Create the table:
    Code:
    CREATE TABLE Tbl_1 ( Col0 COUNTER(1,1), Col1 TEXT(10), Col2 TEXT(10), Col3 TEXT(10) );
    b) Insert some data into it:
    Code:
    INSERT INTO Tbl_1 ( Col1, Col2, Col3 ) 
    SELECT * FROM (SELECT 'V_R0C1' AS Col1, 'V_R0C2' AS Col2, 'V_R0C3' AS Col3 FROM MSysObjects UNION
                   SELECT 'V_R1C1' AS Col1, 'V_R1C2' AS Col2, 'V_R1C3' AS Col3 FROM MSysObjects UNION
                   SELECT 'V_R2C1' AS Col1, 'V_R2C2' AS Col2, 'V_R2C3' AS Col3 FROM MSysObjects UNION
                   SELECT 'V_R3C1' AS Col1, 'V_R3C2' AS Col2, 'V_R3C3' AS Col3 FROM MSysObjects);
    2 Let's create the same table in the Middle_Db (this query must be run in Middle_Db):
    Code:
    CREATE TABLE Tbl_1 ( Col0 COUNTER(1,1), Col1 TEXT(10), Col2 TEXT(10), Col3 TEXT(10) );
    3 To transfer data from Source_Db.Tbl_1 to Middle_Db.Tbl_1, we can use:
    Code:
    INSERT INTO  Tbl_1 ( Col1, Col2, Col3 ) IN 'U:\Access\Middle_Db.mdb'
    SELECT Tbl_1.Col1, Tbl_1.Col2, Tbl_1.Col3
    FROM Tbl_1;
    The contents of Tbl_1 is now the same in both Source_Db and Middle_Db.

    4 Let's now insert a row in Source_Db.Tbl_1:
    Code:
    INSERT INTO Tbl_1 ( Col1, Col2, Col3 ) 
    VALUES ( 'V_R4C1', 'V_R4C2', 'V_R4C3' );
    5 To tranfer the new row into Middle_Db.Tbl_1, we can use:

    a) Create a Query (Qry_1) to "see" the IDENTITY column from Middle_Db.Tbl_1 in Source_Db
    Code:
    SELECT Col0 FROM Tbl_1 IN 'U:\Access\Middle_Db.mdb';
    b) We can now join Tbl_1 and Qry_1 to find the extra column and intert it in Middle_Db.Tbl_1:
    Code:
    INSERT INTO Tbl_1 ( Col1, Col2, Col3 ) IN 'U:\Access\Middle_Db.mdb'
    SELECT Col1, Col2, Col3 FROM Tbl_1
    WHERE Tbl_1.Col0 IN (SELECT Tbl_1.Col0
                         FROM Tbl_1 LEFT JOIN Qry_1 ON Tbl_1.Col0 = Qry_1.Col0
                         WHERE Qry_1.Col0 Is Null
                        );
    6 Let's now change a row in Source_Db.Tbl_1:
    Code:
    UPDATE Tbl_1 SET Tbl_1.Col2 = 'New Value'
    WHERE Tbl_1.Col0=2;
    7 to Save the change(s) in Middle_Db.Tbl_1, we can:

    a) Create a Query (Qry_2) to "see" the data from Middle_Db.Tbl_1 in Source_Db
    Code:
    SELECT Tbl_1.Col0, Tbl_1.Col1, Tbl_1.Col2, Tbl_1.Col3
    FROM Tbl_1 IN 'U:\Access\Middle_Db.mdb';
    b) Update Middle_Db.Tbl_1 through Qry_2:
    Code:
    UPDATE Qry_2 INNER JOIN Tbl_1 ON Qry_2.Col0 = Tbl_1.Col0 
    SET Qry_2.Col1 = [Tbl_1].[Col1], Qry_2.Col2 = [Tbl_1].[Col2], Qry_2.Col3 = [Tbl_1].[Col3];
    8 Let's now delete a row from Source_Db.Tbl_1:
    Code:
    DELETE *
    FROM Tbl_1
    WHERE Tbl_1.Col0=3;
    9 To delete the matching row(s) in Middle_Db.Tbl_1:

    a) We shall use Qry_2 again to "see" the data from Middle_Db.Tbl_1 in Source_Db (see above).

    b) To delete (through Qry_2) row(s) in Middle_Db.Tbl_1 that are not present in Source_Db.Tbl_1:
    Code:
    DELETE *
    FROM Qry_2 LEFT JOIN Tbl_1 ON Qry_2.Col0 = Tbl_1.Col0
    WHERE Tbl_1.Col0 Is Null;
    Have a nice day!

  4. #3
    Join Date
    Oct 2015
    Posts
    2
    holly smoke, thanks sinndho. that's a little over my head but definitely gives me some ideas and something to strive for. where's a good place to learn? so far I've been learning from a little of books, internet, YouTube and forums but its starting to get into the ability to work code and I'm just not comfortable enough yet. I'll keep looking at this and see if I can figure it out.

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    There are several good books about programming with Access (VBA and SQL) as well as about databases in general. For a start, search in the catalogs of Microsoft Press (https://www.microsoftpressstore.com/...access&x=0&y=0) and Sybex (http://eu.wiley.com/WileyCDA/Section...soft%20access). MSDN also is an invaluable source of knowledge (https://msdn.microsoft.com/en-us/lib...fice.14).aspx).

    As far as the specific problem of exporting data to another Access database is concerned, try to reproduce the example I provided (all the necessary queries are included in my post), see how they works and what results they yield. Once you have understood the principles, you'll be able to transpose the solution to your own databases.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •