Can't use DB Results Wizard on Access Database

When attempting to use the Database Results Wizard in FP2000, I get the following message:

Server error: Unable to retrieve the list of record sources from a database using the connection string:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/listings.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------


Source: ADO
Number: -2146824584 (0x800a0e78)


According to MSDN, this error is described as "Operation is not allowed when the object is closed", whatever that means.

However, I'm able to open the sample Northwind Traders database just fine.

Been tearing my hair out with this for several days.  TIA for your help.

LVL 1
rjhowardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MarineCommented:
change your connectin string to this

"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("fpdb/listings.mdb") & ";"

this will work
0
rjhowardAuthor Commented:
How do I do that in the context of the Database Results Wizard?  At no point does it ask me what I want to use for a connection string.
0
MarineCommented:
I never used a wizard and i don't even know how to access one but this connection string works fine connecting to Ms Access. If you tell me how you access the wizard i might help you farther.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

rjhowardAuthor Commented:
Adjusted points to 150
0
rjhowardAuthor Commented:
In FP2000, you click on Add...Database...Results and follow the prompts.  My database is in the fpdb directory of the current web.  But beyond specifying where the database is located, I see no other that relate to the open string.

BTW, I even tried setting up a system DSN and accessing the database through it, but with the exact same result.
0
MarineCommented:
I don't know i didn't have Wizards installed in my FrontPage i can however give you the code which can do this for you. Do you have VisInterdev ? and PWS?
if you don't have visInterdev you can still do this in FrontPage.

Here is the code to connect to the database
<%
Set cnUser=Server.CreateObject("ADODB.Connection")
sCon = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("fpdb/listings.mdb") & ";"
'Note this DSN-Less connecton no DSN is required.
cn.Connectionstring = sCon
cn.Open scon
%>

or you can choose to have OLEDB Provider
sCon="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\my documents\mb.mdb;"
cn.connectionstring sCon
cn.open
--------END Example----------------

I have a question. Doesn't the Wizard generate some kind of a connection string or anycode that will be repsonsible for linking database? If so just use my connection string instead of the one you showed in a first comment. In your 1st comment you posted this
DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/listings.mdb

Try this instead if my examples won't work.
DRIVER={Microsoft Access Driver (*.mdb)};DBQ="fpdb/listings.mdb"
0
rjhowardAuthor Commented:
I don't want to get into the guts of databases or ASP; I just want to access my little simple-minded database.  If I have to jump through lots of hoops, it kind of defeats the purpose of using FP and Access.

I'm not at all sure the connection string is the problem.  Here are some others that also failed:

Server error: Unable to retrieve the list of record sources from a database using the connection string:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=/fpdb/listings.mdb
  (was able to dodge the URL thing by putting a slash in front of fpdb.  It wouldn't buy "./fpdb")

DSN=ListingsDemo;DRIVER={Microsoft Access Driver (*.mdb)}
  (where the DSN is indeed pointing to the correct file)

I'm starting to think this is an Access problem, not a FP problem, but I've examined the options for DBs and work vs. this one that doesn't and see no difference.
0
rjhowardAuthor Commented:
I found out where the connection strings are kept:  in a file called global.asa.  Interestingly, my site that works perfectly is using the "DBQ=URL=fpdb..." syntax in the open string.
0
rjhowardAuthor Commented:
Marine:

Took your advice, but the result turned out the same.  Are the quotes in the right place?  They don't look right...


Server error: Unable to retrieve the list of record sources from a database using the connection string:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("fpdb/listings.mdb") & ";" .

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------


Source: ADO
Number: -2146824584 (0x800a0e78)
0
MarineCommented:
I don't know try this my previouse comments worked for me. Try this one

"DRIVER={Microsoft Access Driver _
(*.mdb)};dbq=c:\inetpub\wwwroot\test\test.mdb;uid=Admin"

give it a full database path in this string. Give it a try
0
MarineCommented:
If you won't get it to work send me your global.asa file. I will see if i can get it to work. email me at Crutoy@aol.com
0
rjhowardAuthor Commented:
Adjusted points to 200
0
rjhowardAuthor Commented:
Using the absolute pathname didn't work either.  In looking at the global.asa file, I see no difference in the way listings.mdb is treated vs. fpnwind.mdb (the Northwind Traders sample database, which works just fine).

Emailing you the global.asa just in case you see something I don't.

Thanks for all your efforts.
0
DassaCommented:
I assume that you have setup your ODBC on the server correctly.  You will need a system data source defined so the web server can connect to the database.  This is setup in Control Panel --->ODBC Data Source Setup (32 bit).  You will need to define the system DSN.
0
rjhowardAuthor Commented:
I believe I have resolved this problem on my own.  Believe it or not, it seems to have been a permissions problem on the Access database.  I opened the permissions up as wide as I could for all users and groups, and the problem went away.

Obviously this is not a very good practice.  Does anyone know what the "right" permissions are?
0
MarineCommented:
If you only have Admin then evreything should be available to you. If you have more then one group then you can place a limit what they can do and tables,queries,etc they can access. I never did this though access i did this programatically. We checked to see the user login and then based on it we gave him access to the data. If he was lower clearance he had limited access maybe read-only , can't chane anything. If you are the only user then leave them the way you have them. Everything should be available to you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rjhowardAuthor Commented:
I'm going to ahead and accept this as an answer, even though I ended up resolving the problem myself.  But Marine put a LOT of time & thought into this, and he forced me to think about possibilities that eventually led me to the answer.
0
rjhowardAuthor Commented:
I'm sorry, but this was supposed to be a 200 point question.  I didn't change that.  But once I accepted the answer it became 20!  What gives??
0
MarineCommented:
rjhoward, thanks for the points i got 400 qaulity points. I think after you give points they get decreased like by 10% or something i don't know how the ee system works. Sorry i couldn't help you more on your problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.