Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    19

    Unanswered: MakeTableQuery table location needs to be back end

    I have a small database (Access 2003) located with backend data on a Nass drive (just tables) and a couple of users with front ends (forms and queries) which we update from time to time and redistribute the front end.

    I have an issue with mail merge and data locations.

    Currenty for producing a monthly servicing list of customers we run a query (on the front end) that collects the data but stores this in a table on the front end.

    We then open up a word mail merged letter (in a directory on the Nass drive) which grabs this data. This worked fine when all PCs where called "admin" as the file location was the same.

    Now each ot the PCs has different names the datasource is different for each front end so only one will work without keep redoing the datasource each time you open the letter.

    Is there any way we can convert the existing make table query to store the newly made table in the back end in stead of the front end so the letter datasource will always be the backend, or will some kind of generic datapath work instead of c:\users\fredsPC\mydatabase etc

    Would be interested to hear if anyone does this kind of mail merge any different.

    Many thanks for any feedback

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    you DONT make table on the backend.
    you create a table on the frontend, then empty it, and run an append query.


    Quote Originally Posted by DaveN View Post
    I have a small database (Access 2003) located with backend data on a Nass drive (just tables) and a couple of users with front ends (forms and queries) which we update from time to time and redistribute the front end.

    I have an issue with mail merge and data locations.

    Currenty for producing a monthly servicing list of customers we run a query (on the front end) that collects the data but stores this in a table on the front end.

    We then open up a word mail merged letter (in a directory on the Nass drive) which grabs this data. This worked fine when all PCs where called "admin" as the file location was the same.

    Now each ot the PCs has different names the datasource is different for each front end so only one will work without keep redoing the datasource each time you open the letter.

    Is there any way we can convert the existing make table query to store the newly made table in the back end in stead of the front end so the letter datasource will always be the backend, or will some kind of generic datapath work instead of c:\users\fredsPC\mydatabase etc

    Would be interested to hear if anyone does this kind of mail merge any different.

    Many thanks for any feedback

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    If each PC has a different user account, and each user account has a different drive mapping for the relevant location, then you can create a table of user accounts and their drive mappings. Then update your code to work out which account is running things and therefore retrieve the correct location.
    I use this approach extensively - in a team of three people, there are three different mapped locations to the same directory on the file server!
    Google "Dev Ashish API functions" for more on this.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Did you know that you can Have a "Make Table" point to a Differance location

    I have a Mail Merge button on a Screen

    the create a Table in K:\Data\MMerge.accdb and that all that there is in the file

    then I have word pointing to that.


    open said query click the Make table icon then select Another Datebase Use the UNC path name eg \\severname\folderNametodatabase\thedatabase.accdb

    then if a oher user run it its going to the same place even if they have K: map to differance place
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •