Results 1 to 15 of 15
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    Can I import excel file via Pass through Query to backend table

    I am using MS Access 2103. I am using pass through query to get PO details. I am using SQL server backend. My query for run fines for one PO or multiple POs. I hard code POs in my query using IN function get po details but I have 5000 POs.



    I have a list of 5000 POs in excel format. I want to run query using this list. My tables are on SQL server I dont know how to import my list into SQL server so that I could run my query.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do you have the ability to create tables on the SQL server and write data to it?
    If so, here is how I would approach it.

    Create a table in SQL server to store all these POs. Then, link this table to Access.
    Use Access to update the list of POs you want to run it for at any point in time (i.e. probably use a Delete Query to delete all the old records in here first, then import the list from Excel).

    Then have your pass-through query reference this SQL table in your query to only include POs that are found in that table.


    By the way, you do not need to post the same question three times in different forums. Just pick the one that seems to make the most sense. Most of us look at ALL the unanswered questions on the entire board (by using the "Unanswered Threads" Quick Link).

  3. #3
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    Do you have the ability to create tables on the SQL server and write data to it?
    If so, here is how I would approach it.

    Create a table in SQL server to store all these POs. Then, link this table to Access.
    Use Access to update the list of POs you want to run it for at any point in time (i.e. probably use a Delete Query to delete all the old records in here first, then import the list from Excel).

    Then have your pass-through query reference this SQL table in your query to only include POs that are found in that table.


    By the way, you do not need to post the same question three times in different forums. Just pick the one that seems to make the most sense. Most of us look at ALL the unanswered questions on the entire board (by using the "Unanswered Threads" Quick Link).
    Thank you very much for reply and sorry for posting in different places. What I understand first I need to create table which I can make it then you want me to use below insert command to load data into it. I have 5000 POs it means I have load all 5000 POs. is there any sql command to import excel file as table ?

    INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you have a list in an Excel file, an easy way would be to link the Excel file in Access as a linked table.
    Then simply use an Append Query in Access to write all those records to your SQL table (which you have also linked in this Access database).
    It doesn't matter exactly where the back-end tables resides (Access, Excel, SQL). If you put (link) them all in your Access database, you can treat them in the same manner as you would inherent Access tables.

  5. #5
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    If you have a list in an Excel file, an easy way would be to link the Excel file in Access as a linked table.
    Then simply use an Append Query in Access to write all those records to your SQL table (which you have also linked in this Access database).
    It doesn't matter exactly where the back-end tables resides (Access, Excel, SQL). If you put (link) them all in your Access database, you can treat them in the same manner as you would inherent Access tables.
    SQL tables are too large about 50 million records. I tried it but it freeze my computer.It will not work.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No, you only need to link the big SQL table to this Access database, just the one you will be creating to hold your 5000 PO codes.

    From my initial reply:
    Create a table in SQL server to store all these POs. Then, link this table to Access.
    Use Access to update the list of POs you want to run it for at any point in time (i.e. probably use a Delete Query to delete all the old records in here first, then import the list from Excel).

  7. #7
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    No, you only need to link the big SQL table to this Access database, just the one you will be creating to hold your 5000 PO codes.

    From my initial reply:
    that is way I am asking how to import file in SQL server ? I have to manually upload 5000 PO by using Insert command after creating table on SQL Server.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, I don't think you understand what I am trying to tell you, so let me try again.

    You have some huge table in SQL that has all your data, and you are trying to select data from based on a list of about 5000 PO numbers. You are trying to create a pass-through query to extract the records you needs (and you need to include these 5000 PO numbers in your criteria). We do NOT need to do anything with this existing SQL data table. Here is what I am proposing:

    1. Create a brand-new single-field table in your SQL database. The field should be named something like "PO Number".
    2. Once you have created this new table, link this table into your Access database (at this point, it should have zero records in it).
    3. Link your Excel table with your PO numbers into Access also.
    4. Create an Access Append Query that writes the PO Numbers from the linked-Excel table to the new linked-SQL table you created in step #1 (yes, you can use Access to import records into a SQL table).
    5. Update your Pass-Through query so that it links your main SQL data table to this new single-record SQL table that lists all the PO Number you want. By linking these two SQL tables on the "PO Number" field, they query will only return the matching records you want.

    Make sense?

  9. #9
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    OK, I don't think you understand what I am trying to tell you, so let me try again.

    You have some huge table in SQL that has all your data, and you are trying to select data from based on a list of about 5000 PO numbers. You are trying to create a pass-through query to extract the records you needs (and you need to include these 5000 PO numbers in your criteria). We do NOT need to do anything with this existing SQL data table. Here is what I am proposing:

    1. Create a brand-new single-field table in your SQL database. The field should be named something like "PO Number".
    2. Once you have created this new table, link this table into your Access database (at this point, it should have zero records in it).
    3. Link your Excel table with your PO numbers into Access also.
    4. Create an Access Append Query that writes the PO Numbers from the linked-Excel table to the new linked-SQL table you created in step #1 (yes, you can use Access to import records into a SQL table).
    5. Update your Pass-Through query so that it links your main SQL data table to this new single-record SQL table that lists all the PO Number you want. By linking these two SQL tables on the "PO Number" field, they query will only return the matching records you want.

    Make sense?
    It really makes sense.. I tried it works fine. You are awesome. Thank you very much for solving my problem. Have a great day.

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome! Glad it all worked out for you.

  11. #11
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    You are welcome! Glad it all worked out for you.
    JoeM one last question once we get the result by running pass through query. I tried to export in excel it takes forever. It is just running and running. Is there any other option to export ?

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you referring to the results of your Pass Through Query?
    What methodology are you using in trying to export them to Excel?
    How many records are being returned?

  13. #13
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    Are you referring to the results of your Pass Through Query?
    What methodology are you using in trying to export them to Excel?
    How many records are being returned?
    Yes result after running pass through query .Details of 5000 PO would be around 50,000 records with items. I am unable to see in navigation button when i hit to last record it freeze. I tried to export through access to "export to excel" but failed. wouldn't be better If I make table of this record ?

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    wouldn't be better If I make table of this record ?
    Might be worth trying.

    Other options include:
    - Instead of using a Pass-Through query, set up the query as a permanent View on the SQL Server (see: http://www.w3schools.com/sql/sql_view.asp)
    - Use a SQL stored Procedure to write the results of your View/Query to a Table (similar to your Make Table Query or Append Query thought). See: http://www.codeproject.com/Articles/...ocedure-in-SQL

    Personally, I always prefer to set up a "shell" table and use an Append Query instead of a Make Table Query, as that ensures that all the fields and properties are set up the way you like. You would just delete all the records from this table each time before writing new records to them.

  15. #15
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by JoeM View Post
    Might be worth trying.

    Other options include:
    - Instead of using a Pass-Through query, set up the query as a permanent View on the SQL Server (see: http://www.w3schools.com/sql/sql_view.asp)
    - Use a SQL stored Procedure to write the results of your View/Query to a Table (similar to your Make Table Query or Append Query thought). See: http://www.codeproject.com/Articles/...ocedure-in-SQL

    Personally, I always prefer to set up a "shell" table and use an Append Query instead of a Make Table Query, as that ensures that all the fields and properties are set up the way you like. You would just delete all the records from this table each time before writing new records to them.
    Thanks a lot , I will try and get back to you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Excel file into a table
    By lucky33 in forum Import/Export Data
    Replies: 2
    Last Post: 09-03-2014, 01:17 PM
  2. Replies: 7
    Last Post: 06-30-2014, 12:11 AM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Replies: 5
    Last Post: 01-29-2013, 06:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums