Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

OpenSchema error

Posted on 2005-04-26
9
Medium Priority
?
360 Views
Last Modified: 2008-02-01
Hello all
What i am trying to do is use the following piece of code to access any database (i.e. oracle, access, mysql, sql server) and run through each table in the database and retrieve each table and its primary key. The problem is when it comes to using it with access, if i use the following connection string "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=......" the scipt runs fine, but if i use an ODBC connection for the same database which has been added to the system DSN, using the following string "DSN=Test" i get the following error "Object or provider is not capable of performing requested".

Set dbConn = Server.CreateObject("ADODB.Connection")  
dbConn.Open strConnection

Set rsA = dbConn.OpenSchema(20, Array(Empty, Empty, Empty, "TABLE"))        
Do While Not rsA.EOF  
    Set rsB = dbConn.OpenSchema(28, Array(Empty, Empty, rsA("Table_Name").value))   '<--- ERROR at this line
 
    rsA.MoveNext
 Loop
 rsA.Close
Set rsA = Nothing

So if someone could point me in the direction of were i am going wroung that would be great.
thanks
ant
0
Comment
Question by:vdhant
  • 5
  • 3
9 Comments
 
LVL 13

Expert Comment

by:jrram
ID: 13873235
Just guessing here, but could the ".value" have anything to do with it.  Should it be:

Set rsB = dbConn.OpenSchema(28, Array(Empty, Empty, rsA("Table_Name")))

And looking at the all examples provided by Microsoft (http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dntaloc/html/office10052000.asp), all they use is the Microsoft.Jet.OLEDB.4.0 connection.
0
 
LVL 2

Author Comment

by:vdhant
ID: 13873358
Thanks for the guss, but i tried that alread and it made no difference.
ant
0
 
LVL 15

Accepted Solution

by:
deighc earned 200 total points
ID: 13874180
The error message is the answer: the Access ODBC driver simply doesn't give you that functionality.

You have to use the native Access Jet driver.

Just because you have certain functionality in ADO it doesn't automatically mean it's available to you. You're ALWAYS limited by the functionality exposed via the database driver.

Native drivers typically offer more features then generic drivers like ODBC,
0
Technology Partners: 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!

 
LVL 2

Author Comment

by:vdhant
ID: 13875117
Thanks again deighc. So to achive this i have to use an OLE DB connection string??
ant
0
 
LVL 15

Expert Comment

by:deighc
ID: 13875133
> So to achive this i have to use an OLE DB connection string??

Yep.
0
 
LVL 2

Author Comment

by:vdhant
ID: 13875146
cool thanks
ant
0
 
LVL 2

Author Comment

by:vdhant
ID: 13875165
Just one last quick thing, is there any other situation(i.e. oracle, mysql, sql server) were there is this same limitation.
ant
0
 
LVL 15

Expert Comment

by:deighc
ID: 13875229
> is there any other situation were there is this same limitation

Absolutely. As I said your functionality is ALWAYS limited by the database driver. ADO presents a common interface for working with databases. But sitting between it and the database itself is the data provider (or driver). ADO gives you many ways to work with data, schema's etc but of course it can only work within the limitations of the mechanism that connects the DB itself.

I've never worked with Oracle so I can't help you there. But the OLE DB driver for SQL Server gives much better performance and functionality than the ODBC driver.

For MySQL there is (as far as I know) only an ODBC driver available (MyODBC). This works pretty well but I know that it doesn't support all ADO functionality (paged recordsets for example). Not sure about table schemas though.
0
 
LVL 2

Author Comment

by:vdhant
ID: 13875246
Thanks alot
ant
0

Featured Post

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!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
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

580 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