[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


un-split database

Posted on 2001-08-29
Medium Priority
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.
Question by:stbedes
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 12

Expert Comment

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.


Accepted Solution

DennisBorg earned 400 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

Expert Comment

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...


NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Expert Comment

ID: 6438309

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

Expert Comment

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 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.


Expert Comment

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

Expert Comment

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.

Expert Comment

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! :)


Author Comment

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.


Author Comment

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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

656 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