CurrentDb Is Nothing

[scking]

I've two databases, one created with Access 2000 with Jet 4.0 Engine and one Upsized to Access 2000 ADP file with a SQL Server engine. I'm trying to use DAO since the original creates many recordsets using DAO but code that works on one will give me a 91 error, 'Object Variable or With Blcok Variable Not Set'. It stops on code the uses the CurrentDb intrinsic object to instantiate a recordset. Using the debug/immediate window to determine the cause led me to discover that CurrentDb in nothing in the SQL Server .adp application that causes the problem. Output from the debugger:

?CurrentDb Is Nothing
True

While searching in the help files I discovered that Access 2000 sets a reference to the DAO 3.6 Object Library automatically but it needed to be set manually for the ADP, which I did. I still get the error and was unsuccessful in my search of the Microsoft Knowledgebase.

Anybody experienced and resolved this? What is my answer? I hope you don't say to rewrite the application using ADO because that would be a significant effort.

I appreciate your assistance.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[cmmrfrds]

An Access project (adp file) uses an ADO connection to SQL Server and the recordset default is to ADO. I am not sure what you are trying to use DAO for, but it will be easier to change to ADO. Are you creating recordsets from the database you upsized or an Access MDB?

The default connection to SQL Server is:
CurrentProject.Connection
This replaces the CurrentDB.

Here is an example. I use the Northwind database which gets installed with SQL Server to test with.


Function Atest()
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
Dim rs2 As New ADODB.Recordset
''connString = "provider=SQLOLEDB.1;" & _
'' "User ID=sa;Initial Catalog=northwind;" & _
'' "Data Source=bigtuna;" & _
'' "Persist Security Info=False"

Set cn = CurrentProject.Connection
''cn.ConnectionString = connString
''cn.Open

If rs Is Nothing Then
Debug.Print "nothing in rs"; rs.State
Else
Debug.Print "somthing in rs"; rs.State
End If

''sql1 = "select * from dbo.Customers;select * from Categories"
sql1 = "SELECT dbrders.OrderID, sum(dbrders.Freight * 2) as price " & _
"FROM dbrders " & _
"GROUP BY dbrders.OrderID " & _
"Order by sum(dbrders.Freight * 2);"
Debug.Print sql1
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Debug.Print rs(0)
If rs Is Nothing Then
Debug.Print "nothing = "; rs.State
Else
Debug.Print "somthing = "; rs.State
End If

Debug.Print "rec count = "; rs.RecordCount
''Set rs2 = rs.NextRecordset
''Debug.Print rs2!Description
''Debug.Print "rec count = "; rs2.RecordCount

''rs.Close
Set rs = Nothing

End Function

 

[scking]

Yeah, I suspected as much. All I wanted is to be able to find it somewhere in documentation, help files, or whatever. I performed a thorough search of my relatively extensive library and failed to find this.

Your surmise is correct. I use DAO.Recordsets flagrantly in a large legacy application that is being upsized to use SQL Server. There are 152 recordsets created but that's not the big issue. The properties and methods differences forces the subs and functions using those recordsets to change also. You can see why I didn't just immediately switch to ADO.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------