Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Extract queried tables from SQL to Access

Posted on 2012-03-15
5
Medium Priority
?
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 37726386
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
ID: 37726458
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 1500 total points
ID: 37731681
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
ID: 37737523
Not the answer I was looking for, but I guess it'll have to do.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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