March 22, 2006 at 5:16 pm
Someone in our organization is using and Access database with a workgroup file and now I have to take information out of that on a regular basis and put it into a table in SQL. The problem is when I try and do the import I get an error indicating that there are no read permissions on that table I am trying to copy. I am figuring it is because of the workgroup file but don't now how to get around it.
I've searched some and in BOL found something indicating that I should add the wrokgroup information file to the registry. I then would have to link that database. Here is the issue though. The database is not on the SQL Server. (BOL indicates that it needs to reside on the server.) If I do this I also don't want to loose the current registry key to the SystemDB because there are other mdb files that we import from as well. Will the two keys "play nice" together?
March 27, 2006 at 7:18 am
If you use dts, you can provide a username and password in the connection that will validate the login and allow you to select from a table.
If you have never created a dts package, it is very easy to do. There are plenty of help files on msdn site as well as others such as http://www.sqldts.com.
March 27, 2006 at 8:56 pm
That is what I thought but when I tried it, it tells me that the login is invalid because the workgroup file is missing. I'm not sure how to indicate to SQL where the workgroup file is.
March 28, 2006 at 1:16 am
If I remember rightly, when you start ms access it needs to find the workgroup file and authenticate the user.
In order to get this to work, I think you need to install ms access on the sql server box and register the workgroup file there. After that, you shouldn't have a problem connecting.
I know it is not ideal, but workgroups are very cumbersome and awkward.
March 28, 2006 at 11:01 am
Yes, workgroups are very awkward and I try and avoid them at all costs. Unfortunately, I kind of inherited this.
Anyway, I was able to solve the issue by just going into the advanced settings on the connection with Access and setting the Property for Jet OLEDB:System Database value to <wg_file_name>.mdw.
March 29, 2006 at 2:24 am
Nice workaround. Workgroups must be the worst type of security EVER on any database and caused me major headaches when I used to administer a pool of access databases back in the 90's.
March 29, 2006 at 9:20 am
My headache is getting worse. It was all running fine yesterday but now is failing. I changed the drive mappings to UNC and that seems to have corrected it. However, I set up a job to run automatically every night and that fails. It gets through the package until it comes to transferring the data. Then I get the following error:
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
March 29, 2006 at 10:04 am
Do you need to have the security on the Access database? I have always controlled who has access to the database through network security. We never had to get granular down to the form or table level. If you could control it that way, you could just create a new Access database and import everything into it without the security.
March 29, 2006 at 10:09 am
The preference is to have security on it because the forms within the access file restrict the users from certain things and if there isn't any security on it they can get around that.
March 29, 2006 at 10:19 am
Another thing you can do is convert the database to an Access project, then all the tables will be stored in SQL and you can have SQL control the security. I have converted all the original Access mdb files to Projects for our company.
March 29, 2006 at 3:50 pm
Did you try to move data from your secured database into a new MS ACCESS database without any security on the machine with the MDW file? Then move this new database to SQL Server computer and do import into SQL Server?
Also you may login to the secure database and link SQL Server new tables and move data.
Regards,Yelena Varsha
March 30, 2006 at 8:17 am
I have it working now. It was a permissions thing in the SQL Server Agent. Just gave the login for the SQL Server Agent rights to the directory on the other server where the mdb and mdw file are located and now it runs fine.
Thanks everyone for your input and ideas! They helped a lot.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply