CurrentProject.Connection equivalent in Access97?

[lokem]

Hi all,

I've been developing with Access2000 and I'm asked to change to Access97. The entire database seems fine after conversion except for one portion of the code.

The code is as follows:

<code>
Dim rs As New ADODB.RecordSet
rs.Open &quot;SELECT * FROM tblSomething&quot;, CurrentProject.Connection, <some other params>
</code>

I've read that it can't be done that way so I changed it to:

<code>
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection
rs.Open &quot;SELECT * FROM tblSomething&quot;, cn, <some other params>
</code>

The problem still persists since Access97 can't seem to find the CurrentProject reference. Is there a way to go about this?

Any help is appreciated.


Thank you in advance.

 

[evalesthy]

ADO

Dim rst As New ADODB.Recordset
rst.Open &quot;Contacts&quot;,CurrentProject.Connection, _
adOpenKeySet,adLockOptimistic
Debug.Print rst!AddNew

DAO

Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;Contacts&quot
Debug.Print rst!AddNew


I think it is the CurrentDb setting that you are looking for - see above examples.

 

[lokem]

Thanks for the reply. Doesn't Access97 support ADO? Anyway, I've tried the code in Access2000 and it can't seem to find the reference to the Database object. Is this only applicable to Access97?

 

[RickSpr]

Access 97 only had DAO, not ADO. I'd be surprised if you can use ADO with it.

Access 2000 supports DAO, but ADO is used by default. To make DAO work, you have to add a reference to the DAO 3.6 Object Library. You can use both in the same project, I believe, but you have to be sure you Dim as either DAO.Recordset or ADO.Recordset, or you might get the wrong one. These objects have the same name, but aren't much alike. Rick Sprague

 

[vr]

I use ADO in '97:

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn
cmdcommand.CommandText = &quot; SELECT * FROM Table.TableName WHERE MyField = ?&quot;
cmdCommand.CommandType = adCmdText
cmdCommand.Parameters.Append cmdCommand.CreateParameter(&quot;MyField&quot;, adVarChar, adParamInput, 10, &quot;123456789&quot

Be sure to select the references and ensure they are registered.

 

[lokem]

Thanks for the info everyone. I've managed to fix the DAO problem in Access97 by creating a new DB and copying all the objects to it. Not the way I'd want to do it, but it works. I just can't seem to find the References... option in the menu bar. Can someone point me to the right direction?

TIA.

 

[mackers2]

To find the References option
Open any module in design mode
Click on Tools then References and add the reference you want.

 

[lokem]

Thanks mackers2. Should have dugged a bit more b4 asking that question