Joel,

thnks once again.
It is a customer request that it be done this way, not what I would have
done givena free hand (& a bigger budget)

"joel" wrote:


Why is the query in Access and not Excel? Is the query in Access
part of a Macro? You can pass a parameters to a Sub in Access from
excel. These parameters can be the ID and Password and the Macro
can contain the query. You don't need a query to extract data from
a SQL server in Access and Excel. You can open a connection to the
Server using the ADO method to retrieve the data and then use SQL.

There are really two types of queries in VBA (both Access and
Excel). One is a hidden query that you set up using the menus. A
hidden macro the credials are fixed values or from a fixed location
(like a cell in a worksheet) and the password is visible to any
user. You really don't want to use this type of query unless the
there is protetions in place to limit the access to the password.

The second type query is part of a macro where you can prompt the
user(s) for the ID and Password. This is the prefered method for
your application.

I asume you know the command text portion of the query is the SQL
statements. I often run the Macro recorder in Excel while manually
setting up a query using the menu to help me get the propery SQL
syntax. then convert the Query in a maco to an ADO Open and use the
Recordset Method to either Read or Write information to the database.

There are lots of ways to read and write data to a SQL Server in VBA
(both excel and access). At the moment I don't know all your
requirements to recommend the best method, only to suggest different
methods you may not of considered.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197932

http://www.thecodecage.com/forumz

.