Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: MS Access FE MS SQL BE Best Idea?

    Hi All,
    I am very new on this forum and I hope that someone can provide me few ideas.
    I am forced to switch one MS Access database to MS SQL 2008 R2 version due to performance problems - MS Access 2010 still as FE to SQL. I have created stored procedure which is returning recordset from couple of tables and creating some extra columns (time calculations and etc. pretty complex). I have created function in Access which execute the store procedure (through ADODB) and assigned recorset returned by stored procerure to the form recordset. All fine, no issue. Howerver, I was a fool as this recordset is obviously not updatable from the MS Access form. Can anyone share idea what is the best way to pass data from MS SQL into Access form and return back to SQL Server? I cant link form directly to the table due to mandatory joins of tables and functions...

    Thank you very much for your help,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without more insight into what you are trying to do I can't be certain, but I'd bet that a view will do what you need. Read the portion about "updateable views" carefully, there are some things that you need to remember when designing/implimenting your view.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Hello Pat, thank you very much for your reply. I will check your link if view can do the job. In fact, what I am trying to do is following: I have to migrate Access back-end to the SQL server. There is a set of tables (one main table + set of reference tables), which I cant change as data must be migrated "AS IS". FE (build in MS Access) is linked to outlook account and donwloading emails and filling details (From, subject and etc.) into the main table. Users are then updating data in the main table (filling additional information, status of mail and etc.) and each change must be real-time visible to all users.

  4. #4
    Join Date
    Feb 2013
    Posts
    3

    Thumbs up

    Quote Originally Posted by Pat Phelan View Post
    Without more insight into what you are trying to do I can't be certain, but I'd bet that a view will do what you need. Read the portion about "updateable views" carefully, there are some things that you need to remember when designing/implimenting your view.

    -PatP
    Amazing! Pat, this is exactly what I need! I dont know why I have ignored views so far, thx. thx. thx.! Wish you nice sunday.

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
  •