By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,007 Members | 998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,007 IT Pros & Developers. It's quick & easy.

Run an AutoExec Macro where an ODBC password is required

P: 27
I want to update my database every night at 10pm. I have a Macro that runs all of the queries needed to update the database (which is also saved as a VBA module) and an AutoExec Macro to runs the "Update Inventory" macro. I also scheduled the task to run every night a 10pm. The problem i have is that in order to run the "Update Inventory" macro I have to enter my ODBC password and there is no way to save the password. This there a Macro action or VBA code I could add that would enter my password automatically?

Thanks
Cindy
Aug 24 '09 #1

✓ answered by Stewart Ross

Hi Cindy. Passwords will not normally be saved when you set up an ODBC connection. To do so would bypass first-level user security, so it really will make no difference whether or not you set up a user DSN - this will not store it for you.

In terms of the function I posted, its job when called is to reset the Connection string of one of the Data Access Object (DAO) table definitions to include the Username and Password for the ODBC link, then to refresh the link, and finally to restore the non-password version of the connection string (for security reasons once again). When Access connects to an ODBC database the link only has to be made once - it is then persisted for all other table accesses that use the current open connection.

DAO is the native form of Access data object. An alternative already suggested in the other post referred to by ADezii is to establish an ADO connection, but if DAO appears complicated then the ADO version will appear more so.

In both cases all that is happening is establishing a connection to the linked database via a VBA function call. That function has to establish the connection without manual intervention, typically by supplying the DSN information, including any defined Username and Password - the things you otherwise do manually via an ODBC dialogue box when trying to access a linked table in Access.

I'm sorry to say that it is difficult to simplify a process much further when it will always involve establishing a programmatic connection to another database, supplying the connection with the Username etc. As you may well have to adapt and modify code to meet your own needs you have to have a reasonable grounding in what the code does and how it achieves it to do so.

The function I supplied has a loop in it which is not needed if you will only be refreshing one table (lines 20, 26, 27). The loop refers to data which is obtained from a recordset (variable RS - lines 16, 19, 20, 21, 22, 23, 28) which again could be done away with if you only want to refresh one table.

However, for maintainability I would caution against hard-coding usernames and passwords into VBA code - as any change to a DSN, username or password would require a code change. That is why I store such data in a table, to allow easy maintenance.

Below is a simplified version of the function - but you need to supply a tablename, username and password to the function as parameters when it is called.

Expand|Select|Wrap|Line Numbers
  1. Public Function fODBCTablesRefresh(byVal strTableName as String, byVal strUserName as String, byVal strPassword as String ) As Boolean 
  2.     ' This function refreshes the DAO ODBC table connections 
  3.     ' for the linked database in which strTableName is any of the tables linked. 
  4.     '   
  5.     ' This function is used to automate the initial refresh of the ODBC table connection 
  6.     ' so that updates etc can run without user intervention to supply the UID and PWD components 
  7.     ' of the DB connection. 
  8.     ' 
  9.     ' 23/4/09 
  10.     ' 
  11.     On Error GoTo refresh_err 
  12.     Dim daoDB As DAO.Database 
  13.     Dim tdef As DAO.TableDef 
  14.     Dim strConnectOld As String 
  15.     Set daoDB = CurrentDb 
  16.     Set tdef = daoDB.TableDefs(strTablename) 
  17.     strConnectOld = tdef.Connect 
  18.     tdef.Connect = strConnectOld & "; UID=" & strUserName & "; PWD=" & strPassword 
  19.     tdef.RefreshLink 
  20.     tdef.Connect = strConnectOld 'restore no-password version of connect string 
  21.     fODBCTablesRefresh = True 
  22.     Exit Function 
  23. refresh_err: 
  24.     fODBCTablesRefresh = False 
  25. End Function 
-Stewart

Share this Question
Share on Google+
8 Replies


Megalog
Expert 100+
P: 378
Dont you have the option of storing the password for the ODBC connection when you initially create it? I dont remember ever having to re-enter passwords for my ODBC connections in the past.

As far as filling in the password box, look up the SendKeys function.. maybe that's the route you'll need to take.
Aug 24 '09 #2

P: 27
Unforunately ODBC passwords can't be saved from what I've read on other sites due to registry security. I tried the SendKey() function but it didn't work maybe it's just not in the right place to respond to the prompt for the password. Here is the what I have so far

Expand|Select|Wrap|Line Numbers
  1. Function Update_Inventory()
  2. On Error GoTo Update_Inventory_Err
  3.  
  4.     DoCmd.SetWarnings False
  5.     ' Remove barstock on order from control inventory table
  6.     DoCmd.OpenQuery "Remove On Order", acViewNormal, acEdit
  7.     ' makes update table
  8.     DoCmd.OpenQuery "Update Inventory MT", acViewNormal, acEdit
  9.     SendKeys "PASSWORD {ENTER}", False
  10.     ' Gets actual bar value
  11.     DoCmd.OpenQuery "Make Bar Value Cost", acViewNormal, acEdit
  12.     ' Updates unit price to actual value of bar
  13.     DoCmd.OpenQuery "Update Unit Price", acViewNormal, acEdit
  14.     ' Adds the new inventory
  15.     DoCmd.OpenQuery "Add New Inventory", acViewNormal, acEdit
  16.     ' Adds split OD and ID
  17.     DoCmd.OpenQuery "UPDATE OD AND ID", acViewNormal, acEdit
  18.     ' updates changed quantites
  19.     DoCmd.OpenQuery "Update Qty", acViewNormal, acEdit
  20.     ' adds sorting tool for Mat'l spec
  21.     DoCmd.OpenQuery "Mat'l Spec Sort", acViewNormal, acEdit
  22.     ' Changes zero bar qty to null
  23.     DoCmd.OpenQuery "update zero qty", acViewNormal, acEdit
  24.     ' add zeroed bars into archive
  25.     DoCmd.OpenQuery "ADD Bar's at Zero", acViewNormal, acAdd
  26.     ' deletes zeroed bars from inventory
  27.     DoCmd.OpenQuery "Remove Zero bars", acViewNormal, acEdit
  28.     ' Adds Barstock On Order
  29.     DoCmd.OpenQuery "Bar Stock On Order", acViewNormal, acEdit
  30.     ' step 2
  31.     DoCmd.OpenQuery "DUPLICATE ON ORDER", acViewNormal, acEdit
  32.     ' step 3
  33.     DoCmd.OpenQuery "Filter On Order", acViewNormal, acEdit
  34.     ' step 4
  35.     DoCmd.OpenQuery "SORT MAT'L SPEC", acViewNormal, acEdit
  36.     ' step 5
  37.     DoCmd.OpenQuery "ADD STOCK ON ORDER", acViewNormal, acEdit
  38.     DoCmd.SetWarnings True
  39.     DoCmd.OpenReport "Bar Stock Inventory plus On Order", acViewPreview, "", "", acNormal
  40.  
  41.  
  42. Update_Inventory_Exit:
  43.     Exit Function
  44.  
  45. Update_Inventory_Err:
  46.     MsgBox Error$
  47.     Resume Update_Inventory_Exit
  48.  
  49. End Function
Thanks
Cindy
Aug 24 '09 #3

Expert 100+
P: 266
But haven't you setup the ODBC connection on your PC?

copy and past this in your run window:
%SystemRoot%\system32\odbcad32.exe

that will display your ODBC connections on your PC, then you need to find your DSN and edit it to save your password.

-AJ
Aug 24 '09 #4

ADezii
Expert 5K+
P: 8,675
@kini113
Have the AutoExec Macro execute a Public Function where the ODBC Password is programmatically entered into the Connection String. See Related Thread, Post #2:
http://bytes.com/topic/access/answer...e-certain-time
Aug 24 '09 #5

Expert Mod 2.5K+
P: 2,545
I run an automated update and export at 5am each day. One of the linked databases is connected via ODBC, and the link is refreshed automatically by the following function, which makes use of the DAO connection string. The function is run from the On-Load event of the main switchboard form, which in turn is opened automatically by an Autoexec macro as mentioned by ADezii. I arrange it this way to be able to run updates etc manually as well as automatically, and to test additions and so on before they are added to the auto export lists running at 5am.

Expand|Select|Wrap|Line Numbers
  1. Public Function fODBCTablesRefresh() As Boolean
  2.     ' This function refreshes the DAO ODBC table connections
  3.     ' listed in the qryODBCRefreshList query
  4.     ' which in turn lists the first table to be connected for each different
  5.     ' connection string listed in table tblDatabasesConnected. 
  6.     '
  7.     ' This function is used to automate the initial refresh of the ODBC table connection
  8.     ' so that updates etc can run without user intervention to supply the UID and PWD components
  9.     ' of the DB connection.
  10.     '
  11.     ' 23/4/09
  12.     '
  13.     On Error GoTo refresh_err
  14.     Dim daoDB As DAO.Database
  15.     Dim tdef As DAO.TableDef
  16.     Dim RS As DAO.Recordset
  17.     Dim strConnectOld As String
  18.     Set daoDB = CurrentDb
  19.     Set RS = CurrentDb.OpenRecordset("qryODBCRefreshList")
  20.     Do While Not RS.EOF
  21.         Set tdef = daoDB.TableDefs(RS![tablename])
  22.         strConnectOld = RS![Connect String]
  23.         tdef.Connect = strConnectOld & "; UID=" & RS![USER ID] & "; PWD=" & RS![CONNECTION PASSWORD]
  24.         tdef.RefreshLink
  25.         tdef.Connect = strConnectOld 'restore no-password version of connect string
  26.         RS.MoveNext
  27.     Loop
  28.     RS.Close
  29.     fODBCTablesRefresh = True
  30.     Exit Function
  31. refresh_err:
  32.     fODBCTablesRefresh = False
  33. End Function
  34.  
Although at present I refresh just one ODBC-linked database I keep the names of all of the databases connected and their connection strings etc in a table within the DB called tblDatabasesConnected. There is an associated query called qryODBCRefreshList referred to in the function which returns only the ODBC ones from tblDatabasesConnected. It supplies the name of the first linked table for that database, the connection string, the UID, and PWD (as stored in table tblDatabasesConnected).

The refresh function needs to know the name of one of the tables to refresh, which as mentioned I obtain in qryODBCRefreshList. I do this by joining to another query which returns the names of the ODBC-linked tables. How it is done does not matter, what does is that the refresh function has to be supplied the name of a table, a connection string, a UID and a PWD. I do this using a recordset and loop as there can be several ODBC databases to refresh as developments progress.

If you are always going to refresh just one database it would be possible to adapt the function above by doing away with the use of the recordset and associated loop, supplying the name of a table to refresh and the UID and PWD strings as parameters.

-Stewart
Aug 25 '09 #6

P: 27
@ajalwaysus
The ODBC connection is a System DSN and I can't find anywhere to save it. Let me know if I'm missing something. Is there a way to change it to a User DSN I know I can save a password there?
Stewart thanks for your info, but I must admit i'm really new to VBA and I don't understand alot of what you said. I normally work in MS Access syntax, is there anyway you could explain your previous post more simply.


Thanks
Cindy
Aug 25 '09 #7

Expert Mod 2.5K+
P: 2,545
Hi Cindy. Passwords will not normally be saved when you set up an ODBC connection. To do so would bypass first-level user security, so it really will make no difference whether or not you set up a user DSN - this will not store it for you.

In terms of the function I posted, its job when called is to reset the Connection string of one of the Data Access Object (DAO) table definitions to include the Username and Password for the ODBC link, then to refresh the link, and finally to restore the non-password version of the connection string (for security reasons once again). When Access connects to an ODBC database the link only has to be made once - it is then persisted for all other table accesses that use the current open connection.

DAO is the native form of Access data object. An alternative already suggested in the other post referred to by ADezii is to establish an ADO connection, but if DAO appears complicated then the ADO version will appear more so.

In both cases all that is happening is establishing a connection to the linked database via a VBA function call. That function has to establish the connection without manual intervention, typically by supplying the DSN information, including any defined Username and Password - the things you otherwise do manually via an ODBC dialogue box when trying to access a linked table in Access.

I'm sorry to say that it is difficult to simplify a process much further when it will always involve establishing a programmatic connection to another database, supplying the connection with the Username etc. As you may well have to adapt and modify code to meet your own needs you have to have a reasonable grounding in what the code does and how it achieves it to do so.

The function I supplied has a loop in it which is not needed if you will only be refreshing one table (lines 20, 26, 27). The loop refers to data which is obtained from a recordset (variable RS - lines 16, 19, 20, 21, 22, 23, 28) which again could be done away with if you only want to refresh one table.

However, for maintainability I would caution against hard-coding usernames and passwords into VBA code - as any change to a DSN, username or password would require a code change. That is why I store such data in a table, to allow easy maintenance.

Below is a simplified version of the function - but you need to supply a tablename, username and password to the function as parameters when it is called.

Expand|Select|Wrap|Line Numbers
  1. Public Function fODBCTablesRefresh(byVal strTableName as String, byVal strUserName as String, byVal strPassword as String ) As Boolean 
  2.     ' This function refreshes the DAO ODBC table connections 
  3.     ' for the linked database in which strTableName is any of the tables linked. 
  4.     '   
  5.     ' This function is used to automate the initial refresh of the ODBC table connection 
  6.     ' so that updates etc can run without user intervention to supply the UID and PWD components 
  7.     ' of the DB connection. 
  8.     ' 
  9.     ' 23/4/09 
  10.     ' 
  11.     On Error GoTo refresh_err 
  12.     Dim daoDB As DAO.Database 
  13.     Dim tdef As DAO.TableDef 
  14.     Dim strConnectOld As String 
  15.     Set daoDB = CurrentDb 
  16.     Set tdef = daoDB.TableDefs(strTablename) 
  17.     strConnectOld = tdef.Connect 
  18.     tdef.Connect = strConnectOld & "; UID=" & strUserName & "; PWD=" & strPassword 
  19.     tdef.RefreshLink 
  20.     tdef.Connect = strConnectOld 'restore no-password version of connect string 
  21.     fODBCTablesRefresh = True 
  22.     Exit Function 
  23. refresh_err: 
  24.     fODBCTablesRefresh = False 
  25. End Function 
-Stewart
Aug 26 '09 #8

NeoPa
Expert Mod 15k+
P: 31,662
A new (hijack) post has been split into its own thread (Link Refresh Code). Any interested parties may visit to help.
Sep 14 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.