Run an AutoExec Macro where an ODBC password is required

[kini113]

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

[Megalog]

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.

[kini113]

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

Function Update_Inventory()
On Error GoTo Update_Inventory_Err
 
    DoCmd.SetWarnings False
    ' Remove barstock on order from control inventory table
    DoCmd.OpenQuery "Remove On Order", acViewNormal, acEdit
    ' makes update table
    DoCmd.OpenQuery "Update Inventory MT", acViewNormal, acEdit
    SendKeys "PASSWORD {ENTER}", False
    ' Gets actual bar value
    DoCmd.OpenQuery "Make Bar Value Cost", acViewNormal, acEdit
    ' Updates unit price to actual value of bar
    DoCmd.OpenQuery "Update Unit Price", acViewNormal, acEdit
    ' Adds the new inventory
    DoCmd.OpenQuery "Add New Inventory", acViewNormal, acEdit
    ' Adds split OD and ID
    DoCmd.OpenQuery "UPDATE OD AND ID", acViewNormal, acEdit
    ' updates changed quantites
    DoCmd.OpenQuery "Update Qty", acViewNormal, acEdit
    ' adds sorting tool for Mat'l spec
    DoCmd.OpenQuery "Mat'l Spec Sort", acViewNormal, acEdit
    ' Changes zero bar qty to null
    DoCmd.OpenQuery "update zero qty", acViewNormal, acEdit
    ' add zeroed bars into archive
    DoCmd.OpenQuery "ADD Bar's at Zero", acViewNormal, acAdd
    ' deletes zeroed bars from inventory
    DoCmd.OpenQuery "Remove Zero bars", acViewNormal, acEdit
    ' Adds Barstock On Order
    DoCmd.OpenQuery "Bar Stock On Order", acViewNormal, acEdit
    ' step 2
    DoCmd.OpenQuery "DUPLICATE ON ORDER", acViewNormal, acEdit
    ' step 3
    DoCmd.OpenQuery "Filter On Order", acViewNormal, acEdit
    ' step 4
    DoCmd.OpenQuery "SORT MAT'L SPEC", acViewNormal, acEdit
    ' step 5
    DoCmd.OpenQuery "ADD STOCK ON ORDER", acViewNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.OpenReport "Bar Stock Inventory plus On Order", acViewPreview, "", "", acNormal
 
 
Update_Inventory_Exit:
    Exit Function
 
Update_Inventory_Err:
    MsgBox Error$
    Resume Update_Inventory_Exit
 
End Function

Thanks
Cindy

[ajalwaysus]

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

[ADezii]

@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

[Stewart Ross]

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.

Public Function fODBCTablesRefresh() As Boolean
    ' This function refreshes the DAO ODBC table connections
    ' listed in the qryODBCRefreshList query
    ' which in turn lists the first table to be connected for each different
    ' connection string listed in table tblDatabasesConnected. 
    '
    ' This function is used to automate the initial refresh of the ODBC table connection
    ' so that updates etc can run without user intervention to supply the UID and PWD components
    ' of the DB connection.
    '
    ' 23/4/09
    '
    On Error GoTo refresh_err
    Dim daoDB As DAO.Database
    Dim tdef As DAO.TableDef
    Dim RS As DAO.Recordset
    Dim strConnectOld As String
    Set daoDB = CurrentDb
    Set RS = CurrentDb.OpenRecordset("qryODBCRefreshList")
    Do While Not RS.EOF
        Set tdef = daoDB.TableDefs(RS![tablename])
        strConnectOld = RS![Connect String]
        tdef.Connect = strConnectOld & "; UID=" & RS![USER ID] & "; PWD=" & RS![CONNECTION PASSWORD]
        tdef.RefreshLink
        tdef.Connect = strConnectOld 'restore no-password version of connect string
        RS.MoveNext
    Loop
    RS.Close
    fODBCTablesRefresh = True
    Exit Function
refresh_err:
    fODBCTablesRefresh = False
End Function
 

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

[kini113]

@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

[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.

Public Function fODBCTablesRefresh(byVal strTableName as String, byVal strUserName as String, byVal strPassword as String ) As Boolean 
    ' This function refreshes the DAO ODBC table connections 
    ' for the linked database in which strTableName is any of the tables linked. 
    '   
    ' This function is used to automate the initial refresh of the ODBC table connection 
    ' so that updates etc can run without user intervention to supply the UID and PWD components 
    ' of the DB connection. 
    ' 
    ' 23/4/09 
    ' 
    On Error GoTo refresh_err 
    Dim daoDB As DAO.Database 
    Dim tdef As DAO.TableDef 
    Dim strConnectOld As String 
    Set daoDB = CurrentDb 
    Set tdef = daoDB.TableDefs(strTablename) 
    strConnectOld = tdef.Connect 
    tdef.Connect = strConnectOld & "; UID=" & strUserName & "; PWD=" & strPassword 
    tdef.RefreshLink 
    tdef.Connect = strConnectOld 'restore no-password version of connect string 
    fODBCTablesRefresh = True 
    Exit Function 
refresh_err: 
    fODBCTablesRefresh = False 
End Function 

-Stewart

[NeoPa]

A new (hijack) post has been split into its own thread (Link Refresh Code). Any interested parties may visit to help.