Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How pass credentials to Access from Excel (for onward use)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How pass credentials to Access from Excel (for onward use)
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 and 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 & ";" -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How pass credentials to Access from Excel (for onward use)
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" wrote: 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 and 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 & ";" -- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How pass credentials to Access from Excel (for onward use)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How pass credentials to Access from Excel (for onward use)
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How pass credentials to Access from Excel (for onward use)
How much freedom do you have in modifying the Access Database file? Can you modify/add macro(s)? Can you add the prompting for ID and Password in the Access file? -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I pass the logon credentials in a macro to a ODBC query | Excel Programming | |||
run access macro from excel pass parameter | Excel Programming | |||
Can a service account be embedded in Workbook so that by default the macros are run with service account credentials and not the user credentials??? | Excel Discussion (Misc queries) | |||
Selecting a range from second row onward | Excel Programming | |||
Pass parametres from Excel to Access query | Excel Programming |