Access Data based linked with Excel file

Hello Sir / Madam,

I have Access Data base & linked with the 2 different Excel file for test run, & which runs perfectly however when I try active Share Workbook for the multiple users can work on the excel files but I cant.
Therefore kindly help is there any option where I could share the excel file with the access database linked?

M ZahidAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Therefore kindly help is there any option where I could share the excel file with the access database linked?
try put these 2 Excel files in a shared directory that is accessible for all your users.
Gustav BrockCIOCommented:
It's my experience that you can't.
If either Excel or Access has the file opened, at the best the next application (Excel or Access) can gain read-only access.
M ZahidAuthor Commented:
thx for the response Sirs,

@Ryan file are already in the shared directory but only one user can use or update & i cant active Share Workbook option for multiple user.  

@Gustav not getting the read only even boht Excel or Access are edit able, but problem same as above :(
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
Then you must find another way to set this up.

Have in mind please, that Excel in its core is a single user application. The shared feature is somewhat half-hearted, and the on-line version (Office 365) is a completely different animal behind the scene.
Access does not play well with others and will not share a file that is already opened - period.

If you have to import this data while others have the file opened, you can use FSO to copy the file to a temp folder where you will have complete control.  You can then link to the file and do your thing.  At the end, delete the copy.
M ZahidAuthor Commented:
Thanks for enlightening comments,

@PatHartman – sorry but what is FSO, I didn’t get that actually.

@Gustav – another way which comes in my mind is that same Access Data base LINKED with other access working files & put on share mode for the multiple users, instead of Excel? Can we do that?
Gustav BrockCIOCommented:
Yes. An Access database with tables (called and backend ) can be linked to multiple frontends - the Access application with forms, reports, etc. - one copy for each user.
M ZahidAuthor Commented:
can i have some example file to see how is work & all plz?
Gustav BrockCIOCommented:
No. But do create a copy/backup of your database, then follow the official guide:

How to manually split a Access database in Microsoft Access
FSO = File System Object.

You should find examples of copying a file from one place to another and renaming it in the process.  Here's something that will give you a framework.  You need to populate FromFileName and NewFolderName.

Don't forget to set a reference to the Microsoft Scripting Runtime.FSO.JPG
'' requires a reference to Microsoft Scripting Runtime

    Dim fs          As Scripting.FileSystemObject
    Dim folder      As Scripting.folder
    Dim file        As Scripting.file
    Dim filefolder
    Dim FromFileName As String
    Dim NewFolderName As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set folder = fs.GetFolder(SourceFolderPath)
    Set filefolder = folder.Files

    For Each file In filefolder
	fs.CopyFile Source:=FromFileName, Destination:=NewFolderName & "\"

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.