Results 1 to 8 of 8

Import Access Database into Excel in Multi-User Environment

  1. #1
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8

    Import Access Database into Excel in Multi-User Environment


    Hello, I have an Excel file that imports data from a query in an Access database. However, if someone else has the Access database open, the Excel file is not able to open the database. Is there a way around this? Would a split database allow a user to have the database open and another import data into Excel since each would be opening a different front-end database? Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,746
    its not access, its excel. 2 people cant have the excel open if you have it as an attached table.
    Which means only 1 user in access can use it.

    Instead of an attached table, import the excel sheet to a table and no external file will remain open.

  3. #3
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8
    Thanks for the reply. I may have worded it bad, I am pulling data from Access into Excel to analyze/plot. If one person is in Access, the VBA script I have in Excel cannot open the Access database to import the data into Excel. Maybe you answered this and I am not understanding. Thanks

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    If you split the Access DB you should be able to access the back end from other Access applications and Excel. Having said that, I have not actually tried doing this. I suppose it is possible that Excel can lock some tables when it connects to them. To be honest, I am not sure why you cannot connect to an Access db that is opened. I would expect opening it in design mode or exclusive mode would lock it. Otherwise I would expect multiple simultaneous users being capable of accessing the same file.

  5. #5
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8
    I set this problem aside for a while but just attempted a simple split database. I have a front end with a form that has one field linked to the table in the back end. I found that if I have the front end database open but the form (and table) closed, I can go to Excel and import the database. In Excel when I click Get External Data -> From Access, I get a "Import Data" pop-up and can click OK to import. However, if the form in the front end database is open I get a different pop-up that is called "Data Link Properties". Is there a way to set these properties to import the back end table at the same time the form in the front end is open? Maybe change something about the table that is locking it when the front end form is open? The other thing I noticed is if I import the table into Excel and then go back to Access and try to open the front end form, the fields from the back end table do not show up (the test one I made and record ID).

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    If I am understanding correctly you have an Access Backend file and an Access Front end file. When you have the Access front end file open and open a form that looks at the backend, you can use Excel to import data from the Access backend to the Excel spreadsheet.

    Perhaps the form you have open in the Access Front End file is locking a record on the backend. Also, maybe you can have Excel create an ODBC connection to Access instead of the Import procedure, you are describing.

  7. #7
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8
    I almost have the solution. If I go into Excel and try to import the table while the database is open, I get the 'Data Link Properties' pop-up as I mentioned. In the 'Data Link Properties' I found that I need to change the 'Access Permissions' from the default 'Share Deny Write' to 'Read'. Once I do that I can import the database into Excel while it is also opened. The final issue is figuring out how to automatically make the connection 'Read' only. If I open Excel and import the database when the database is closed, it does it automatically and does not give me the option to change the permissions. This prevents me from opening the database until I close Excel. I am using the following VBA to import the database in my real application, which is based on a recorded macro I made. I tried changing it from Mode=Share Deny Write; to Mode=Read; in the 4th line, but when I run the code it seems to ignore this and still give the 'Data Link Properties' pop-up with only the 'Share Deny Write' option checked. Any ideas?

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password= """";User ID=Admin;Data Source=DatabasePath;" _
    , _
    "Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB[IMG]file:///C:\Users\Dan\AppData\Local\Temp\msohtmlclip1\01\cl ip_image001.png[/IMG]atabas" _
    , _
    "e Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB[IMG]file:///C:\Users\Dan\AppData\Local\Temp\msohtmlclip1\01\cl ip_image001.png[/IMG]atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _
    , _
    "k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _
    , _
    "OLEDB[IMG]file:///C:\Users\Dan\AppData\Local\Temp\msohtmlclip1\01\cl ip_image001.png[/IMG]on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Co" _
    , _
    "mplex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Vali" _
    , "dation=False"), Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("LayerCalibration")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = DatabasePath
    .ListObject.DisplayName = "DatabaseTable"
    .Refresh BackgroundQuery:=False
    End With

  8. #8
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8
    Figured it out. I recreated the import macro when the database was opened. This way I was given the 'Data Link Properties' pop-up and could set it to read, and this makes the macro run correctly. I also added a VBA command to delete the connection after the table has been read from Access so that Access can be opened if the Excel file is already opened (I don't need it to periodically refresh, and have a button to manually refresh the data if needed). Maybe someday I'll figure out exactly what the VBA code is doing for importing the database and what was wrong with changing the orignal VBA to read only as shown in my last post, but for now this works. Thanks for the help.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-24-2015, 01:49 PM
  2. Split DB Question - Multi User Environment Launch
    By aussie92 in forum Database Design
    Replies: 6
    Last Post: 01-22-2014, 03:19 PM
  3. Replies: 3
    Last Post: 06-14-2012, 08:10 AM
  4. Record Locking Multi User Environment
    By praetorianprefect in forum Database Design
    Replies: 5
    Last Post: 04-16-2012, 02:31 PM
  5. Replies: 13
    Last Post: 05-18-2010, 01:12 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
  •  
Tech Forums: Microsoft Office Forums