Automatically connecting a network drive (and disconnecting) via Access 2013 VBA

Hello All

I have an Access program where the front end will reside on everyone's machine and the back end is on a server in the building.  To get to the back end the person would have to connect to the server and supply an id and password.

I created a .bat file that they can run just before opening the Access database but I was wondering if there was a way to put that net use connection string into the Access code itself.  When I try running the Access database without connecting to the network drive I get an error, but the error comes up so quickly (before even the main form pops up) that I did not know if there was some way to do the connecting first.

Any ideas?

Thanks!
alevin16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surone1Commented:
is the main form bound to a linked table or does it try to access a linked table?
if so you may want to use an unbound form as a main form, and do the connecting there.  using a shell comand
http://dmcritchie.mvps.org/excel/shell.htm
and after that open the "old" main form.
i'm not 100%  sure if this will work, but if it doesn't there may be another way  way to  try this.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<To get to the back end the person would have to connect to the server and supply an id and password.>>

 That's a bit odd...usually a windows log in will take care of things fine.

<<I created a .bat file that they can run just before opening the Access database but I was wondering if there was a way to put that net use connection string into the Access code itself. >>

 As long as you don't try and get to the back end tables, yes you'd be fine.  I'm sure there's a windows API as well that could be called in place of the .BAT.  Would have to dig for that though.

  As nick said, make sure your not trying to touch the backend before you execute the batch file.  If you can't do that easily within your current app, consider a small "Launcher" app that executes the batch file, then calls the main app.

Jim.
alevin16Author Commented:
Hey Jim,

Is the launcher app something I could find online or is that something I would create in Access?  Basically the main form is unbound.  I have the backend tables linked via the ODBC connection.  I guess the program is trying to refresh the connections before it does anything else.

I like the Launcher app idea.  Please get back to me with any more info you have.  I will look online in the mean time.
Andy
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Andy,

 It can be anything and probably the simplest is an Access db.

 Just call that from your shortcut, have it execute the shell(), test for a good connection (i.e. do a DIR() for the backend), and if good then open your main app.  If not, message the user and quit.

You could even go as far as having the app write the .BAT file, then delete it once it's done doing the mapping.

Also, you should be aware that you don't need a mapped drive letter; you can use UNC naming ( \\Server\ShareName), but you'd still have security to deal with.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alevin16Author Commented:
Hi Jim,

The batch file worked.  Thanks!

Andy
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.