December 9, 2004 at 7:44 am
Hello,
I have a DTS package that migrates data from Excel to Access to SQL Server. Before that occurs, at the very beginning, I have a delete script that deletes the existing data in the Access database. When I hit that first step, it gives me the error:
Microsoft JET Database Engine (-2147467259) - The Microsoft Jet database engine cannot open the file '<path to access .mdb>'. It is already opened exclusively by another user, or you need permission to view its data.
I checked the permissions and the account that the web site is running under (impersonated) has full permissions to the file. Also, I am running the web site on my machine, I'm triggering the DTS package through .NET code, the access database is on a file/print server, and the SQL Server is a separate machine.
I'm thinking that maybe the issue is that the access database is on a separate machine, but I'm not quite sure.
Brian
December 9, 2004 at 7:55 am
I've already had a similiar problem but not in dts. This may sound simplistic but is it possible that the database has been opened by your dts but not closed? Try killing all msaccess apps running on the server and your machine (using task manager in case there's an invisible window) and then try again.
If that doesn't work try to find the locking file of the db (MyDbName.ldb) and try to delete it (if you're sure nobody's using it).
December 9, 2004 at 7:57 am
The locking file should exist in the same directory as the Access database, correct? I'll check the database to see if access is running. I never thought of that. Is there a way I can kill the process, as I don't want to have this happen everytime?
Thanks for your help.
December 9, 2004 at 8:11 am
yes the locking file is in the same directory and same filename.
You'll have to kill the process manually this time but for the next times make sure that everytime you open the db you close it (in the same task if possible). Or at least make sure there's no way to NOT close the db after the dts has finished running.
That'll save you a lot of problems.
December 9, 2004 at 12:43 pm
I checked the server that has SQL Server, no process running there. I tried to have the network guys check the processes for the file/print server, but they have been out all day. I changed the DTS package to close the connections whenever dealing with access.
Alternatively, I tried to go out to the access DB and open it manually and was successful. Also, I kicked the package off manually and it worked.
Brian
December 10, 2004 at 4:33 am
I have simalar situations and I found that it is better to move data from Excel to SQL and then make it available to Access. Is there something specific that Access is doing with the data that SQL could do better?
Eric Swartwout
December 10, 2004 at 6:17 am
Yes actually, when I go directly from Excel to SQL Server, I get an error stating that the data is too large for the field. I checked and in every instance it wasn't, So I bumped up the size of the field to the maximum 8000 chars to no avail.
However, when I went Excel to Access to SQL Server, I set the "problem" fields to type memo, then inserted into SQL Server, and I didn't have that problem. Worked real nice too.
Brian
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply