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
8 18772 Megalog 378
Recognized Expert Contributor
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.
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
But haven't you setup the ODBC connection on your PC?
copy and past this in your run window:
%SystemRoot%\sy stem32\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
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 tblDatabasesCon nected. There is an associated query called qryODBCRefreshL ist referred to in the function which returns only the ODBC ones from tblDatabasesCon nected. It supplies the name of the first linked table for that database, the connection string, the UID, and PWD (as stored in table tblDatabasesCon nected).
The refresh function needs to know the name of one of the tables to refresh, which as mentioned I obtain in qryODBCRefreshL ist. 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
@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
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 32,568
Recognized Expert Moderator MVP
A new (hijack) post has been split into its own thread ( Link Refresh Code). Any interested parties may visit to help.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tom Chidwick |
last post by:
Hello,
I've custom-built a fairly extensive object model that is designed to
run in the background
and control the opening and closing of related forms. The whole thing
kicks-off with an
AutoExec macro at startup, using a RunCode action that calls a global
function and the
rest works great. Pretty good stuff - flies like an eagle, when it
works.
|
by: mvivar |
last post by:
Hi everybody:
This will be not easy to explain as my mother language is not english,
so my apologies in advance if it sounds confusing.
We have a database access 97 wich controls time of people in their job
in order to finally know how many money costs us to run some projects.
People always estimates the time when reporting and we do have the
impression they really underestimate the time.
|
by: MLH |
last post by:
Did A97, perchance, do away with the need for
an AutoExec macro to initiate an autoexec sequence
for databases? Or, must we still suffer with that hideous
macro?
|
by: Bob Sanderson |
last post by:
I am trying to set up an ODBC DSN to our company MySQL database so that I
can use a MS Access front end. I am using the MySQL ODBC 3.51 driver. The
database is running off of Apache on our Windows 2000 server (call it
'foobar'). The host is 'localhost', the user is 'root'. To access the
database via a web browser on a PC on our network, I use the address
'http://foobar:8080/". This works fine. If I set up a DSN on the server,
I connect...
|
by: Rysiek |
last post by:
Hi.
I've got a "base.mdb" secured with "system.mdw". There's also a
password set on "base.mdb" . After configuration ODBC driver ( system
DSN ), supplying user and his pass too, when I try to connect do it I
receive error "Invalid password". What I'm doing wrong ? Is it related
to pass which is for "base.mdb" ? Regards.
Rick
| |
by: darren via AccessMonster.com |
last post by:
Hi
I have multiple .mde FE's linked to an .mdb BE. The FE is distributed with an
Auto FE updater and as such all users are on the same version of the FE.
However, one user is getting "Action Failed" when the FE starts up and runs
AutoExec code.
I have seen some threads say this could indicate corruption but no ohter
users are experiencing this problem. In the past on a different db I had
something similar and it was down to the...
|
by: dick |
last post by:
I can't seem to get AutoExec to run a function. Per an article on this
group, I:
1) built a boolean function in a module that simply issues a MsgBox,
then returns True
2) in AutoExec macro, I wrote a condition "onOpen()=False" including
the dbl quotes. For the action I put out another MsgBox saying 'onOp is
False'.
The 2nd MsgBox always appears. I put a breakpoint in the function. It
never triggered, so my function is not being called.
|
by: Takeadoe |
last post by:
Dear NG,
Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?
Thank you very much!
Mike
|
by: kini113 |
last post by:
I want to update my MS Access 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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |