Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Updating querys in other databases 1
- Thread starter Muzzery
- Start date
-
1
- #2
muzz,
If you have permissions for the other db, you can use the QueryDef's .SQL property, and change it. First set a Database reference, ie
dim db as database, qd as querydef
set db = dbengine(0).opendatabase("c:\somedb.mdb"
set qd = db.querydefs("somequery"
qd.sql = "Select something from somewhere"
--Jim
If you have permissions for the other db, you can use the QueryDef's .SQL property, and change it. First set a Database reference, ie
dim db as database, qd as querydef
set db = dbengine(0).opendatabase("c:\somedb.mdb"
set qd = db.querydefs("somequery"
qd.sql = "Select something from somewhere"
--Jim
- Thread starter
- #3
Thanks, that's been a great help, but now i've got another problem, as i said, i want to update the value in a query to the value in the form, and i did what you suggested,and i wrote this:
Dim db As Database, qd As QueryDef
Set db = DBEngine(0).OpenDatabase("\\Drumm-server1\FDrive\Drumm\Programs\drumm update.mdb"
Set qd = db.QueryDefs("sysadmin update Query"
qd.SQL = ("UPDATE sysadmin SET sysadmin.[in use] = False , sysadmin.[Override date] = Format(Now(),dd/mm/yy), sysadmin.[bord year] = forms![auto drumm update]![update year], sysadmin.[bord month] = forms![auto drumm update]![update year]"
The problem is, as you may have noticed, is that it makes the "Update to field" to what is written after the = not the actual value, how do i get around this, sorry if this seems an easy question,, but i'm inexperienced in SQL statements.
Thanks, Muzz
Dim db As Database, qd As QueryDef
Set db = DBEngine(0).OpenDatabase("\\Drumm-server1\FDrive\Drumm\Programs\drumm update.mdb"
Set qd = db.QueryDefs("sysadmin update Query"
qd.SQL = ("UPDATE sysadmin SET sysadmin.[in use] = False , sysadmin.[Override date] = Format(Now(),dd/mm/yy), sysadmin.[bord year] = forms![auto drumm update]![update year], sysadmin.[bord month] = forms![auto drumm update]![update year]"
The problem is, as you may have noticed, is that it makes the "Update to field" to what is written after the = not the actual value, how do i get around this, sorry if this seems an easy question,, but i'm inexperienced in SQL statements.
Thanks, Muzz
muzz,
I guess I'm not sure I understand what you need...when you said 'Update a query', I thought you meant to update the query itself, which would typically mean to change the sql.
It now sounds like you mean update a table's records in another db? If this is the case, then the query can be local--it your db, but the table would be in the remote db, so you'd link the table.
If you still want to update the query in the remote db, but make the sql show the hardcoded values of what's in your local form, then do the following:
qd.SQL ="UPDATE sysadmin SET sysadmin.[in use] = False , sysadmin.[Override date] = #" & Format(Now(),dd/mm/yy) & "# , sysadmin.[bord year] = " & forms![auto drumm update]![update year] & ", sysadmin.[bord month] = " & forms![auto drumm update]![update year]
If the Month and year fields are string, you put quotes around it. Also, it looks like you repeated the Update Year for the bord month field in the last section.
--Jim
I guess I'm not sure I understand what you need...when you said 'Update a query', I thought you meant to update the query itself, which would typically mean to change the sql.
It now sounds like you mean update a table's records in another db? If this is the case, then the query can be local--it your db, but the table would be in the remote db, so you'd link the table.
If you still want to update the query in the remote db, but make the sql show the hardcoded values of what's in your local form, then do the following:
qd.SQL ="UPDATE sysadmin SET sysadmin.[in use] = False , sysadmin.[Override date] = #" & Format(Now(),dd/mm/yy) & "# , sysadmin.[bord year] = " & forms![auto drumm update]![update year] & ", sysadmin.[bord month] = " & forms![auto drumm update]![update year]
If the Month and year fields are string, you put quotes around it. Also, it looks like you repeated the Update Year for the bord month field in the last section.
--Jim
- Thread starter
- #5
Thanks very much for your help, but by completing this problem, i have encountered another, which if any one could help me with, i'd appreciate it.
I know need to update an on load procedure for another form in seperate database, can i do this through SQL or do i need to use another VB command to do this? any ideas or help much appreciated. Thanks. Muzz
I know need to update an on load procedure for another form in seperate database, can i do this through SQL or do i need to use another VB command to do this? any ideas or help much appreciated. Thanks. Muzz
Muzz,
You can access the form event properties, and set the event to be some function. Also, you can write directly to Modules or form modules. For a remote db you use OLE, set an application object, and use the modules collection. It's not something I've done often at all, but it's doable. Are you sure there isn't another way to accomplish what you want?
--Jim
You can access the form event properties, and set the event to be some function. Also, you can write directly to Modules or form modules. For a remote db you use OLE, set an application object, and use the modules collection. It's not something I've done often at all, but it's doable. Are you sure there isn't another way to accomplish what you want?
--Jim
- Thread starter
- #7
This is the only way i know of donig it but here's a brief summary of what i need to do:
Database 1 is the database with the information in it, database 2 is the database with the update procedure. (FYI database2 is sent out to clients as an upgrade, and then they use it to update, change, adjust, settings on there version of the database which they needed changing.)
What I need to do is modify an onload procedure for a form in access using the data from a form in another, separate, database.
e.g. On load says:
Private Sub Form_Load()
If Format(Now(), "dd/mm/yy" <> "04/02/02" _
And Format(Now(), "dd/mm/yy" <> "05/02/02" _
And Format(Now(), "dd/mm/yy" <> "06/02/02" Then
MsgBox "This update is only valid on 04/02/2002 to 06/02/2002”
End Sub
That is some of the code for an update .exe file, basically, it opens access, opens the form, run’s the onload procedure, then quits, updating the necessary tables, etc. Do you know of a line of code that will enable the onload procedure to be altered from a different database, separate to the one that the updated form is in i.e. instead of saying “04/02/02” it would say the date that is in the form in another database. The code needs to be put on a button, so that when it is pressed, it updates the values in the other database.
Database 2 is always closed, but can be opened if need be. (prefer if it was closed tho)
Database 2, when opened by cleints, loads a form, runs the on load command, (which includes running the query mentioned above) closes the form, quits access.
Database 1 needs to adjust what is coded into the onload command for the form. (i need to change the date which i mentioned in the above.)
Database 1 will do this by asking some basic information that needs to be changed, (i.e admin password, month, year, are values that might be changed) then the a button is pressed and on the onclick command of that button another line of code is run
This code needs to add the appropriate values from the form in database 1 and then put these values into the onload code in database 2. Now this is then e-mailed to clients, they run the update and bingo, there up to date.
The thing is, at the moment, we are having to do this manually every time somebody needs an update, and my boss wants me to write the code to do it all automatically, via our administration system we have at the office. I've been stuck on this for a good while, so far i can only e-mail the clients the update automtically and change a few values in a table via the query mentioned above.
I was thinking that this could be done via an SQL statement copied into the onload procedure. but i'm not sure how to go about it.
Thanx Muzz
Database 1 is the database with the information in it, database 2 is the database with the update procedure. (FYI database2 is sent out to clients as an upgrade, and then they use it to update, change, adjust, settings on there version of the database which they needed changing.)
What I need to do is modify an onload procedure for a form in access using the data from a form in another, separate, database.
e.g. On load says:
Private Sub Form_Load()
If Format(Now(), "dd/mm/yy"
<> "04/02/02" _ And Format(Now(), "dd/mm/yy"
<> "05/02/02" _ And Format(Now(), "dd/mm/yy"
<> "06/02/02" Then MsgBox "This update is only valid on 04/02/2002 to 06/02/2002”
End Sub
That is some of the code for an update .exe file, basically, it opens access, opens the form, run’s the onload procedure, then quits, updating the necessary tables, etc. Do you know of a line of code that will enable the onload procedure to be altered from a different database, separate to the one that the updated form is in i.e. instead of saying “04/02/02” it would say the date that is in the form in another database. The code needs to be put on a button, so that when it is pressed, it updates the values in the other database.
Database 2 is always closed, but can be opened if need be. (prefer if it was closed tho)
Database 2, when opened by cleints, loads a form, runs the on load command, (which includes running the query mentioned above) closes the form, quits access.
Database 1 needs to adjust what is coded into the onload command for the form. (i need to change the date which i mentioned in the above.)
Database 1 will do this by asking some basic information that needs to be changed, (i.e admin password, month, year, are values that might be changed) then the a button is pressed and on the onclick command of that button another line of code is run
This code needs to add the appropriate values from the form in database 1 and then put these values into the onload code in database 2. Now this is then e-mailed to clients, they run the update and bingo, there up to date.
The thing is, at the moment, we are having to do this manually every time somebody needs an update, and my boss wants me to write the code to do it all automatically, via our administration system we have at the office. I've been stuck on this for a good while, so far i can only e-mail the clients the update automtically and change a few values in a table via the query mentioned above.
I was thinking that this could be done via an SQL statement copied into the onload procedure. but i'm not sure how to go about it.
Thanx Muzz
Muzz,
I'm a little confused--you have db1 and db2, but then there seem to be two others--1 that they use to 'update, change, adjust, settings on their version of the database which they needed changing', then one where they modify a form using data from another, separate, database. Are either of these db1 or 2, or are they other dbs?
Anyway, one suggestion, if the your example code is the only procedure that needs changing, or if it's typical of what need changing, you can use table values in the code and then the user can change those values (the dates in your example). ie:
Lets say you have a table, table1, with 3 fields, date1, date2 and date3 (it might be better to use one date field, but 3 or more records, but this is easier to explain here)
If Format(Now(), "dd/mm/yy" <> dlookup("Date1","Table1" _
And Format(Now(), "dd/mm/yy" <> dlookup("Date2","Table1" _
And Format(Now(), "dd/mm/yy" <> dlookup("Date3","Table1" Then
MsgBox "This update is only valid on 04/02/2002 to 06/02/2002
End If
Of course there are many ways of making this more efficient, but I'm just showing a broad concept here, not coding technique. So as you can see, either you or the user (you can prevent the user from accessing table1 if need be) can update those dates at will, and the effect of the code will change along with that.
But to change the code itself, I would suggest putting the code in a module, and, for your example with the OnLoad event, have the form's OnLoad event be set to =SomeChangeFunction(), instead of putting the code itself in the form module. Then this function can is in a separate module, say modChange. Then you can set the user's .mdb to automatically import that module from your fresh source .mdb. If you're on a network this fresh source .mdb can sit on the network, if not, you email it to them or get it to them however you currently get changes to them. To import
This code runs in their local .mdb, and lets say your updated .mdb is on the network, \\venus\Dbupdates\update.mdb
dim strDbrem as string, strModname as string
strDbrem = "\\venus\Dbupdates\update.mdb" '(this can be table-driven as well, not hardcoded)
strModname = "modChange" 'this contains the function SomeChangeFunction(), which we're replacing
DoCmd.DeleteObject acModule, strModname 'must delete first before importing new
'Just make sure the module doesn't have any global declarations in it--that messes up the delete/import process
DoCmd.TransferDatabase acImport, "Microsoft Access", strDbRem, acModule, strModname,strModname
Now you've replaced the code itself. So either way--the first way--table-driven, or the second way, importing the Module, can achieve what you need.
--Jim
I'm a little confused--you have db1 and db2, but then there seem to be two others--1 that they use to 'update, change, adjust, settings on their version of the database which they needed changing', then one where they modify a form using data from another, separate, database. Are either of these db1 or 2, or are they other dbs?
Anyway, one suggestion, if the your example code is the only procedure that needs changing, or if it's typical of what need changing, you can use table values in the code and then the user can change those values (the dates in your example). ie:
Lets say you have a table, table1, with 3 fields, date1, date2 and date3 (it might be better to use one date field, but 3 or more records, but this is easier to explain here)
If Format(Now(), "dd/mm/yy"
<> dlookup("Date1","Table1" _ And Format(Now(), "dd/mm/yy"
<> dlookup("Date2","Table1" _ And Format(Now(), "dd/mm/yy"
<> dlookup("Date3","Table1" Then MsgBox "This update is only valid on 04/02/2002 to 06/02/2002
End If
Of course there are many ways of making this more efficient, but I'm just showing a broad concept here, not coding technique. So as you can see, either you or the user (you can prevent the user from accessing table1 if need be) can update those dates at will, and the effect of the code will change along with that.
But to change the code itself, I would suggest putting the code in a module, and, for your example with the OnLoad event, have the form's OnLoad event be set to =SomeChangeFunction(), instead of putting the code itself in the form module. Then this function can is in a separate module, say modChange. Then you can set the user's .mdb to automatically import that module from your fresh source .mdb. If you're on a network this fresh source .mdb can sit on the network, if not, you email it to them or get it to them however you currently get changes to them. To import
This code runs in their local .mdb, and lets say your updated .mdb is on the network, \\venus\Dbupdates\update.mdb
dim strDbrem as string, strModname as string
strDbrem = "\\venus\Dbupdates\update.mdb" '(this can be table-driven as well, not hardcoded)
strModname = "modChange" 'this contains the function SomeChangeFunction(), which we're replacing
DoCmd.DeleteObject acModule, strModname 'must delete first before importing new
'Just make sure the module doesn't have any global declarations in it--that messes up the delete/import process
DoCmd.TransferDatabase acImport, "Microsoft Access", strDbRem, acModule, strModname,strModname
Now you've replaced the code itself. So either way--the first way--table-driven, or the second way, importing the Module, can achieve what you need.
--Jim
- Thread starter
- #9
DB1 id our "sysadmin" database, which we have in the office, it is basically is used to view or adjust client detalis for DB3.
DB2 is a database that is just an upgrade, as i said it opens, runs the update proceudre and closes. (It is basically a patch for DB3, but each one is tailor made, if you will, for each individual client)
DB3 is the client side version. This DB is the one that i am trying to upgrade. The idea is that DB1 modifys DB2 so that it adjust DB3 so that DB3 works correctly. Does this make it any clearer? Sorry i forgot to mention DB3 correctly. It was a bit vague.
As for the code, i'll filter through it and see if i can understand it and get it to work. (Please excuse my in-experience, but i am only an office junior, and my boss is off ill, so i'm left on my own!)
Many thanks (again)
Muzz
DB2 is a database that is just an upgrade, as i said it opens, runs the update proceudre and closes. (It is basically a patch for DB3, but each one is tailor made, if you will, for each individual client)
DB3 is the client side version. This DB is the one that i am trying to upgrade. The idea is that DB1 modifys DB2 so that it adjust DB3 so that DB3 works correctly. Does this make it any clearer? Sorry i forgot to mention DB3 correctly. It was a bit vague.
As for the code, i'll filter through it and see if i can understand it and get it to work. (Please excuse my in-experience, but i am only an office junior, and my boss is off ill, so i'm left on my own!)
Many thanks (again)
Muzz
- Thread starter
- #10
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question