MS Access Link table issue

Hi All, We have an issue as following, We have an excel file linked as a table into an access app, since the program is used by multiple users on a network and each user wanted to use their own copy of the excel file, we placed the file under (C:\Application\NameOfFile.xls)... However that worked well for local users in office that each uses their own pc.
Now we have this application also shared by remote users that access the server directly (thru remote desktop), how can we accomplish the above, meaning that the table name tablexls linked to an excel file should actually refer to each user separately for their own copy of the excel file, without the information being shared?
LVL 6
bfuchsAsked:
Who is Participating?
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.

Jeffrey CoachmanMIS LiasonCommented:
Please know that this is not the typical way a linked Excel table is setup.

Can you first explain why this system is setup this way, and what this system is tracking?
0
bfuchsAuthor Commented:
Hi thanks for replying,

we have a sql table of scheduling records and users are supposed to merge those record with another scheduling system that is on the web.

The way I instructed them to do is as follows

1-they open the web app, copy all the records and paste them into an excel sheet under a specific folder as described above.
2-then they have a function that displays all not matching records from the existing table and they have the option to select which ones the want to update and so forth.

The reason they don't want to share the same excel file is, since each user deals with their set of records, and all want have the ability to access and update their records at the same time.

If you have a better way of doing this, I am open for suggestions..
0
Jeffrey CoachmanMIS LiasonCommented:
so why cant the remote users simply do the same?
0
Challenges in Government Cyber Security

Has cyber security been a challenge in your government organization? Are you looking to improve your government's network security? Learn more about how to improve your government organization's security by viewing our on-demand webinar!

bfuchsAuthor Commented:
the following path "C:\Application" by the users connected remotely direct to the server refers to the C drive of the server, therefore everyone ends up opening the same file.

I guess what I would need here is one of the following

1-a way of making a certain folder that is named the same, but actually for each user it points to a diff place on the server  
2- programmatically  link to the excel file using windows variables that holds users info...
0
bfuchsAuthor Commented:
Hi boag2000,
Do you still need any clarification from my part?
0
Jeffrey CoachmanMIS LiasonCommented:
Nope,

I am waiting for other experts to chime in...
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
The trick I use and recommend is a mapped drive letter.

I set up all my client's servers so that every user has Z: mapped to a person folder on a server share with a log on script. You could use any drive letter. I use Z since that is what Windows SBS uses. This is where I put my front ends and other non shared files.

Example using a mapped drive letter path:

Z:\Application\NameOfFile.xls
0
bfuchsAuthor Commented:
Hi, don't get it how will this single letter map to each individual user as a different location on the same server? I see you mentioned about a script, do you have an example to show?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Hi, don't get it how will this single letter map to each individual user as a different location on the same server?
Every user will have the same drive letter mapped to their own person folder that is not shared.


I see you mentioned about a script, do you have an example to show?

Do you have an IT department/person that can help with this? If yes, you really should work with them on this to set it up best for your environment.

To know hat kind of example to give you it would help to know:

1) What version of Windows Server you are running as the Terminal Server?
2) Do you have administrator permissions on the Terminal Server?
3) Do you have administrator permissions on the Primary Domain Control (PDC) server so you can edit Active Directory?
4) Do you already have a share set up on the server where every  users can have a personal folder? What is the share name (example: \\server\users)?
0
Jim P.Commented:
If you open a DOS prompt and type in set you will get the list of variables. One of them should be USERPROFILE and another is HOMEPATH. So if you do a batch file that is along the lines of
net use Z:="%USERPROFILE%\My Documents"

Open in new window

It will make a personal directory for each person.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:

It will make a personal directory for each person.

Just to be clear, it does not "make"(create)  a personal directory for each person.  

NET USE will assign a driver letter to an existing folder or network share. It does not create any folders. The folder must exist before you execute the NET USE command.

My preferred method is to use the Profile properties for the user in Active Directory. You can use this to run a log on script that would make the drive using NET USE and/or set the Home folder.  Whatever method you use you should plan it out and document it so it can be consistently/properly maintained.

User Properties
0
bfuchsAuthor Commented:
Hi thanks to all for replying,
@jimpen,@TheHiTechCoach: Will this approach work also dynamically in access, meaning instead of currently have this excel file linked as "C:\Application\NameOfFile.xls" or "Z:\..." as you're suggesting, I can get it linked to each users personal mydocuments ? I would prefer linking it this way when the program opens, this way I don't have to dig into our IT guys work..
0
Jim P.Commented:
You can do a map of the drive during the Access startup. But you can also do it by using the environ("USERPROFILE") to access the system variables.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I can get it linked to each users personal mydocuments ? I would prefer linking it this way when the program opens,
Yes that is possible. You will need to make sure that each user has there own copy of the front end. Every time you deploy updates you will have to relink.

Since you will be relinking with code you don't need a drive letter since you can determin the absolute  path with code. as using environ("USERPROFILE")  as a  jimpen suggested.

Depending on the version of Windows OS where the "My Documents" folder is located.

Example for Win7\2008

FilePath =  environ("USERPROFILE") &  "\Documents\Application\NameOfFile.xls 

Open in new window

0
bfuchsAuthor Commented:
@TheHiTechCoach: thanks for letting me know that, so this is not an option, Since we more often release new updates then setup new users.. I rather prefer to have IT guy set this up once than have to bother with that by each release.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I rather prefer to have IT guy set this up once than have to bother with that by each release.
That sounds like a great plan.  You probably will find additional uses for it. In the long run it will be worth it.
0
bfuchsAuthor Commented:
ok, will forward all this thread to our IT guy, let see what he has to say about it...
Thanks again for all responses, will let you know what we end up with.
0
bfuchsAuthor Commented:
Hi, Our IT guy just told me to find out the below

"Since this is not a domain controller I cannot have a logon script is there any way you could bypass by regular users to add a logon script?"
0
Jim P.Commented:
Since this is not a domain controller I cannot have a logon script is there any way you could bypass by regular users to add a logon script?

You don't need a login script. This is aircode below but it would be something like the VBA below to export the first time:
Public Function Q_28267991()

Dim Path As String
Dim FileName As String
Dim OSType As String

Path = Environ("USERPROFILE")
FileName = "NameOfFile.xls"

'I think Win7 and above does the C:\Users. XP and 2003 is C:\Documents and Settings
If Left(Path, 8) = "C:\Users" Then OSType = "Win7" Else OSType = "WinXP"

Select Case OSType
     Case "Win7"
        Path = Path & "\Documents\AppFiles\"
     Case "WinXP"
        Path = Path & "\My Documents\AppFiles\"
End

If Dir(Path, vbDirectory) = "" Then
    MkDir Path
    'this would be where you have a routine to create the SS
    ExportTheSS Environ("USERNAME")
End If

If Dir(Path & FileName, vbDirectory) <> "" Then
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "MyTableName", Path & FileName, True
Else
    'this would be where you have a routine to create the SS if it was accidentally deleted.
    ExportTheSS Environ("USERNAME")
End If

End Function

Open in new window

0
bfuchsAuthor Commented:
hi jimpen, thanks for the code, I'm out of the office for few days, will continue on this next week, have a nice weekend!
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
"Since this is not a domain controller I cannot have a logon script...

That is not correct.  You definitely can have long on scripts on the local machines. They are still used a lot.  

Add a script to the "start up" group of the start menu. You can add it to specific users or for all users (public).
0
bfuchsAuthor Commented:
Hi all,
@jimpen:
1-where is this code falls into? as we had two options one to make a mapped drive as Z, like  TheHiTechCoach suggested, however this would impose hardships while doing each release..as discussed, the other option was creating a link dynamically to the users file by vba code using system variables like you had suggested, which of those options does it apply?
2-you are referring in code example as export procedure, however my case is a import (actually just a link) that's necessary, would you have an example to show how programmatically link to an excel file stored in c:\documents and settings\user profile...\NameOfFile.xls?

@TheHiTechCoach:
forwarded to our IT guy, his response was that will have to work on each user/workstation separately, does not seem like he wants to get involved into this mess, at least at this moment... therefore my option here would be, try figuring out the easiest way out I can get this working without his involvement..
0
Jim P.Commented:
1-where is this code falls into?

If you know the file is located in a certain directory on the user's machine then this is easy. Some functions to make it easier. And each person should have their own front end. (Ask another question about version checking.) You can put some of this in a form that fires on startup. The way I prefer to do it is put all the code in modules and call the functions. That way if the form goes bad, I can just create a new one. It also encourages code reuse.

2-you are referring in code example as export procedure,

I was thinking the first time around you would have to do an export. The linking is just as easy. If you do "echo %userprofile%" in a command prompt then you see where the that is a system variable. If you want to see them all type SET at a command prompt.

The function below checks if an object exists in the DB:
Public Function TableExistence(TableName As String) As Boolean

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String

TableExistence = False

SQL = "SELECT NAME " & _
        "FROM  MSysObjects " & _
        "WHERE TRIM(UCASE(NAME)) = '" & Trim(UCase(TableName)) & "';"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = True Then
    TableExistence = False
Else
    RS.MoveFirst
    If Trim(UCase(RS!NAME)) = Trim(UCase(TableName)) Then
        TableExistence = True
    End If
End If

Set RS = Nothing
Set DB = noting

End Function

Open in new window

Then this will link in the SS
Public Function Q_28267991()

Dim Path As String
Dim FileName As String
Dim OSType As String

Path = Environ("USERPROFILE")
FileName = "NameOfFile.xls"

'I think Win7 and above does the C:\Users. XP and 2003 is C:\Documents and Settings
If Left(Path, 8) = "C:\Users" Then OSType = "Win7" Else OSType = "WinXP"

Select Case OSType
     Case "Win7"
        Path = Path & "\Documents\AppFiles\"
     Case "WinXP"
        Path = Path & "\My Documents\AppFiles\"
End


If TableExistence("MyTableName") = True Then
    DoCmd.SetWarnings = False
    DoCmd.DeleteObject acTable, "MyTableName"
    DoCmd.SetWarnings = True
End If

If Dir(Path & FileName, vbDirectory) <> "" Then
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "MyTableName", Path & FileName, True
Else
    MsgBox "File Not Found!", vbOkOnly + VBCritical
End If

End Function

Open in new window

0

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
bfuchsAuthor Commented:
Hi all, many thanks for all your great work, its a real pleasure to deal with you guys!!
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You're welcome. Glad we could assist.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
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 Access

From novice to tech pro — start learning today.