Can't open recordset! Help!

[Aaron Croasmun]

I'm attempting to open a recordset. I've tried using DAO and ADO...the database opens just fine, but when I try
set rs=db.openrecordset("<tablename>",opendynaset,openreadonly)
I get an error saying "Type Mismatch"
if i try rs.open "<tablename>",db,<options>
I get "Open method of _recordset object failed"

what the heck am I doing wrong? I've done it a hundred times in a COM Object, but when i try doing this with a standard EXE, I get all kinds of errors. Any help here would be greatly appreciated. Thanks!
-Aaron

[Judgey]

In your References, have you got the Microsoft DAO Object Library 3.5 selected ?

[sotoasty]

Not sure if this is a "typing" problem or not, but opendynaset and openreadonly won't work. The constants are

dbOpenDynaset
dbOpenReadonly

Also I am assuming that above you db.OpenRecordset statement you are doing a corresponding...

Set db=OpenDatabase("dbname")

A little more code would help. Hope this is enough to get you going.

[Aaron Croasmun]

Actually, I have 3.51...I've also tried 3.6. I don't have 3.5 as an option.

[Judgey]

Try this Aaron;

Dim ADBase As Connection
Dim ARecs As New ADODB.Recordset

ProductDBConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source= < Database Name >.mdb;Jet OLEDB;"
Set ADBase = New Connection
ADBase.CursorLocation = adUseClient
ADBase.Open ProductDBConnectionString
ARecs.Open "SELECT Users.* FROM Users;", ADBase, adOpenStatic

[Aaron Croasmun]

ok, that worked, but unfortunately, I also need to gather a list of the tables within the database and the connection object doesn't seem to support that..Is there an easier way to do this or should I just create a database and connection object and use the db object for the table list and connection object for recordsets? Thanks for all your help!
-Aaron

[Iouri]

Sub ListTables()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

'References : Microsoft ADO Ext 2.1 or DDL and Security

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\nwind.mdb;"

' Loop through the tables in the database and print their name
For Each tbl In cat.Tables
If tbl.Type <> "VIEW" Then Debug.Print tbl.Name
'not to see system tables If Left$(tbl.Name, 4) <> "MSys" Then Debug.Print tbl.Name

Next

End Sub

Iouri Boutchkine
[email protected]

[Judgey]

Hi Aaron,

If you send me you Email address, I've got a form here, which allows you to select any Access 97 Database via a Common Dialog, It thend fills a TreeView control with all Tables, Queries etc in the Database, you should be able to rip out the code you need.