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

x
?
Solved

Help creating my first SQL ViewSQL

Posted on 2013-01-30
6
Medium Priority
?
441 Views
Last Modified: 2013-01-31
This will be my first attempt at setting up an SQL view and I am hoping that someone out there can answer a couple quick questions for me. I need to select data from a SQL table located off-site and bring that data into Microsoft access locally. Will the SQL View code reside offsite where the SQL database resides or is it on my side where my computer and Microsoft access resides?

Our J.D. Edwards ERP system is managed by an off-site company, Titan Technologies Inc. The data that I need will come from the SAL server located at Titan across our data line to our local office here in Newton North Carolina. Could someone give me a very quick blow-by-blow education or point me to a place where I can get the information that I require.

Below, is what I believed to be the correct code for my first SQL View.

CREATE VIEW F4111 AS
SELECT PRODDTA.F4111.ILNLIN, PRODDTA.F4111.ILITM, PRODDTA.F4111.ILLITM, PRODDTA.F4111.ILAITM, PRODDTA.F4111.ILLOCN, PRODDTA.F4111.ILTRNO, PRODDTA.F4111.ILFRTO, PRODDTA.F4111.ILKIT, PRODDTA.F4111.ILMMCU, PRODDTA.F4111.ILKCO, PRODDTA.F4111.ILDOC, PRODDTA.F4111.ILDCT, PRODDTA.F4111.ILJELN, PRODDTA.F4111.ILICU, PRODDTA.F4111.ILGLPT, PRODDTA.F4111.ILDCTO, PRODDTA.F4111.ILDOCO, PRODDTA.F4111.ILKCOO, PRODDTA.F4111.ILLNID, PRODDTA.F4111.ILIPCD, PRODDTA.F4111.ILTRDJ, PRODDTA.F4111.ILTRUM, PRODDTA.F4111.ILAN8, PRODDTA.F4111.ILTREX, PRODDTA.F4111.ILTREF, PRODDTA.F4111.ILTRQT, PRODDTA.F4111.ILUNCS, PRODDTA.F4111.ILUKID, PRODDTA.F4111.ILCRDJ, PRODDTA.F4111.ILAID, PRODDTA.F4111.ILASID, PRODDTA.F4111.ILMCUZ, PRODDTA.F4111.ILOBJ, PRODDTA.F4111.ILSBL, PRODDTA.F4111.ILSUB, PRODDTA.F4111.ILUOM2, PRODDTA.F4111.ILCMOO, PRODDTA.F4111.ILRE, PRODDTA.F4111.ILSBLT, PRODDTA.F4111.ILSQOR, PRODDTA.F4111.ILVPEJ, PRODDTA.F4111.ILHDGJ, PRODDTA.F4111.ILSHAN, PRODDTA.F4111.ILOPSQ, PRODDTA.F4111.ILRFLN, PRODDTA.F4111.ILTGN, PRODDTA.F4111.ILLOTC, PRODDTA.F4111.ILSVDT, PRODDTA.F4111.ILLRCD, PRODDTA.F4111.ILRLOT, PRODDTA.F4111.ILLPNU
FROM PRODDTA.F4111
WHERE (((PRODDTA.F4111.ILMCU) Like '       31101' Or (PRODDTA.F4111.ILMCU) Like '       31110' Or (PRODDTA.F4111.ILMCU) Like '       31120' Or (PRODDTA.F4111.ILMCU) Like '       31130' Or (PRODDTA.F4111.ILMCU) Like '       31140' Or (PRODDTA.F4111.ILMCU) Like '       31150'));
Regards,

Tw
0
Comment
Question by:Tom Winslow
[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
  • 3
  • 3
6 Comments
 
LVL 85
ID: 38837283
Views are stored in the server database. Views are handled just like Tables, in regard to linking, opening via recordsets, etc.

What is it you want to do after building and saving the View on the server? If your goal is to store the data locally, then the best way to do that is to link to the table or view, and then use something like a SELECT INTO statement to save the data into a local table.

To do THAT,, you'd need to build and link the view, and then do this AFTER you've linked the view:

Currentdb.Execute "SELECT * INTO MyLocalTable FROM MyLinkedView."

Note that "MyLocalTable" cannot already exist, so you might need to use a DROP statement first:

Currentdb.Execute "DROP MyLocalTable"
0
 

Author Comment

by:Tom Winslow
ID: 38837453
I need to get the data local into an MS Access database. Should I use a PassThru Query or can I link directly to the View in the same manner that I currently link to the tables?
0
 
LVL 85
ID: 38837479
You could use a PassThrough, but in  general if you need to localize the data a simple linked table with the SELECT or INSERT query works best.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Tom Winslow
ID: 38839728
I am wanting to use Views because:

1. The tables are too large to access all of the needed data directly with an ODBC connection to the table. The view employs filtering for the needed data.
2. Security. The Associate that will be using the data cannot have access to the entire table(s).

Not being familiar with VIEWS, I am assuming that security can be applied to the VIEW.

tw
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38839831
You can treat an SQL View the same as you do a table, so yes you could apply security to a View independent of it's root tables.

However, you're talking apples and oranges when you refer to a PassThrough vs a View.

A PassThrough Query in Access is simply a query that is "passed through" Access to the source database, as defined in the Connect property of that Query. Your PassThrough query could point to a View, or a Table.

A linked View is no different than a linked Table, as far as Access is concerned.

So you could have either one - a linked View, or a PassThrough query that is build using a View on the server.
0
 

Author Closing Comment

by:Tom Winslow
ID: 38839841
Great! All my questions answered. I am off and running.

Thanks.

tw
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

618 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