Good afternoon all,
Been all over the place trying to find an answer but alas, still in the dark so here I am for some help.
We have an Access 2003 database that has linked tables to a SQL Server 2000 database. We have some users that use a SQL userid and password that gives them R/W access and another group with a different SQL Userid and PW that gives them RO access to the linked tables. All users use a network copy of the Access database.
We have been using system DSN's for a number of years with good luck but the organization is changing their workstation permissions to preclude the creation of System DSN's without a help desk technician logging in. We thought that a File DSN for the ODBC connection might solve our problem but I am not having any luck.
I have created the file DSN and placed it on a network drive. I then have an Access DB on the network that I link to tables in the SQL server db successfully on my workstation. When I log onto a second workstation and run linked table manager, it appears as if it is successfully hooking up but then I get an Invalid Object Name error #208. When I return to my original workstation, run linked table manager again, it appears to work fine but I get the #208 error and a 'statements can't be prepared' error #8180.
What I need to happen is to duplicate the effect the System DSN has. The users open the shared db once, run their linked table manager to hook up to the file DSN and then be able to just open the file, provide the password and go in the future.
Thoughts on what I'm missing?
Thanks,
Peggy
Been all over the place trying to find an answer but alas, still in the dark so here I am for some help.
We have an Access 2003 database that has linked tables to a SQL Server 2000 database. We have some users that use a SQL userid and password that gives them R/W access and another group with a different SQL Userid and PW that gives them RO access to the linked tables. All users use a network copy of the Access database.
We have been using system DSN's for a number of years with good luck but the organization is changing their workstation permissions to preclude the creation of System DSN's without a help desk technician logging in. We thought that a File DSN for the ODBC connection might solve our problem but I am not having any luck.
I have created the file DSN and placed it on a network drive. I then have an Access DB on the network that I link to tables in the SQL server db successfully on my workstation. When I log onto a second workstation and run linked table manager, it appears as if it is successfully hooking up but then I get an Invalid Object Name error #208. When I return to my original workstation, run linked table manager again, it appears to work fine but I get the #208 error and a 'statements can't be prepared' error #8180.
What I need to happen is to duplicate the effect the System DSN has. The users open the shared db once, run their linked table manager to hook up to the file DSN and then be able to just open the file, provide the password and go in the future.
Thoughts on what I'm missing?
Thanks,
Peggy
