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?

Thx,
LVL 1
M ZahidAsked:
Who is Participating?
 
PatHartmanCommented:
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 & "\"
    Next

Open in new window

0
 
Ryan ChongCommented:
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.
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 :(
0
 
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.
1
 
PatHartmanCommented:
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.
1
 
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?
0
 
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.
0
 
M ZahidAuthor Commented:
can i have some example file to see how is work & all plz?
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.