Open a Database with VBA 7

[HenriM]

I am using the usual front-end/back-end structure in an application and do not wish my users to access the front end database by opening it in Windows Explorer.

So,
I have disabled the menu bar, Database Manger and Shift Key, created an .mde etc and made this open with a Form carrying a warning that this database can only be accessed via xxxx.mde, which is the back-end.

That serves my purpose but I would ideally like a piece of code which automatically closes the front-end and opens the back-end, so the back-end opens whichever one the user tries.

From all the code I have seen in various tek-tips forums I am sure nothing is impossible but I have tried in vain to find this particular issue. Using the title above I get a nil return on any search - but I am sure someone, somewhere must have done this and covered it in a forum.

Any ideas or pointers would be most welcome.

 

[GingerR]

sorry i'm confused...seems backwards to me? 'Front-End' is the db with forms, reports etc...'Back End' is just the data tables. do you mean you do not want them to open the 'back end'?

the best thing to do is to not even tell people where the database is housed. why are they messing around in your share? you can give them a desktop shortcut that points to the db of your choice, with the server path in the shortcut, then they will open the proper db. but obviously they can look in the properties of the shortcut to find the server and then go look. why would people be doing this? two reasons: dumb, and malicious. dumb, i think you've taken care of. malicious...the best way to take care of that is the access security. you can't prevent someone from poking around where they shouldnt be if they are determined to be there. one tricky thing i do is to name my backend db(data tables) system.dll and link my front-end to that. people will just think it's some system file and not even try to open it.

if i am way off the mark of your issue, please let me know.

 

[HenriM]

GingerR

First of all I do appreciate your responding so quickly.

You are right, I have always assumed that the front end was the data and the back end, the program. I stand corrected, thanks.

Your suggestion that I call the back end .dll or something is certainly a possibility, but the users must be able to maintain adequate backup of their data and I don't see how they can do that if they don't know what or where it is. (Actually, as I write I can think of a way we might achieve this, but it will take me some time to think it through.) Another advantage of using tek-tips!

Perhaps I should explain why I am using the front/back end structure in the first place.

The program must have flexibility to change with the times, leaving the historical data stored by the user unaffected. So all changes must be catered for by upgrades or updates to the front end.

The target market is a specific group who have the need for a fairly sophisticated system but, to put it nicely, a wide range of computer literacy. The security measures are there principally to stop them, by accident or through curiosity, from screwing up their own data.

So the structure is designed to ensure that the data can only be accessed or viewed via the front end, which has its own error traps etc.

I am happy that the program as it stands will do the job. Try to open the back-end from Windows Explorer and you get a screen telling you the name and location of the front end; you will get no further.

But I do want to make the thing as simple as I can for the user and it is clearly better if the program, instead of giving this warning, just closes the back end and opens the front end automatically. Open back end or front end; you get the same, correct result.

I am convinced that there must be a way of doing this in VBA and I am trying to work it out. But if there is a wheel out there somewhere, why reinvent it?

That is why I am posting the question.

If you or anyone can provide the answer or point me to an FAQ or article which covers this it will be a very big help.

 

[GingerR]

in your back-end db, you can write a macro and call name it AutoExec. then it will open when someone opens the db. in it put action

RunApp

and put in the command line:

"C:\Program Files\Office2K\Office\MSACCESS.EXE" \\fil-nw03-04\eedepot\pbd\pbdmetrics_Be737.mdb[code]

which is "the path to the msaccess.exe"{space}The path to the db

then put action QUIT (options = EXIT)

or you can make the form pop up as it does now, and have it say "blah....The REAL db will now open" and when they hit ok or whatever button you have, you can run that code (then dont call it autoexec).

in VBA it would be:

    [code]Call Shell("""C:\Program Files\Office2K\Office\MSACCESS.EXE"" \\fil-nw03-04\eedepot\pbd\pbdmetrics_Be737.mdb", 1)
    DoCmd.Quit acExit

 

[HenriM]

This is just what I want!!

I can't try it right away but I'll give it a go this evening.

I presume that this code recognises the link automatically and I don't need to put in the name of the front-end database. Anyway, Ill soon find that out.

Just one other thing if you will. This code goes right beyond my own comprehension and I try if possible always to understand what I am doing. Could you possibly give me a brief outline of what each of the last four (\fil-nw03-04\\etc) parts of the code means and actually does? - or point me to somewhere where I can read it up?

In the meantime the least I can do is mark this for a star!

Thanks again

 

[HenriM]

OOps!!
I tried your code , got an error "File not Found" and worked out why:

My system does not have subforms 'Office' and 'Office2k'
It has 'Microsoft OfficeXP' and 'Office10'. This is because on Microsofts'advice I created a new folder to avoid conflict with Office97 which I sometimes need to use.I can of course amend this in the code BUT

What folders and subfolders do my users have? They will be different, sure as God made little green apples!

Since I am making the application available in Runtime Access only, that should not in the end be a problem. I shall just have to work out how the code needs to be edited to cope with this.

But you have definitely pointed me in the right direction and I'll do you the courtesy of letting you know the final answer when I get to it.

Of course. if you already have it, it will be most welcome.

HenriM

 

[GingerR]

that bit is the path to the db. (this happens to be my db).

yes you have to put in the path of what you want it to open, otherwise it does not know.

say your location is \\ServerName\ShareName\FolderName\MyDB.mdb

you would put that in place of \\fil-nw03-04\eedepot\pbd\pbdmetrics_Be737.mdb

i thought from your orig write-up that both frontend and backend db's were on a server. if the front-end is on each person's machine, then why are they in your back-end at all??!!? my opinion is that is nuts. you say "but the users must be able to maintain adequate backup of their data and I don't see how they can do that if they don't know what or where it is. "...the way you back up data is to have the server admin people do it. they should be backing up data every night!!! that's normal. in any case, my users are not responsible for that type of thing, i am. why exactly are your users in the backend data again? they could easily (mistakingly) delete the entire db or on purpose or not mess up someone else's data. ugh!!

the way you might have wanted to deploy this to your users is this: write a batch file that copies the db from the server to a specific place (your call) on their machine. then copy a desktop shortcut to their desktops, which points to that specific place where the db is. this is also convenient in case you have updates to the forms, reports, etc...you just write a little code that if needed, on launch it first copies down a 'new' version from the server if there is one. this way you have more control over what is going on.

 

[HenriM]

Thanks, I think I can work on from there.

But I assure you, my method is not as crazy as your comments suggest.

Sorry if I didn't make myself clear. My target market is a large number of small businesses, many of whom are working with stand-alone machines. The program is designed for private individuals with their own client base on their own desktops (or notebooks) and even where there is access to a server, there is no reason to work that way.

We are not working or aiming to work in a corporate environment.

I do not and must not have any control over the use they make of the system, so once they have paid for their licence my only role is to keep them up to date with public data (tax rates etc) which I do by issuing upgrades to the front end. The back end starts as a set of empty tables which they populate as they enter data. I never see it.

My only concern is to provide an application which can be used with confidence by people who are not in the main computer technicians and to write that application in such a way that the margin for error is minimised - and can be handled either by e-mail or on the telephone.

Whatever I produce must work on any machine with Windows 95+ , which is why I opt for RunTime Access.

So far, so good, everything is fitting into place and I think your help will enable me to fill the final gap.

Again, this help has been of more value than you imagine, and is much appreciated.

HenriM

 

[HenriM]

Dear GingerR

Sorry, I must come back to you again - I am now so close it is is mortifying, but there must be something silly in my code that I just cannot spot.

Of your options I decided that the RunApp macro is the best. I created this with the Command Line -

"C:\Program Files\Microsoft OfficeXP\Office10\”Msaccess.exe"\\C:\Program Files\InstantProfile\”InstantProfile.mdb””
and put in the action Quit as Exit as you indicated.

I got the message "Microsoft Access can't invoke the application using the RunApp Action.... The path to the application is invalid, or a component of the application is missing. Check the path in Windows Explorer or File Manager."

I have checked and rechecked the paths; tried all sorts of
possible variants, getting the same error every time.

My logic from what you sent is ... Path to Msaccess.exe (U/L Case as in my Windows Explorer) and Path to my chosen database, separated by "\\". I have tried just separating by "\" and that doesn't work either.

Obviously you have no way of checking that I have got the paths right, but I have been very careful with this and wonder if you can suggest anything else which may be wrong?

HenriM

By the way this isn't a private conversation - anyone can join in!

 

[Philem]

Hullo, Henri.

You gave a line from your RunApp section, and I believe there may be a space missing. Refer to Ginger's post first mentioning this. The post includes, amongst other gems, this: "path to msaccess.exe"{space}path to database. That {space} is important. Access is very picky, and would likely bomb if you didn't have it.

HTH

Philem

 

[scriverb]

Just copy and paste this path and it should work for you. You had more quote marks than necessary also.

"C:\Program Files\Microsoft OfficeXP\Office10\Msaccess.exe" “C:\Program Files\InstantProfile\InstantProfile.mdb”

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[HenriM]

Sorry gents - still no go!

Bob Scrivener's code produces the same error message.

So to check other paths I tried:-

"C:\Program Files\Microsoft OfficeXP\Office10\Excel.exe” \\D:\Accounts\HMaccts.xls

"C:\Program Files\Microsoft OfficeXP\Office10\Msaccess.exe” \\C:\Program Files\Microsoft OfficeXP\Office10\Samples\Northwind.mdb

both of which produced exactly the same error message.

Obviously, Autoexec is starting RunApp and RunApp is trying to do its job; but does not seem to recognise any paths.

Could there be a bug in AccessXP? I have Access97 on my system and can try that - if it works, I'll convert to 2002 and see what happens.

Failing that, it looks as though it's a call to Pearly Gates's people and see what they have to say!

 

[HenriM]

The procedure doesn't work in 97 either - I get the same error message again, so I can't see it being a bug.

 

[scriverb]

If you are using a call to Shell you should be using this format:
Dim x as variant
x = Shell("C:\Program Files\Microsoft OfficeXP\Office10\Msaccess.exe" “C:\Program Files\InstantProfile\InstantProfile.mdb”,1 )

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[HenriM]

Thanks Bob

I wasn't proposing to go down the Shell Route as the Macro RunApp seems so much neater if only I can make it work.

I have gone on to the Microsoft Forum and someone there had exactly the same requirement and was given the same code:-

All I have done is change the folder names from Microsoft Office to Microsoft OfficeXP and Office to Office10.

"C:\Program Files\Microsoft
OfficeXP\Office10\MSACCESS.EXE" "C:\Program
Files\Microsoft OfficeXP\Office10\Samples\NorthWind.mdb"

I tried it and it STILL gives me the same error message!

I will have a go at the Shell call, but when I have got x, what do I do with it?

Thanks again

 

[scriverb]

This is from ACCESS Help:
Runs an executable program and returns a Variant (Double) representing the program's task ID if successful, otherwise it returns zero.

So, you could check the value of x for <> 0 to make sure that the shell executed properly. A zero means that it failed.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[GingerR]

try just the access part and see if you get error
&quot;C:\Program Files\Microsoft
OfficeXP\Office10\MSACCESS.EXE&quot;

does that work? it should launch access.

 

[HenriM]

GingerR

Yes it does open Access! Next job is to work out why it does not go the rest of the way.

I need a break, so I am closing down for the evening (20.30 here) and I'll start again in the morning.

Really I am thrilled with the response from the three of you and I can't thank you enough. If you want to use this thread to chat among yourselves while I'm away, do feel free!

 

[HenriM]

My apologies to those who gave me the right answer - which didn't work for me!

The reason was that on installation I have created two copies of OfficeXP, one in the folder MicrosoftXP and one in the default Microsoft Office (which also holds Office97). I don't know how this happened and I know Microsoft do not recommend having two versions of Office in the same folder; but their support technician suggests that until I do a complete reinstall - as it all works, I leave it alone.

&quot;C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE&quot;,
followed by a space and my target path does work.

Why didn't I try this when I tried all the other possible options? Please don't ask!!

One puzzle - although this command line opens my database, if I rename Microsoft Office XP and try to open the same database via Windows Explorer, the system looks for the Microsoft OfficeXP folder, doesn't find it and starts the Install procedure!

The answer must be to Uninstall both versions of Office, making sure that both folder sets have gone, then reinstall, follwing more carefully the Microsoft article and if that doesn't work, back to reinstalling Windows .... but not just yet!

Anyway, thanks again

 

[llafretaw]

have to thank all of you for this post as well, same issue and have been banging my head against a brick wall till I found this.