Problems after linking to tables over WAN

[CB]

Hi all,

I have a problem that is giving me major headaches. I hope my explanation
doesn’t give you headaches. I should also add that this is the first
split database that I’ve created so I’m sure I still have much to learn. I’m
not sure if my problem is a database issue or a networking issue or both.

The background…
I created a split database (has ULS) in one city. I made copies of the FE
(mde file), BE, and MDW file for use in another city (we’re not ready to use
Remote Desktop yet). After testing my duplicate, everything was working
perfectly under all group profiles so I sent the files to the other city with
instructions how to install everything on their server and desktops.

They couldn’t log in to the application and I realized that the FE was still
linked to the test BE in my personal folders where I was doing my testing. In
the FE, I’ve hidden the database window and restricted menu access so they
cannot re-link to their BE (placed on their LAN) themselves.

To solve this, I logged into MY copy of their FE and linked to THEIR back BE
over the WAN. After exiting, I then logged in as one of the Full Data Users
to ensure they could log in and all way okay so I e-mailed them the new FE.

The problem…
Though the Full Data Users can open the database, view data in various
forms, and open reports (based on queries), they cannot ENTER data. Three
particular forms open up as blank – there is NOTHING on the form – no combo
boxes, no labels, nothing. It’s as if the database opened up as read-only.
Not very useful for those users that need to add data!

My trouble-shooting…
My first thought was perhaps there were issues with network permissions on
the folder on their server. There were issues at first but now all relevant
users have read/write/modify/delete permissions on the applicable folder and
the problem remains.

I tried the following to duplicate the problem.
1) I ensured that I had known working copies of the FE (mdb and mde), BE,
and MDW file in my personal folder on our LAN. All works as it should for the
various user groups.

2) In the FE MDE, I deleted the links to the BE in my personal folder and
re-linked to the BE over the WAN. When I login as myself (owner and
administrator), all works fine. When I login as a Full Data User, the issue
persists – I cannot enter data.

3) In the FE MDE, I deleted the links to the BE on the WAN and re-linked to
the BE in my personal folder. In other words, I **returned to my original
configuration**. The problem persists – all is okay when I login as
administrator but when logged in as a Full Data User, I cannot enter day.

4) I again ensured I had known working copies as in step 1.

5) In the FE MDB, I deleted the links to the BE in my personal folder and
re-linked to the BE on the WAN. Same issues – okay for me as admin but not
when I login as Full Data User.

6) In the FE MDB, I deleted the links to BE on the WAN and re-linked to the
BE in my personal folder; i.e., returned to the original configuration. The
problem persists.

7) I compact and repair the MDB – the problem persists.

What is going on?? What am I missing? Are there issues linking over a WAN?

Thanks for any and all help!

Chris

 

[Douglas J. Steele]

You shouldn't even try to use Access across a WAN. The network speed simply
isn't sufficient for Access's needs.

You might want to read what Albert Kallal has to say on the topic at
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

 

[CB]

Hi Doug,

Thanks for replying.

The database will NOT be used over the WAN. The users will use it over their
LAN. Because I had the FE linked to the wrong BE, I re-linked over the WAN
and that's when I had problems.

With my being in a different city and them not being able to re-link to the
correct BE themselves, I don't know any other way of linking to the proper BE
other than over the WAN.

Regards,
Chris

 

[Paul Shapiro]

I thought the same as Douglas, but if your issue is just how to link the
tables in a location-independent fashion, here's what I do.

It depends a bit on how your users are setup. As long as they reference the
linked server with a simple server name, you just need to setup a DNS
reference for the same server name, but pointing to your server. Then on
your system you link the tables with that server name, and it should work
identically for them. The machine where you host the backend db should have
a share name that matches the client's setup.

As an example, if I have a client who needs to link to \\ClientServer, I
create a DNS entry on my server for ClientServer, but this entry is in my
domain and points to whichever local machine holds the backend db. So I link
the frontend by navigating to \\ClientServer\ShareName\backEndDb.mdb. As
long as that exact URL exists on the client systems, it should work.

However, I also include a simple interface for the user to choose a new
backend db and relink all the tables automatically. At application startup I
test to make sure the links are valid, and if not, I present a
file-selection dialog so the user can locate the be data.

 

[Douglas J. Steele]

Put code into the application to do the relinking for them.

Look, for instance, at http://www.mvps.org/access/tables/tbl0009.htm at "The
Access Web". Note that that code allows them to search for the back-end. If
you don't want that capability, replace the section of code

strMsg = "Do you wish to specify a different path for the Access
Tables?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") =
vbYes Then
strNewPath = fGetMDBName("Please select a new datasource")
Else
strNewPath = vbNullString
End If

with code that sets strNewPath for them.

(Note that if you do want them to be able to search for the back-end, you
need to include the code from http://www.mvps.org/access/api/api0001.htm as
well)

 

[CB]

Hi Doug,

I've looked at the code and while I THINK I have somewhat of an idea of what
it supposed to do I don't have a clear understanding of it since I do very
little coding.

If I understand you correctly, I will comment out the few lines you mention
and replace it with

strNewPath = "\\...path to file\myDB.mdb"

My next question though is where to put the code. The instructions state to
run the function at database startup. My database opens to a switchboard I
created using the Switchboard Manager. Would I simply place the code in the
switchboard's On Open event procedure?

Thanks for your help!

Chris

 

[Douglas J. Steele]

Yes to both.

 

[CB]

Hi again,

Well, I've copied the code into the Switchboard's On Open Event procedure,
commented out the necessary code, and changed the line of code per my last
post.

When I compiled the code, I got a compile error that stopped on the first
instance of "ahtAddFilterItem" in the function fGetMDBName(). The error was
"Sub or Function not defined." Since I didn't change the code other than
mentioned above, there must be another issue.

I recall a similar type error for some other code that I found through the
newsgroup. If I recall correctly, I had to add a reference. FWIW, the
following references are made available:
Visual Basic for Applications,
Microsoft Access 11.0 Object Library,
OLE Automation,
Microsoft ActiveX Data Objects 2.5 Library,
Microsoft DAO 3.6 Object Library.

What are you thoughts?

Chris

 

[CB]

I forgot to add that since I'm giving the direct path to the file (rather
than allowing them the option to browse to the BE) I did not include the
additional code you referred to with the second link. I inferred from your
post that it would not be necessary.

Thanks again!

Chris

 

[Douglas J. Steele]

I'm confused, then.

Your other message said you got "a compile error that stopped on the first
instance of "ahtAddFilterItem" in the function fGetMDBName(). " If you
didn't include the code from the second link, you shouldn't be callinging
fGetMDBName in your code!

Are you calling fRefreshLinks() in your Open event? If so, what do you have
as fRefreshLinks()?

 

[Paul Shapiro]

If the switchboard items table is in the frontend db, then it's ok to put
the relinking code in the switchboard form. I prefer putting that code into
an unbound splash form (just a form with fixed text for the application
title, and maybe a copyright notice, etc.) that does not have any data
source. That way the form can open without needing anything from any linked
table. If your switchboard items table is in the backend db, then you have
to use an unbound form for the linking code.

 

[Larry Linson]

If your "switchboard items table" exists, that means you opted for a complex
solution to a simple problem. That table is for application navigation, is
not data-related, and there is no need to place it in the back-end database.

It would be, IMNSHO, better to replace it with unbound forms and no table,
done from scratch; but if you are determined to use the Switchboard Manager
to create your switchboard, the table, too, should be in the front-end,
located on the user's machine.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

If your "switchboard items table" exists, that means you opted for
a complex solution to a simple problem. That table is for
application navigation, is not data-related, and there is no need
to place it in the back-end database.

And it really needs to be front-end version-specific. If you put it
in the back end, you could end up with mismatches between the data
in the back end driving your switchboard and the app components
available to an end user. For instance, if you create a new report,
and create a switchboard item to print it, that will immediately
appear for your end users on their switchboard, even if they haven't
upgraded to your new front end that includes the report.

In other words, there is no logical justification for putting the
switchboard table anywhere except in the front end.

 

[CB]

Hi Doug,

Sorry for the delay in responding. I didn't check the forum over the weekend.

Now I'm confused. The code in the first link has fGetMDBName as the fourth
function from the bottom of the code.

I've placed the entire code from the first link in the Open event (I also
have a few lines of code created by the Switchboard manager). Is this not
what I was to do?

Thanks for your time!

Chris

 

[Douglas J. Steele]

Since you don't want to prompt for the file, remove the definition of
function fGetMDBName from the module.

Sorry, I was going by memory. I'm sure that function used to be in
http://www.mvps.org/access/api/api0001.htm not in
http://www.mvps.org/access/tables/tbl0009.htm!

 

[CB]

Hi Paul,

Thanks for replying. Sorry for not replying to your other post. I was pretty
busy at work on Friday and got side-tracked.

Joan Wild has mentioned to me in the past that the switchboard table should
be in the front end but mentioned that there wasn't any harm in leaving it in
the backend. However, that was before I was having re-linking issues.

As to your other post, I *think* I grasped what you meant. Basically, the
application sees the same path name but underlying are two different IP
addresses?? It essentially tricks the application?

That said, I work for a large government organization and am not a network
person so I contacted our IT department to see if this is something they
would even let me do. My contact wasn't sure what you meant. It was Friday
and I was busy so I didn't pursue it further. If my understanding was correct
though, it seems like it might be a nice alternative to more code.

Regards,
Chris

 

[CB]

Hi Larry,

Thanks for responding. As I just mentioned in my reply to Paul, Joan Wild
did mention that my switchboard table should be in the FE. I will do that in
the next version of the database.

I'm not sure what you mean by "...opted for a complex solution to a simple
problem." Are you referring to using the Switchboard Manager to create the
switchboard and having the table in the backend or are you referring to
earlier posts about placing all that code in the switchboard's 'on open'
property?

Regards,
Chris

 

[CB]

Hi David,

Thanks for responding. Actually, I came across this very thing when I
created a new form while working on version 2 for the users in my city.

The only reason the switchboard table is in the BE is that when I split my
database (this is the first database in which I've done that), I was under
the impression that ALL tables should be in the BE. I will be changing this
in the next version of the database.

Regards,
Chris

 

[CB]

Hi again,

I had commented out that function previously but then the code stops in the
IF ELSE statements in the FOR statment. Would I simply comment out that
particular IF statement or would I need to comment out more?

Thanks!

Chris

 

[Douglas J. Steele]

To what have you changed the code from
http://www.mvps.org/access/tables/tbl0009.htm ?