[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

IIS / ASP opening MDB using Access Application object

Posted on 2005-04-27
14
Medium Priority
?
971 Views
Last Modified: 2008-03-03
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

0
Comment
Question by:frontechltd
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 22

Expert Comment

by:neeraj523
ID: 13874197
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
 

Author Comment

by:frontechltd
ID: 13874274
I am trying to create a query in the database…  ADODB is not compatible with the syntax of the query.

0
 
LVL 22

Expert Comment

by:neeraj523
ID: 13874571
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:frontechltd
ID: 13875840
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
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13876063
Your error message would suggest that IUSR_machine name does not have write permissions to the database.
0
 

Author Comment

by:frontechltd
ID: 13876776
Thanks for the reply.  We have checked this, however, and believe it does have access.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13878051
>Access 97

Can you try an Access 2000 mdb file?
0
 

Author Comment

by:frontechltd
ID: 13883476
Not without a considerable cost (about 20 licences) and heartache.  Do you think this will fix it, then?
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13885141
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
 

Author Comment

by:frontechltd
ID: 13885433
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 13885567
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
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 2000 total points
ID: 13885606
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
 

Author Comment

by:frontechltd
ID: 13893283
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 13898928
I can help with this if you like by giving you one or two code samples. Please let me know.

ftB
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question