Results 1 to 5 of 5
  1. #1
    MrMom is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Virginia
    Posts
    11

    Convert link in front end Access db to local table in backend db

    I am doing a price evaluation of contract modification proposals from vendors. All of the data - proposal data and existing contract data - is all ODBC. The results of my evaluation are in local tables in the front end db and I create links to all required ODBC tables in the front end.

    However, I have identified conditions where I can gain significant performance improvements by converting the ODBC links to local tables (verified by doing so manually at breakpoints in the VBA code). Because of the data volumes, though, I want those local tables to be in a backend Access database and I need to do that via VBA. Once I have linked to the proposal submission table, I can run my threshold tests to determine if the conditions are met warranting the use of local tables. If they are met, I need the link in the front end to become a local table in the back end. Then, I also need to create local tables in the back end database to contain the full contract price tables for each of the vendors on the contract.

    Initially I tried the following:


    Code:
     sPTFullLinkName = "lnk_" & parmSubmissionIDSchema & "_" & strTableName
     DoCmd.SelectObject acTable, sPTFullLinkName, True
     DoCmd.TransferDatabase acExport, "Microsoft Access", dbpath, acTable, sPTFullLinkName, sPTFullLinkName, False
    But, that only created a link in the backend database (at dbpath).

    So, I changed acExport to acImport (everything else unchanged), and I get Error code 3709 The search key was not found in any record.

    Any suggestions? Please.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    A local table db is fine for 1 single user.
    But if you want multiple users to access it, you need linked tables.

  3. #3
    MrMom is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Virginia
    Posts
    11
    A single user performs the evaluation of each contract modification proposal and a new copy of the front end database is used for each proposal evaluation. At no time does the evaluation process modify any of the ODBC data; after analysis, users export the anomalistic results from the front end for inclusion in their evaluation report. The database is retained for a period of time and then deleted.

    Also, the local copies of the ODBC contract tables have to be filtered so they only contain data relevant to evaluating the current proposal. When the evaluation is completed, these backend local copies will be deleted.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please feel free to download and use my free utility: http://forestbyte.com/ms-access-utilities/fba-fuze/

    It is the opposite of the db splitting utility that comes with Access, as it will take your regular front-end and convert it into an "all-in-one' db with all local tables.

    Cheers,
    Vlad

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    really don't understand this

    single user performs the evaluation of each contract modification proposal and a new copy of the front end database is used for each proposal evaluation
    since all data should be in the backend, the front end never changes

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2017, 05:43 PM
  2. Front-end Access Backend Teradata
    By tmcrouse in forum Forms
    Replies: 2
    Last Post: 08-05-2014, 03:45 PM
  3. Replies: 3
    Last Post: 01-02-2014, 08:11 AM
  4. Replies: 5
    Last Post: 11-13-2012, 12:16 PM
  5. MS Access Front End MS Excel Backend
    By spideynok in forum Import/Export Data
    Replies: 2
    Last Post: 03-18-2012, 09:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums