vdhant
asked on
OpenSchema error
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.OL
Set dbConn = Server.CreateObject("ADODB
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
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.OL
Set dbConn = Server.CreateObject("ADODB
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
ASKER
Thanks for the guss, but i tried that alread and it made no difference.
ant
ant
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again deighc. So to achive this i have to use an OLE DB connection string??
ant
ant
> So to achive this i have to use an OLE DB connection string??
Yep.
Yep.
ASKER
cool thanks
ant
ant
ASKER
Just one last quick thing, is there any other situation(i.e. oracle, mysql, sql server) were there is this same limitation.
ant
ant
> 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.
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.
ASKER
Thanks alot
ant
ant
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.