A better way to query data currently being done with a linked query in Access

Question

A better way to query data currently being done with a linked query in Access

jirish

Grasshopper

Points: 10
More actions
November 25, 2014 at 7:56 am

#295375

I have a view built in SQL that pulls all of our sales orders, items, descriptions, quantities, and number of labels each. I am pulling this into access, and than running 3 nested queries on it in order to print product labels. First one limits it to a stated SO#, second one uses a COUNT table to create a seperate label for each of the quantity, and 3rd uses the Count table again to create multiples per each if required
(For instance, a 3-pc component that has qty 10 ordered will generate 30 labels in total, 10 1of3, 10 2of3, and 10 3of3, etc)
My report (label) is linked to the 3rd query and spits out our labels on a zebra printer.
This works, but is unbearably slow on anything that has more than a few lines on it.
Ultimately, I am still going to be using Access for the final mile (to print the actual label), but it would be nice to have it linked to a view with a much smaller subset of the data to start with.
Can a view be created that can be updated with the SO# from Access, or am I pretty much as lean as I can get on this scenario while still using Access to do my report?
Thanks!
Jeff

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Jeff

I would use SSIS (or your favourite other way of doing ETL) to bring the data from the remote servers into Access. Don't use linked servers at all - I find they're less flexible and less secure and they don't perform very well.

John

jirish Grasshopper Points: 10 More actions · Answer 2

Don't have SSIS so not an option for me. I need to do it within SQL studio or access for now.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Jeff

It's easy enough to install. You don't even have to run it on the same server as you're doing the ETL on. If it's really not an option, you can use sqlcmd. It's fiddly but it'll work just as well. There may be ways of doing ETL from within Access, but I don't know because I don't use it.

John

pietlinden SSC Guru Points: 63428 More actions · Answer 4

I would probably bring all the data down into Access and index the tables -- after you have all the data. Then build your query there. When you're done, truncate the tables in your Access DB. (don't want that stuff laying around for someone to misuse.)