January 17, 2007 at 10:11 am
I confess that this is probably a stupid question, but I want to be sure of the answer before going to a lot of trouble:
Is it possible to import from an Access db (using SQL Server 2000 DTS) when the server where the Access db resides does not have MS Office installed on it?
Thanks in advance!
January 17, 2007 at 11:16 am
Yes it is. You don't need Access on the SQL server either.
January 17, 2007 at 1:42 pm
Thank you Erik. This brings me to my next question.
I have a DTS package that I've been using for quite a while to import data from an Access db located on another server (via VPN connection) to a Sql Server db. I've never had a problem running this package until today. Suddenly I'm getting the following error message:
Error Source: Microsoft JET Database Engine.
Error Description: The Microsoft Jet database cannot open the file ‘\\[my ip address here]\c$\Openings Database\openings.mdb’. It is already opened exclusively by another user, or you need permission to view its data.
The file is definitely not already opened by any user. The outside party maintaining the Access db assures me that the permissions on the db have not been modified since the last import (I checked, and that appears to be true).
I'd appreciate it if anybody can give me some insight on what might be causing this problem. Also, if permissions need to be set up for a particular user, how do I know which user DTS connects with (ex: 'ADMINISTRATOR', 'SYSTEM', 'Users') and what permissions should be established to be able to import from this db. Do permissions need to be set for the server itself (the one where Access db resides), the directories and the folder?
Please forgive the detailed questions -- I'm comfortable with db development, but am inexperienced on the admin side.
Thanks again!
January 18, 2007 at 1:54 am
Hi,
When you receive this message check the folder where the .mdb resides and check if there is the corresponding .ldb file.
Sometimes on networks when an access db closes or even if the PC is switched off whilst the .mdb is still open, MS Access does not close the locking file. if the locking file has not been closed then try opening the .mdb then closing it, if that does not work then shut down the PC, power back up then delete the locking file.
January 18, 2007 at 8:03 am
Thanks for the response!
FYI, MS Office is not installed on the server where the Access db file resides (hence the original question). As a result, I'm unable to open the file on this server. I can also confirm that there is no corresponding .ldb file.
Also, I've restarted the machine with the hope that whatever the issue is would resolve itself, but that didn't work either. I'm still getting the error message.
?????????
January 19, 2007 at 1:32 pm
There are complicated permission issues with asking a server to use the Jet driver to open a file on another server. Try to figure out what the exact issue is. It could simply be file permissions on the remote server. A more complicated possibility is if your Active Directory domain doesn't fully support Kerberos authentication, so your Windows credentials don't get passed around correctly. Windows 2003 can also get picky about SQL connections starting another process to run the Jet driver.
Can the SQL server read the file? Or, to be more precise, can the SQL Server service running on the SQL Server read the file? Try running "xp_cmdshell 'copy <URL>\file.mdb <local path>\file.mdb' ". If it doesn't work, the SQL Server service account may not have rights to the files on the other server. Is SQL Server running under a local system account that doesn't have network rights? I'm not positive, but you may need write privileges to the source folder to create the .ldb file even if you're only planning to read from the .mdb.
Can you get the server to read a local Access file? If you can copy the mdb file to the SQL Server, will the DTS package read it? If it doesn't work with a Windows-authenticated connection try a SQL login, maybe even sa.
Can you import data from a different Access database in the same folder, or maybe from another network share?
If the answer is you can't open any Access file, or only local files on the SQL Server, maybe your network admin can help. If you can open network files but nothing from the remote server, it is also probably a network admin question. (Is the remote server in the same domain?) If you can open every Access file in the world except this one, and the file isn't corrupt, there is probably some custom security set up in Access that you're not aware of.
January 22, 2007 at 2:09 am
It sounds like the Access db has become corrupted. Suggest you ask the 3rd party responsible to check it out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply