Solved

un-split database

Posted on 2001-08-29
10
217 Views
Last Modified: 2008-02-26
We have created a databse in Access2000 for use over a LAN.

This has been split so the back end is on one of our serves and the front end on the workstation.

The reference to the back end used a UNC to prevent reliance on a specific mapped drive letter, but performance is poor.

We suspect we may be better off mapping a drive letter in the login script and referring to our back end through that.

Question - how do we unsplit/rejoin the database so we can split it again. (do we have to map each query to its new location - hopefully not - or can we point to it in a single place in the database)

Constructive comments please, even if there may be an obvious answer we have missed.
0
Comment
Question by:stbedes
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6438083
hi stbedes,

i have a dutch version, so my naming might be of...

select menu 'Tools' --> 'Add in' --> ...
here is an add-in to refresh the linked tables.
U can select a new location in this window.

cheers
Ricky
0
 
LVL 8

Accepted Solution

by:
DennisBorg earned 100 total points
ID: 6438194
You don't need to resort to Joining, and the reSplitting the database.

You can use the Linked Table Manager, as Ricky (Paurths) has suggested.

Or, in the event that you do not have that AddIn installed, you can delete the linked tables from your front end, and then relink those tables using the logical drive reference.

You don't need to do anything with the Queries ... just the linked tables.


Could you let us know if your application performs better after you start using a mapped drive instead of UNC? I'm not sure it would really help, but I would appreciate hearing how it turns out for you.



-Dennis Borg
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6438234
i'll verify what dennisborg wrote. so far you're two for two on good answers. i've got something else though...

instead of remapping the tables (you could though, that's fine) simply map a drive to the server. any drive letter will work.

i used to have the EXACT same situation as you have. someone would have a problem and i'd go to their station to troubleshoot. when i got there, the app ran 10x slower than it did on MY workstation. i was puzzled. why was it so much slower at the user's workstation. then one day i found a user who had a mapping to my server. (any mapping to the server is fine) and at that station the app was as fast as it is on MY development station... finally i realized that as long as the user's pc had ANY mapping to the server in question, performance was improved dramatically. (moving to sql server has even better effects)

what happens when you map a drive "permanently" is the M$ operating system hold the "path" to the server and caches that path somewhere. then, when the UNC name is called, it knows the EXACT path to take to get to the server, making things a BUNCH faster...

try it out. map ANY drive to the server in question without modifying the app and see if it makes a difference. i know it did for me using WinNT and A97. i'd be interested to know if it was just something that i experienced.

btw. i work for a large company (30,000+ employees worldwide) and my users are on a couple of different subnets...

enjoy,

dovholuk
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6438309
dovholuk:

Thank you for the kind words. :-)


I do have a couple questions though.

First, thank you for the good explaination and suggestion. I learned something new today because of you. :-)

As I understand your suggestion, you would map a drive to the server, but not change the connect property for any of the linked tables. That is, the linked tables would still use the UNC references. This in and of itself would improve the performance when the two computers communicate with each other.



Question #1) - You said to map any drive to that *server*. For your suggestion to work properly, would you not need to map to that same Share Name on that server? I know you didn't say that any share on that server would do, but it was left a little unclear.

In other words, if you used \\MyServer\MyShare\MyDir\MyDB.mdb as the UNC reference, you should map a drive to \\MyServer\MyShare, where mapping a drive to \\MyServer\SomeOtherShare would not help, or not as much.


Question #2) - Do you know, either by experience or by theory, whether replacing the UNC's with a drive reference in the linked table's connection property would help more than simply mapping a drive but leaving the linked table connections unchanged?


Again, thank you for your insight in this matter.

-Dennis Borg
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6438462
Hi Dennis...

1.) you need not map to the same share. a common technique i used to employ (and still do where necesarry) is to map to UNC "hidden" shares that the user normally can't open, see, or access. even still. mapping to ANY share will hold the path (through the network) to the server. i found that THAT's the really important part. every time you send a packet to a server it follows a route through routers/switches/hubs (whatever's applicable). once you establish this route the OS somehow caches the path and data transmission is very fast.  at least, it was for me! :)

2.) i've mapped to drives on purpose because it DOES provide a significant improvement in speed. for example. i had a db that was "saving" images into a db. (in reality, the images were being saved to a share) using UNC it took a LONG time. i found that the time that it was taking, was simply the time to LOATE the server. after the server was located, the data transfer happened as fast as the network works. so, for fun i mapped a drive and tried again. mapping the drive skips the whole "hello network. please find me MyServer. Oh, MyServer? that's IP 192.168.32.23. IP .... etc." that was what i found was the limiting step. by mapping a drive, all the "does the server exist and where is it" work the OS does, is cached so the transmission happens very fast.

does that clear things up any?

i GOT to fly (gotta "date")

if i didn't answer your questions throughly, please forgive me. i'll check later.

dovholuk
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Expert Comment

by:DennisBorg
ID: 6438535
In regard to my Question #2:

Did you realize my question was in how the preformance compared between the following scenarios:

   1) Link the tables using UNC, but map a drive to the server anyway to improve performance.

   2) Link the tables using a logical drive reference instead of using UNC

I was wondering if you could confirm my suspicion that scenario #2 would perform better than scenario #1


Thanks again, and I hope you have a great date!


BTW, do you have any comments on linking to a UNC refering to an IP address instead of to a machine name?  (ex:  \\111.222.333.444\ShareName\DirName\FileName)



-Dennis Borg
0
 
LVL 3

Expert Comment

by:Koka
ID: 6438783
Well, old, old , common knowledge. Even if it may NOT be the solution to your problem it's always a good idea to create a dummy table (say, single row one) in the backend called e.g. "Dual", and upon opening frontend to open it in code and leave it open so that connection to back-end is always kept open.

Try it, and at least you'll find whether the slowdown is in Access seeking for backend locaton.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6438948
hey dennis,

yes i noticed what q2 entailed. i thought i'd answered it, but i interpreted q2 differently.

i never did a study on which one had the better performance, however i really don't think that either would perform better than the other. the key is having a persistent mapping as koka pointed out. i don't like holding tables open with access though (just me) but holding a table open probably would do the same thing. the only difference is that the initial "startup" it takes to open the first connection is when you start the app, and not when you start the OS.

if you do any "studies", be sure to let me know what you find out! :)

dovholuk
0
 

Author Comment

by:stbedes
ID: 6445252
Thanks for all these suggestions and the info.

We will try them next week (as anohter issue has raised it head) and get back to you.

We originally set our user profiles  (start menu etc) to point to a UNC in a system policy (to avoid need for mapped drive letter and visible access to central profile on network). Logging in was ok, but evry time users went to the start menu it was v slow.

Changed it to a mapped drive letter in login script (accepting the security compromise) and it speeded up - hence my suggestion in question and confirmed by comments above. Don't have stats for the difference though.

0
 

Author Comment

by:stbedes
ID: 6653502
Apologies for delay in responding. The higher order IRQ was called MS Exchange Server.

Mapping to the same location speeded things up as suggested - so the points are yours.

(didn't have the add-in so could not give them to previous suggestion)

Many thanks for so many constructive contributions.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now