IIS / ASP opening MDB using Access Application object

Hi,

I’m trying to open a database using an instance of Access through my ASP page, as follows:

 
 sDataFile = “R:\webapp\phonedata.mdb”
 Set oAccessApp = CreateObject("Access.Application")
 oAccessApp.Visible = False
 oAccessApp.OpenCurrentDatabase sDataFile
 

This works fine on my W98 development system, with PWS.  When I run this on the W2000 server, the Access application starts ok, but I just cannot open the database.  When the last line executes I get the following error:

MSAccess error '800a1eba'
Microsoft Access can't open the database because it is missing, or opened exclusively by another user.

This equates to the MS Access error 7866, I believe.

The MDB file definitely exists and is definitely not opened anywhere else (there is no LDB file).

I have tried every possible permutation and combination of the following:

1)       Using Server.CreateObject rather than just CreateObject (Access 97 is installed on all clients and the server)
2)       Running the code on a client or on the server
3)       Changing the sDataFile variable to:
   i)        The local path to the MDB file on the server – e.g. “C:\apps\webapp\phonedata.mdb”
   ii)        The URL path to the file – e.g. \\192.168.1.201\apps\webapp\phonedata.mdb
   iii)       The mapped path to the file, e.g. “R:\webapp\phonedata.mdb” as above

In each case, I have verified that the file can be found, using filesystemobject.fileexists(sDataFile)

Other settings I have checked:

IUSR_Supervisor (as the IIS anonymous access account for this site) has full rights to the database folder.
I tried disabling “Allow IIS to control password” for the anonymous use for this site.
I checked the system environment variables for TEMP and TMP are set to WINNT/Temp, and that this user has full rights to this folder.

Can anybody please help?

Many thanks in advance

frontechltdAsked:
Who is Participating?
 
fritz_the_blankCommented:
If you are trying to create the query, I suppose that you could try using the CREATE VIEW command and then execute it. Otherwise, why not create the queries in Access and then call them by passing parameters?

FtB
0
 
neeraj523Commented:
hello

may i ask u what really u r looking to do with this mdb file.. if u want to access the data .. why don't u use ADODB ??

neeraj523

0
 
frontechltdAuthor Commented:
I am trying to create a query in the database…  ADODB is not compatible with the syntax of the query.

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
neeraj523Commented:
try this

<%
sDataFile = Server.MapPath("/") & "/phonedata.mdb”
 Set oAccessApp = CreateObject("Access.Application")
 oAccessApp.Visible = False
 oAccessApp.OpenCurrentDatabase sDataFile
%>

i m assuming your mdb file is located in the web root..

neeraj523
0
 
frontechltdAuthor Commented:
Thanks very much for your time and suggestion.  However, I am certain that the problem is not in locating the database, but in opening it.  I have established that the file can be found by the server, as I checked this using the filesystemobject.fileexists(sDataFile) test.

I am assuming that there isn’t a way to cheat ADODB into creating a new query, of course?
0
 
rdivilbissCommented:
Your error message would suggest that IUSR_machine name does not have write permissions to the database.
0
 
frontechltdAuthor Commented:
Thanks for the reply.  We have checked this, however, and believe it does have access.
0
 
rdivilbissCommented:
>Access 97

Can you try an Access 2000 mdb file?
0
 
frontechltdAuthor Commented:
Not without a considerable cost (about 20 licences) and heartache.  Do you think this will fix it, then?
0
 
rdivilbissCommented:
Can you just do it for a test?

BTW: MSDE is free and is MS SQL lite, so you could use that instead of Access if it is determined that Access 97 is the problem.
0
 
frontechltdAuthor Commented:
That’s an interesting suggestion, thanks.  We are definitely headed down the SQL Server path – this is an interim solution.

We actually have a full SQL Server implemented, but the migration is yet to be done on the full program suite.  The problem is that this is one of a suite of around 20 applications accessing this same data, so they all need to be converted simultaneously.

I may be able to construct a SQL Server test on our test platform, though.  Meantime, I just thought it would be a dead cinch to create a query in the Access database – it really doesn’t sound difficult, does it!!!!

Is there any other way of using Access (rather than ADODB) to run the query on the Access database?

Thanks
0
 
fritz_the_blankCommented:
First, you don't want to open Access as an application as Microsoft explicitly warns against doing so:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q257/7/57.ASP&NoWebContent=1

Now, are you trying to create the query or execute the query through ASP?

FtB
0
 
frontechltdAuthor Commented:
Thanks for the comments and suggestions.

 

I had read that MS article, yes.  As I only wanted to create a temporary query this way, not actually access any data, I didn’t think I could do much damage.  I am happy using ADODB to execute the query, once it’s there.  I may need to resort to parameterised query, but this is much more complicated than it sounds.  At the end of this text is a sample query, showing how complex this would be.

 

I really liked your second suggestion and have spent quite a while trying it.  Unfortunately this seems only to allow “SELECT” queries to be created – I get:

 

Microsoft JET Database Engine error '80004005'

Invalid SQL syntax - expected token: SELECT.

 

I tested this with a “SELECT” query and it worked, but as soon as I change it to an “INSERT INTO” query, it goes pear-shaped.  The MS ADODB documentation is pretty hard work, isn’t it?  Further to this idea, I may try ADODX, which will probably open a whole new can of worms!

 

Failing that, my final (embarrassing!) work-around will be to write the query to a text file, then call an external routine (asynchronously, I appreciate) to append the query.  I’ll just keep trying ‘til the query exists, then run it.

 

I hate this solution – but it is only an interim plan ‘til we go live with SQL Server, when I can start all over again ;-)

 

Cheers
0
 
fritz_the_blankCommented:
I can help with this if you like by giving you one or two code samples. Please let me know.

ftB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.