Solved

Extract queried tables from SQL to Access

Posted on 2012-03-15
5
310 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
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 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