Open recordset from current database

[RobJDB]

I'm new to DAO, and I've been working on recordsets to get myself started. What I want to do is open a recordset from a table within the current db, wherever it may be copied or moved to. This means that instead of using something like:

oConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Inetpub\

http://wwwroot\db_Utilities\testdb.mdb"

I'm looking for some way of defining the recordset's connection without using an absolute path or a dsn.

Anyone know how to do this?

Thanks,

Rob

 

[gol4]

perhaps I am miss understanding you but, To open a DAO recordset in the current db

dim rst as recordset
set rst= currentdb.openrecordset("tablename&quot
'code to navigate
rst.close


Set recordset = object.OpenRecordset (source, type, options, lockedits)

 

[RobJDB]

I think you do understand exactly what I want. Trouble is, when I put your code in, I got a type mismatch error on the currentdb.openrecordset part. This is what I currently have:

Dim rs As ADODB.Recordset
Dim constr As String
constr = "DRIVER={Microsoft Access Driver (*.mdb)}; _DBQ=C:\Inetpub\

http://wwwroot\db_Utilities\testdb.mdb"

Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseClient
oConn.Open constr

Dim sSQL As String
sSQL = "select * from " & myTB ' myTB is the table, defined earlier from user input
Set rs = New ADODB.Recordset
rs.Open sSQL, oConn, adOpenStatic, adLockOptimistic

It works fine but I bet most of it is unnecessary. Everywhere I look I see a different way of opening a recordset, so I'm quite confused at the moment. I just want to specify an SQL statement and use it to open a recordset from the current db.

Thanks,

Rob

 

[gol4]

Rob,
you are using ADO not DAO

I don't use Access 2000 that much so I can't be 100% certain but I think You can use

Dim oConn As ADODB.Connection
set oConn = Currentproject.connection for an already database

good luck

 

[raymondo]

Like the man said, you're using ADO, not DAO and it's a good idea to do so as this is the way things are moving in the MS world.
If you are already in "testdb" when you want to open the table then
set oConn = CurrentProject.Connection
is the way to go, otherwise what you have written is fine.
If you're that set on reducing the code then the two lines
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
can be combined into one
Dim rs As New ADODB.Recordset
However, my advice is 'If it ain't broke, don't fix it!'

 

[thornmastr]

Before you whole heartedly swallow the swill of all the ADO enthusiasts, use

http://www.google.com,

groups> databases and do a search on DAO vs ADO or its converse, do some reading and make up your own mind.

When you get advice here, sometimes it’s good, sometimes it’s not so good. The best approach is to take the advice and experiment with it. When you find an approach which works for you and you understand what is behind the logic, make it part of your repertoire.

Good luck.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[RobJDB]

gol4, raymondo, thornmastr,

Thank you all for that. I've implemented the recommended changes and it worked straight away. I love it when that happens! I'll go and read up on ADO vs DAO today.

Cheers,
Rob