Trying to copy and overwrite a file

[pegicity]

Having issues of users leaving excel workbooks that contain pivot tables linked to a db open, users are located all over the region so it is not as simple as walking around the office asking everyone to close out their files. I am trying to write a sub to copy and paste the newest version of my db over the old even while there are active connections, is there any way to force this? Currently my code is a simple

FileCopy "S:\path\x.mdb", "S:\path2\x.mdb"

Obviously if a file pointing to this db is open it throws error 90. Is there any way to either

1) kill connections to the db or
2) force the paste anyway?

Thanks in advnace for any insight, havn't found answers anywhere!

[xzorxx]

you can make your db multiple, so more than one person can use it at same time
from tools go to option then advance option you will find something about default open change it to from private to shared
good luck

[zmbd]

@xzorxx: if more than one can open then it is already in a shared state.

@pegicity: have you tried to open a normal Access session on the file? Depending on recordlocks, you should be able to alter the data directly from Access. Then the users when they refresh the pivot tables should pull from the new dataset.

[NeoPa]

It's common practice to allocate a separate front end to each user. This is because of this, and other similar, problems involved with multiple users accessing the same database file directly.

I suspect you would find this approach gives you fewer problems.

[zmbd]

Neo, I think that's already happening... just in excel not access.
IF they use the ribbon/data approach in excel to the tables then the db file can be locked, even from an Access front end as the connection default is shared deny write.

What They have to do in excel is:
File Open - Show All Files
Select the Access DB
In the advanced connections set share deny none
The connection string looks like this:
(sorry, not sure how to step this... yet)

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents and Settings\z\My Documents\MS_Database_Projects\BytesWork\Bytes_peopletemplate.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

The table is opened in a worksheet and there you have it... change the settings to refresh in background and set the timer to 5 or 15 or 60 minutes as you desire.
You can then use this table/worksheet as one would for any other operation such as a pivot table.

Then Access program can get at the data too... (well so can everyone).

[pegicity]

Thanks for helping me out everyone, unforetunetly Zmbd's answer (while correct) isn't exactly what I was looking for.

Let me re-describe the situation. I am in the reporting unit for a company, my entry level job is to maintain a series (few dozen) of databases that the financial managers point to with excel pivot tables (to use Cognos or a similar SAP product would cost millions to set up and maintain, and the company doesn't find it worth while).

So, while your solution is correct, it isn't really feasable for me, as I have no idea how many reports have been created for each database. As you know it is relatively easy to create a pivot table pointed to a shared db. What I really need is some way to force an unlock from the db end of things.

[zmbd]

Yes, I understand your situation, no need to re-describe.

If each user gets to create their own worksheet to pull the data, then you maybe out of luck as there is NO built in method to force a user to close the connection to an Access database.

Your only hope is to have IT force log them off. This may corrupt your data so be sure to have backups!

[pegicity]

UPDATE:

I found that by using a holding database that is simply a mirror of the master file (but all tables are smiply linked tables to the master file) I can freely update the source, which in turn updates the linked database holding file which is now the database that excel locks. Sure it is bush league, but it works!

Thanks to everyone who took the time to take a look. Yes I know this is a terrible way to run things but re-organizing the reporting unit is beyond my pay grade.

[zmbd]

Hmmm... learned something new!

How is the performance? Are the end user's seeing a lag in the data updates? With a double link (main-db>mirrored-db>excel) each time they want data, the user re-queries the link, which results in the mirrored-db re-quering its link.

[pegicity]

Thankfully as this is just a reporting unit, the tables never get above 200 or 300K records as opposed to our SAP database that tracks actual transactions which gets to millions easily. So it might take 10 or 15 seconds, but seeing as it is better than tracking down a few hundred or so reports and trying to enforce a new connection precdure, I will take it.

[NeoPa]

I reset the Best Answer as we generally don't encourage members to assign it to their own posts. That is to say, it's unusual that they have posted something new and not resulting mainly from others' work.

On this occasion, though, it seems it does make sense to assign that post as Best answer, so I have redone it for you.

BTW. Good thinking. Clever approach.

[pegicity]

@NeoPa sorry about that was my first time actively taking part in this sub!

If it confuses anyone else why Excel would want to lock a file even when it is not being actively used (in this case, from a refresh point until the workbook is closed) I have found a way to ensure this does not happen. Now for me this was not an option as mentioned.

There are multiple way to select data sources for a pivot table in Excel. If you use the simple "Insert > Pivot > external data source" and attempt to link in the way I have used in this thread, you will NOT find the linked tables (previously created files will still find them just fine, but new ones will not). You will need to create a SELECT query that selects the entire table and point your pivot at that. This results in the same sort of file locking I was having problems with, but with this holding database you will be fine.

If you are the one creating and disseminating the reports, or are teaching the users how to create their own, there is another way to create a pivot table that points to your access database that will NOT keep the file locked after the refresh is completed. Under the Data ribbon you will find "From Other Sources" and then "From Microsoft Query". Should you point at the access database this way the file lock will not be an issue, you can over-write the database at will no matter how many reports are currently open and linked to it.

Why? Well I am sure Neo or Zmbd can explain it better but I will show you the difference in coding when I record the macro.

For the first import method it looks like this:

Workbooks("Test.xlsx").Connections.Add _
"<DATA CONNECTION NAME>, """" , " & _
      " Array( " & _
        "OLEDB; Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Password="""";User ID=Admin;" & _
        "Data Source=S:\<DATABASE>.mdb;Mode=Share Deny Write;" & _
        "Extended Properties="""";Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;" & _
        "Jet OLEDB:Database Locking Mode=0; Jet OLEDB:Global Partial Bulk Ops=2;" & _
        "Jet OLEDB:Global Bulk Transactions=1; Jet OLEDB:New Database Password=""""; " & _
        "Jet OLEDB:Create System Database=False; " & _
        "Jet OLEDB:Encrypt Database=False; " & _
        "Jet OLEDB: Don't Copy Locale on Compact=False; " & _
        "Jet OLEDB:Compact Without Replica Repair=False; " & _
        "Jet OLEDB:SFP=False; " & _
        "Jet OLEDB:Support Complex Data=False; " & _
        "Jet OLEDB:Bypass UserInfo Validation=False), " & _
     "Array(""<NAME OF QUERY/TABLE IN DATABASE>""), 3 " & _
     "ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, " & _
     "SourceData:= ActiveWorkbook.Connections(""<NAME OF CONNECTION>""), " & _
     "Version:=xlPivotTableVersion14).CreatePivotTable " & _
     "TableDestination:=""Sheet1!R2C1"", " & _
     "TableName:= ""PivotTable8"", " & _
     "DefaultVersion:=xlPivotTableVersion14"

(Really sorry for the awful code, it IS a recorded macro after all) You can see it uses the OLEDB and that nasty Share Deny Write Zmbd mentioned. BUT the second method looks like this when recoreded:

Workbooks("Test.xlsx").Connections.Add "Query from MS Access Database3", "", _
        Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=S:\<DATABASE.mdb>;DefaultDir=S:\;DriverId=25;FIL=MS Access;MaxBuf" _
        ), Array("ferSize=2048;PageTimeout=5;")), Array( _
        "SELECT <DATABASE>.Division" & Chr(13) & "" & Chr(10) & "FROM `S:\DATABASE.mdb`.<TABLE IN DATABASE> <TABLE IN DATABASE>" _
        ), 2
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("Query from MS Access Database3"), Version:= _
        xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R1C1", _
        TableName:="PivotTable7", DefaultVersion:=xlPivotTableVersion14

please note anything <IN CAPITALS BETWEEN THESE SIGNS> was changed for security reasons.

Short and clean (well relatively) with no write lock. As far as I can surmize from research OLEDB is simply a newer import standard that allows a wider variety of data sources, but default when importing this way (the ribbons I already described) it defaults to a Share Deny Write, an expression not available/used when the ODBC is called. I am SURE this isn't all right, but the end result is what matters most to me, and changing the connection method is easier than remembering to edit out the Share Deny Write each time.

[NeoPa]

@NeoPa sorry about that was my first time actively taking part in this sub!

As I say, apologies not required. In this case it became clear that what you posted was very much worthy of being flagged as a Best Answer. You've actually provided a great deal of helpful information in this thread. I'm sure many members (of this forum and the public) will come across this thread in future and find it very helpful. Your further explanations give it even greater value.

[Timoo]

Indeed I came across this post and found it very interesting.
The linking is indeed a clever solution to de-attach your master data.

And the querying in Excel: I did not even know this option!
Clever guy you are :-)