|
|
I have an application written in Access 2000 using linked tables to SQL
Server 2000. The Access database sits on a fileserver where it is used by 10-
15 users. All users have the same ODBC DSN on their PC's and the linked
tables work well - with all users using the same SQL Server logon. However,
I would like to implement SQL Server security and give each user their own
SQL logon. I like having all users share the same MDB on the fileserver
because it makes distributing new releases easier.
How can I link tables in Access 2000 so that each user uses his/her own SQL
logon and distributing new versions of the MDB does not require visiting each
desktop?
Thank you in advance.
|
|
"richxyz" <u35439[ at ]uwe> wrote:
[Quoted Text] >I have an application written in Access 2000 using linked tables to SQL
>Server 2000. The Access database sits on a fileserver where it is used by 10-
>15 users. All users have the same ODBC DSN on their PC's
I much prefer DSN-Less connections as it is one less thing for someone to have to
configure and one less thing for the users to screw up. This is also better for
Citrix/TS farms where each individual system would have to have a DSN created and
maintained.
Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html
ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor01.htm&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm
Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm
> and the linked
>tables work well - with all users using the same SQL Server logon. However,
>I would like to implement SQL Server security and give each user their own
>SQL logon.
Why not use Windows authentication to SQL Server?
>I like having all users share the same MDB on the fileserver
>because it makes distributing new releases easier.
No it doesn't. To make updates to a shared MDB on the file server requires that
all your users have to exit the shared FE. And your users get some bizarre errors.
You're quite fortunate that it has lasted this long without corruption.
I specifically created the Auto FE Updater utility so that I could make changes to
the FE MDE as often as I wanted and be quite confident that the next time someone
went to run the app that it would pull in the latest version. For more info on the
errors or the Auto FE Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up
to date.
>How can I link tables in Access 2000 so that each user uses his/her own SQL
>logon and distributing new versions of the MDB does not require visiting each
>desktop?
See above comments..
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
Tony,
Thank you very much for your valuable answers! I cannot use Windows
Authentication because the client's network is a mess. The network is a peer-
to-peer network with no domain server or anything. Thanks again for your
answers!
Rich
|
|
"richxyz" <u35439[ at ]uwe> wrote:
[Quoted Text] >Thank you very much for your valuable answers!
You're welcome.
>I cannot use Windows
>Authentication because the client's network is a mess. The network is a peer-
>to-peer network with no domain server or anything.
Ahhh, now that was a response I wasn't expecting. <smile> I don't know what the
best answer would be for that situation. We'll see if someone else jumps in here.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
"richxyz" <u35439[ at ]uwe> wrote:
[Quoted Text] >Thank you very much for your valuable answers! I cannot use Windows
>Authentication because the client's network is a mess. The network is a peer-
>to-peer network with no domain server or anything.
I do wonder though how they got more than ten users if they are still using peer to
peer. Or do they indeed have workgroup security on some kind of Windows server?
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
Perhaps it's not peer to peer - my mistake. I am not network savvy at all.
I know that users all log into their local machine and are able to gain
access to internet, the fileserver and SQL Server (all that is needed.)
There is a domain (called "DOMAIN") but no one, even the network
administrator, knows how to log into it! hehehe.
By the way, Tony, I just wrote a 2-line batch file that copies an MDB file
from the fileserver using xcopy x.mdb /m and then launches the local copy.
Wouldn't this accomplish the same thing as your code that copies the latest
version?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200706/1
|
|
"richxyz via AccessMonster.com" <u35439[ at ]uwe> wrote in message
news:746c53b083684[ at ]uwe...
[Quoted Text] >
> By the way, Tony, I just wrote a 2-line batch file that copies an MDB file
> from the fileserver using xcopy x.mdb /m and then launches the local copy.
> Wouldn't this accomplish the same thing as your code that copies the
> latest
> version?
I believe Tony's routine only copies when necessary, thus potentially saving
a lot of network traffic.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
|
|
You'd could alter the code in
http://www.accessmvp.com/djsteele/DSNLessLinks.html to include the
appropriate ID and password, prompt the user for those credentials, then
relink all of the tables and pass-through queries with the correct
information. As an added sophistication, you could check whether they're
already linked with those credentials, and not bother relinking unless
necessary.
Actually, since the user credentials would be stored as part of the Connect
property, it might be better simply to have a table that holds the details
of what tables need to be linked, and then automatically creates the linked
tables when the application opens, and deletes them when the application
closes.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"richxyz" <u35439[ at ]uwe> wrote in message news:746731e9e1b98[ at ]uwe...
[Quoted Text] > Tony,
>
> Thank you very much for your valuable answers! I cannot use Windows
> Authentication because the client's network is a mess. The network is a
> peer-
> to-peer network with no domain server or anything. Thanks again for your
> answers!
>
> Rich
>
|
|
|
|
Yes, but I believe that's based on file date. Every time you use an MDB, the
file date changes.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"richxyz via AccessMonster.com" <u35439[ at ]uwe> wrote in message
news:746ca45d49695[ at ]uwe...
[Quoted Text]
|
|
"richxyz via AccessMonster.com" <u35439[ at ]uwe> wrote:
[Quoted Text] >Perhaps it's not peer to peer - my mistake. I am not network savvy at all.
>I know that users all log into their local machine and are able to gain
>access to internet, the fileserver and SQL Server (all that is needed.)
>There is a domain (called "DOMAIN") but no one, even the network
>administrator, knows how to log into it! hehehe.
Sheesh.
But still for them to access the file server would require an account on that file
server. So maybe they should consider setting up a whole new server and doing it
right.
>By the way, Tony, I just wrote a 2-line batch file that copies an MDB file
>from the fileserver using xcopy x.mdb /m and then launches the local copy.
>Wouldn't this accomplish the same thing as your code that copies the latest
>version?
Yes, and no. My auto fe updater also creates shortcuts, .cmd files and does much,
much more.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
|