How pass credentials to Access from Excel (for onward use)

[Mark]

Hi,
I have an Excel vba sub that uses ADODB to execute a saved query on Access.
The query runs on Access but requires the user to enter a Password (the
Access query is to a linked table to Sql Server).

What I want to do is to have a userform in excel that prompts the user for
credentials and then passes them to Access, that it may in turn use for its
query to SQL Server.
I.e. I want to avoid saving the password in Access.

is that possible?
thanks
mark

 

[joel]

See webpage

'How To Open ADO Connection and Recordset Objects
(http://support.microsoft.com/kb/168336)


The OPen method has a password as shown below

' Connection Open method #3: Open("DSN","Uid","Pwd")
Conn1.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=nwind.mdb;" & _
"DefaultDir=C:\program files\devstudio\vb;" & _
"Uid=Admin;Pwd=;"


You can use an inputbox or listbox to prompt the user for the ID an
password and use these string as part of the open command like this

MyAdmin = inputbox("Enter Account")
MyPassword = inputbox("Enter Password")
Conn1.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=nwind.mdb;" & _
"DefaultDir=C:\program files\devstudio\vb;" & _
"Uid=" & MyAdmin & ";Pwd=" & MyPassword & ";"

 

[Mark]

Joel,
thanks for that but I don't think that is quite what I need.

What I'm trying to do is to run a saved query on access (from excel). The
Access
query gets its data from a linked table in a Sql Server Database.
So what I need to pass is the credentials for onward use in sql server.
No data is returned to excel from this query, that happens at a later stage.


The examples seem to show credentials to access the data in Access.

 

[joel]

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.

 

[Mark]

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]

How much freedom do you have in modifying the Access Database file? Ca
you modify/add macro(s)? Can you add the prompting for ID and Passwor
in the Access file?