Update VBA within Forms with 1 single update

[SmithHerf]

We have sent out 300 CDs with our yearly DB on it. Problem is there are 2 places within 2 different forms that we have a button that uses VBA to load an Excel spreadsheet depending on data and options. Both peices of VBA has the wrong path for the spreadsheets. We bundle Access run-time 2003 for those that doens't have Access. Some, out of the 300, has Access 2003 or 2007. I need a way to create an update, can use VBS, VBA within another DB or a different way I haven't thought of to fix these mistakes. Create an update that they can download and run to fix, so needs to be some what small (our current DB are 37 MB) as some of thes folks still us dial-up. What ideas does someone have that a mild beginner to pull of. Thanks for all the help in advance.

[JustJim]

We have sent out 300 CDs with our yearly DB on it. Problem is there are 2 places within 2 different forms that we have a button that uses VBA to load an Excel spreadsheet depending on data and options. Both peices of VBA has the wrong path for the spreadsheets. We bundle Access run-time 2003 for those that doens't have Access. Some, out of the 300, has Access 2003 or 2007. I need a way to create an update, can use VBS, VBA within another DB or a different way I haven't thought of to fix these mistakes. Create an update that they can download and run to fix, so needs to be some what small (our current DB are 37 MB) as some of thes folks still us dial-up. What ideas does someone have that a mild beginner to pull of. Thanks for all the help in advance.

You're probably going to have to depend on some Access proficiency from your users. The easiest way is to create a new, empty Access application, import the two forms into them, fix their code and re-distribute this application with just two forms in it. The users will have to receive this little application and import the corrected forms into the original application.

The other option is to distribute a "repair" application that will open up to a form with a button on it that runs code to find the original application and export the new forms/code to it. This could be complex to develop and test.

Sadly, my experience is that, no matter how clear, concise and foolproof your instructions are, if you ask any 300 people to follow them, you're going to get 15, 30, 50 or more who mess it up.

Why not just fix the application and re-distribute the whole thing?

Jim

[SmithHerf]

Agreed, some will mess it up and then complain to the VP.
I kind-of thought of the repair idea but I was attempting to read the code and use the replaceline built in function or something like that. I don’t understand the export the new form and it does sound complex and most likely something beyond me at this point.
Some of these folks well have already updated contact info., pricing and the like. So if I re-send, I would need to import all of their data from the many tables. I realize I didn’t put that into my previous posting, sorry.
Is it possible to open another DB, then attached an object to the code within that form? Thanks for replying.

[JustJim]

Agreed, some will mess it up and then complain to the VP.
I kind-of thought of the repair idea but I was attempting to read the code and use the replaceline built in function or something like that. I don’t understand the export the new form and it does sound complex and most likely something beyond me at this point.
Some of these folks well have already updated contact info., pricing and the like. So if I re-send, I would need to import all of their data from the many tables. I realize I didn’t put that into my previous posting, sorry.
Is it possible to open another DB, then attached an object to the code within that form? Thanks for replying.

I really don't know what your best solution is. It doesn't help now but in the future you might be best distributing two databases a sort of local front end/back end system where "your" stuff is separate from "their" stuff. I'll have a think and try to get back to you tomorrow.

Jim

[JustJim]

I really don't know what your best solution is. It doesn't help now but in the future you might be best distributing two databases a sort of local front end/back end system where "your" stuff is separate from "their" stuff. I'll have a think and try to get back to you tomorrow.

Jim

Having been on a site visit today and being re-acquainted with the type of users we've got to put up with... I still think that the best bet is for you to distribute an "update" database application (either by CD like your first distribution, or make it available for download from a website) containing just the two forms with their corrected code.

(by the way, this is all about an incorrect path, could you not tell the users to put the spreadsheets into the "incorrect path"?)

You then provide detailed instructions (do the recipients of this distribution have local IT guys that could help?) on how to import the corrected forms into their original application and provide a help phone number or e-mail address if they get into trouble.

Then you get down on your knees and pray, burn incense, sacrifice goats (your first-born son if you don't like him all that much), etc, etc.

Users, meh!

Jim

(Fair dinkum, this morning I said to a user "You do 'A' then you do 'B' then you do 'C'". She said "OK, I do 'A' then I do 'C' then I do 'B'. I (calmly) said, "No, you do 'A' then 'B' then 'C'. "Ahh, I get it", she said, " I do 'A' then 'C' then 'B'". I edited the form to put an 'A', a 'B' and a 'C' in front of the labels, wrote it down again and left! I don't get paid enough for this!)

Jim

[SmithHerf]

The 2 DB front/end back/end idea, assuming I understand, might be something to review for next year.
I can't beleive I am going to do this, but I created a very small installer that will create if needed the wrong path and copy the files from the install point into there. But, also includes an un-installer, and the installer has a seperate appID.
This is after some testing to see your previous point of how complex this is.
This also changes next years plan of just an update, meaning a couple of *.jpg that are within a form... since I created the org. path problem and I can't just fix form easily to replace the images.. I will be sending out another CD next year.

[JustJim]

The 2 DB front/end back/end idea, assuming I understand, might be something to review for next year.
I can't beleive I am going to do this, but I created a very small installer that will create if needed the wrong path and copy the files from the install point into there. But, also includes an un-installer, and the installer has a seperate appID.
This is after some testing to see your previous point of how complex this is.
This also changes next years plan of just an update, meaning a couple of *.jpg that are within a form... since I created the org. path problem and I can't just fix form easily to replace the images.. I will be sending out another CD next year.

The two part system uses two mdb files (or mde if you go that way). One (front end) contains all the operations parts like forms, reports, the code/queries that drive them and any tables that contain data that you will change (for example a table of names/contacts. The other (back end) contains tables of data that the operators can change locally (but using the forms etc from the front end). That way if you make any changes, you send them a whole new front end, they delete the old front end (losing no data) and keep on going with the new front end.

All the best.