Get Data from SQL to Access - via Access

[kimi]

Please help me decided the best way to do this...

I have a shopping cart web site that stores its data in an SQL server
database.

The client wants to retrieve daily orders via their access DB on their
desktop.

I have written macros to connect to the same SQL server, retrieve data
and process data on my desktop.

But What I'd like to do is...
..Display a list of open orders, etc, allow the client to select which
items to transfer from the live to the desktop. How can I do this?

Can someone point me to an example of data retrieval and display for
action in Access?

thank you in advance.



Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

Someone else may have a better idea of what your clients want (based on
your description) or a better way to accomplish it. But my
interpretation of what the client wants is to be able to retrieve orders
remotely (from a computer that is not on the same network as the sql
Server - if on the same network, shouldn't be any issues, use ADO/ODBC).
If this is the case, here is one simplistic solution.

You have your basic application which takes orders from the internet,
and stores them in a Sql Server db. The client (owner of the DB) will
have exclusive access to the data tables in Sql Server. So the client
could query the tables remotely through an asp/aspx page and see the
results on a separate webpage that only the client has permission to
view (the client being the owner of the application). I will guess that
the client wants to store this data onto the Access mdb on their laptop.
Here is where I fall a little short on retrieving the data automatically
from the client side web page to the client side (the remote computer -
laptop/desktop) mdb. I would just highlight the data with the mouse and
do a copy paste (like to excel - then copy that to Access). I believe
security issues make retreiving data automatically from a webpage
difficult (mostly for hackers). Hopefully, if someone has a better
solution they will share (if I even interpreted your question
correctlly).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

Hi Rich,
Yes interpreted correctly.

However the client wants to do this from within Access - and not via a
web site.

Thanks for the help.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

The only thing I can think of for that would be something like
PCanywhere type software where you can remotely control your server
computer from anywhere. There may be better software packages than
PCanywhere, but something like that.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Albert D. Kallal]

Well, obviously, the hard part of connecting to sql server has been done,
and is setup...correct ?

So, really, your question at this point has little to do with sql server.

Your question is:

I want to display a list of orders on the screen. Then I want the user to
select some of these orders, and the copy them to another table.

The fact that one table is on planet mars using the Microsoft Mars Sql
server that runs on the mars rover is really a moot point here.

So, one of your tables just happens to be linked to sql server, and the
other table is a simply local table in the mdb file...right?

Assuming the above, then we need some way to let the user copy the records
to a local table.

Ok...well, the first question then is how many records (max) will we have to
display. (this question is VERY important, as it will help us decide what
kind of control or UI thing to use here).

Up to this point, we have a VERY simple problem (and solutions are simple).

However, is the above really your problem/question?

What happens once ONE user moves these orders to their desktop? After this
happens..then can others also move the orders to their desktop? In other
words, once one user selects an order..shall others be prevented from moving
this order? If this is the case, then your problem of course it not the
original question anymore...is it?

Further, since ms-access is connecting to the sql server...then why bother
to move the records anywhere at all? Would not just showing which person
selected which orders be better? (then all office people could see/know who
has selected what orders to work on!).

Again, the approach and answer for you will depending on the above
questions. If you DO NOT care that one person has already
selected/downloaded orders, then course you can actually transfer the
records to the local pc (and, that is your first question). On the other
hand, if it DOES matter that someone has already downloaded some
records...then you need to make note of this fact on the server side.
(otherwise..how will you know who grabbed what order). And, it follows if
you have to make note of this fact that someone grabbed and order to work on
then it would be a BAD idea to transfers the records also...right?

So, you need to decide the above issues first. Once you decide..I don't see
much difficulty with the solution.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn