variable backend location

[Richardson]

I have a set of databases that work together through linked tables. The
program has one front end and up to 3 possible backends depending on the
datasets the users want to access.
I have a splash screen that appears, and then the main switchboard. I would
like a form to appear after the splash screen with the various backend file
locations listed. When the user selects one (and I don't care if it is a
checkbox, option button, hyperlink, etc) I would like the front end to
refresh the linked tables to the location they specified.
Does anyone have a suggestion on how to create that situation/VB code? I am
just starting to move from macros to VB code, so any help is greatly
appreciated.

Thanks in advance for your help.

Lori

 

[John Nurick]

Hi Lori,

I'd do this by making a little table (in the front end) like this:

tblBackEnds
BEName - text, PK, a unique name for the back end
FileSpec - text, e.g. "\\SERVER\Share\Folder\File.mdb"
SortOrder - Number(Long) in case you want them to be listed
in other than alphabetical order so you can put the one
most often used at the top of the list.

Next, get hold of VBA code for linking tables. There's a procedure here
http://www.fabalou.com/Access/Modules/refreshtables.asp (which I haven't
tested but which looks as if it will work) and an internet search will
find others. Paste the code into a Module in your front end.

Then make a form, continuous view, that displays the back end names in
order, with a second textbox (txtFileSpec) for the back end filespecs.
Give the form a header (with a label explaining what the user should do)
and a footer with a commandbutton on it. If you want to keep the form
small and sparse, set txtFileSpec's Visible property to false.

In the Click event procedure of the button, put something like this:

With Me.txtFileSpec
RelinkTables .Value
End With

Good luck. Once you get used to VBA you probably won't want to go back
to macros!

 

[Richardson]

John,
Thank you for the quick reply. This looks like exactly what I need. I am
positive VB will move me away from macros as I learn the VB as well as I
know the macro commands.
Have a great weekend.

Lori

 

[Richardson]

John,
I have created the VBA code you mentioned, created the form and the command
button at the bottom. However, I don't see how the module and the command
button are working together. When I click the command button, nothing
happens. Can you give me any suggestions on what else I need to do to get
this working?

Thanks,
Lori

 

[Ronald W. Roberts]

John,
I have created the VBA code you mentioned, created the form and the command
button at the bottom. However, I don't see how the module and the command
button are working together. When I click the command button, nothing
happens. Can you give me any suggestions on what else I need to do to get
this working?

Thanks,
Lori

You need to create the event procedure "OnClick" for the command button
and then put you code
inside of the procedure.

Ron

 

[Richardson]

Ronald,
I think that is what I have done. The command button's on click is event
procedure with the VBA code. I added a line in the code to display a
message box when complete. Other than adding that line of code, the code is
exactly a copy/paste of the example code given. When I click the button, no
errors are displayed and the message box appears, but the linked files do
not change.

Lori





You need to create the event procedure "OnClick" for the command button and
then put you code
inside of the procedure.

Ron

 

[John Nurick]

Hi Lori,

Please post the VBA code from the button's Click event procedure.

While you've got the VBA editor open to do that, click in the gray left
margin of the code module next to the "Private Sub" statement that
begins the Click event procedure. This will set a breakpoint on that
line (big burgundy dot in the margin). Next ,right-click in the toolbar
and display the Debug toolbar if it's not already visible.

THen switch back to the main Access window, launch the form and click
the button. You can now use the buttons on the Debug toolbar to step
through the code a line at a time. At a minimum you'll be able to see
whether or not the relink code is running. With luck you'll see what the
problem is and be able to fix it.