Solved

Extract queried tables from SQL to Access

Posted on 2012-03-15
5
300 Views
Last Modified: 2012-03-20
I need to extract some data from our SQL server to an access mdb. However, it needs to be only the stuff that was edited today. I have the queries for getting the info, and it all works in a normal SQL query, but when I put it into the export wizard as a custom script, it only does the first table. What am I doing wrong? Below is the script that I'm using.

Thanks
BA

SELECT cIns.* FROM Install AS Ins inner join cInstall AS cins ON Ins.UID = cins.MasterUID Where (Ins.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Ins.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
SELECT cIss.* FROM Issue AS Iss inner join cIssue AS cIss ON Iss.UID = cIss.MasterUID Where (Iss.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Iss.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Classes Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Code Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
SELECT cSer.* FROM Service AS Ser inner join cService AS cSer ON Ser.UID = cSer.MasterUID Where (Ser.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Ser.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Cust Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustCont Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustEQTrans Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustEquip Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Defaults Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Install Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenGroup Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenSnap Where (Snapshotdate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Snapshotdate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Inventory;
select * from InvenTrans Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Invoice Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Issue Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from IssueStatus Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Location Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Maint Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from MaintEQ Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from MatDetail  Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderMat Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderStatus Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Paydetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Payment Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from PayMethod Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from PayTypes;
select * from Service Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from ServiceItem  Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from ShipMeth Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SLACategory  Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPLink  Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPLinkSetup Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPSystem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPUser Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Terms Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Vehicle Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VendItem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Vendor Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VFuel Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VMaint Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Zones Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
0
Comment
Question by:bachopper
  • 2
5 Comments
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
You can do a few diffent things depending on what/how the data is:


Is all the data the same just from different sources?  If so you can do UNION on all the statements to get them all at once.

Or create a seperate export for each query.

Use a View do combine them all then export the view results

Populate a temp/working table then just export that table

If you have ability to use SSIS (or DTSX) you can use that (a more advanced version of Export Wizard) and actually export wizard will generate 1 of the 2 files (SSIS or DTSX) and let you save it.
0
 

Author Comment

by:bachopper
Comment Utility
It's from the same database, different tables. And I am using SSIS, but I can't seem to find in the wizard where to customize the data I want, other than the "Use a custom script" section.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
but when I put it into the export wizard as a custom script, it only does the first table. What am I doing wrong?
You need to do each one separately.
0
 

Author Closing Comment

by:bachopper
Comment Utility
Not the answer I was looking for, but I guess it'll have to do.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now