Results 1 to 8 of 8
  1. #1
    vinayak36 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Location
    mumbai
    Posts
    7

    conditionally importing table data from sql

    Hi,
    Has anyone tried to import data conditionally fro SQL Database to access DB??
    I am Importing data from SQL Server to my Access Database to generate report. But SQL server is having large number of data which will cross limit of 2 GB of Access Database. So, I want to Import data data conditionally from SQL Database, Means Whatever Input I will give to filter Data in Access only that much of data should have to get imported from SQL DB to Access DB. And After completion of that Task Access DB Should have to be Blank. Which means It will not Consume Space of Database.


    Awaiting for Reply...

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Why do you need to import? Just link to the relevant tables or run a stored procedure to limit the records you want to use. Or perhaps a view will work for you?

  3. #3
    vinayak36 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Location
    mumbai
    Posts
    7
    Already Linked with sql database but my access db file size is also increasing as my SQL data is getting added in its database..

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    my question is, why are you adding the data to your access db, why not just reference the linked table when required?

    If you are concerned about the access db bloating, use multiple back ends. But if the situation is that a single table is over or approaching 2Gb, then you need to use a different back end such a s sql server or sql server express.

  5. #5
    vinayak36 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Location
    mumbai
    Posts
    7
    Because access DB is used to generate reports from data which I am getting in SQL database. To generate reports from that data I need to link with SQL server DB which is having all raw data record. Among these record data is getting filtered as per requirement and report is being generated.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    still not answering the question. why do you need to add data to your db? Why do you save the data to report it?

  7. #7
    vinayak36 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Location
    mumbai
    Posts
    7
    I think you didnt understand what I want to ask.. Actually I am generating report of Machine running data. For that I am getting all machine data values in SQL database and machine SCADA software will keep adding new data to this database per second as machine runs. So, This database is going to be increase day by day as machine runs. And I am generating report of that data in Access database. if I Link this database to access DB both will grt synchronized simultaneously. So, whatever data is I am getting in SQL will be reflected in access DB and after some time Or some days or some months File size of that access databasd will cross 2 GB limit. Thats why I want to call that particular data whenever I require and then It should become zero..

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am Importing data from SQL Server to my Access Database to generate report
    As Ajax says, there is no need to do this. Only store data in Access for long-term use, not for display-only purposes. The most efficient method would be to create a pass-thru query which contains only the data that you wish to display on the report. That would need to be created in VBA and would be the quickest to run. Or if you are not comfortable doing that, create a pass-thru query which selects all records and a Select query which uses that PT query and has criteria to filter the data, and then use this query to create your report.

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

Similar Threads

  1. Importing data from Pivot Table?
    By jstoler in forum Import/Export Data
    Replies: 22
    Last Post: 07-11-2013, 06:58 AM
  2. Replies: 5
    Last Post: 12-03-2012, 05:03 PM
  3. Importing excel data into table automatically
    By jwalker55 in forum Import/Export Data
    Replies: 2
    Last Post: 01-10-2012, 07:56 AM
  4. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 AM
  5. Moving data to table conditionally
    By adams.bria in forum Programming
    Replies: 4
    Last Post: 12-06-2011, 08:31 AM

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