Results 1 to 6 of 6
  1. #1
    Entellex is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    14

    Access DB Backend / Excel Frontend

    I want to pull in data from SQL Server and store it in Access. I then want to be able to have Excel use Access as the source for data to create reports.



    I plan on making a report on Excel for several people to use at once, but when I have the Excel opened and connected to Access, I can't have Access opened as well, because it only opens as read-only.

    Is there a way around this? So that I can still work on the Access DB if someone is using the Excel report?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use access solely as the frontend.
    access should be the app that creates excel sheets, not the other way.

  3. #3
    Entellex is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    14
    I need users to be able to have the ability to refresh that report when new data arrives. I don't want to distribute reports everyday.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    How do you connect the Excel workbook to Access?

    I have an Access database (back-end and front-end). For testing I just opened a new Excel file, created there an ODBC query to read the main table from back-end of Access application, and got the table read into Excel. After that I leaved Excel workbook open, and opened the Access application - without any problems. Then I refreshed the query table in Excel workbook with Access application opened - again without any problems.

    Btw. Are you doing some additional data entry in your Access database? When not, then you can read needed data into Excel using ODBC query directly from SQL server. When you need some calculations with read data, then you can create a view on SQL server, which presents data exactly as you need them in your Excel report, and you read the view into Excel.

  5. #5
    Entellex is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    14
    Quote Originally Posted by ArviLaanemets View Post
    How do you connect the Excel workbook to Access?

    I have an Access database (back-end and front-end). For testing I just opened a new Excel file, created there an ODBC query to read the main table from back-end of Access application, and got the table read into Excel. After that I leaved Excel workbook open, and opened the Access application - without any problems. Then I refreshed the query table in Excel workbook with Access application opened - again without any problems.

    Btw. Are you doing some additional data entry in your Access database? When not, then you can read needed data into Excel using ODBC query directly from SQL server. When you need some calculations with read data, then you can create a view on SQL server, which presents data exactly as you need them in your Excel report, and you read the view into Excel.
    I am using the "From Access" on the Data Tab.

    I found a solution though. DATA -> Connections -> Definition Tab -> Connection String: Change Mode=Share Deny _______ to Share Deny None

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    I use DATA > From Other Sources > From Microsoft Query, and then select "MS Access Database" and from there on. This gives you read-only connection always.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-01-2013, 09:11 AM
  2. Replies: 2
    Last Post: 05-21-2013, 02:25 PM
  3. Replies: 2
    Last Post: 02-16-2013, 12:35 PM
  4. Access Switching to frontend and backend
    By caliskier in forum Access
    Replies: 4
    Last Post: 11-19-2012, 11:58 AM
  5. Passwording frontend / backend Access 2010
    By erkwong in forum Security
    Replies: 0
    Last Post: 01-30-2012, 01:28 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