Upsized database - getting "Object Variable or with block not set" error!

Question

Upsized database - getting "Object Variable or with block not set" error!

wayne.taylor

SSC Rookie

Points: 36
More actions
October 13, 2008 at 6:44 am

#73056

Hi All
I've got an Access 2003 database that I've upsized to SQL 2005 using an ADP application.
I've got a block on code as below:
Dim rstt As DAO.Recordset
Dim txtsql As String
txtsql = "select * from [Hardinge Products] where productid = " & Trim(machine)
Set rstt = CurrentDb.OpenRecordset(txtsql)
And I get an "Object Variable or with block not set" error at the
Set rstt = CurrentDb.OpenRecordset(txtsql)
line. This works fine in Access, what am I missing???
Thanks
Wayne

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Just a guess because I am not an Access expert by any means, but I think because you have upsized CurrentDB will no longer work. You will need to create a connection, command, and then a recordset and probably do it using ADO instead of DAO.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

klymster Say Hey Kid Points: 704 More actions · Answer 2

Hi Wayne,

You are in the same situation as I am. I have upsized my Access DB to SQL Server Express and now my existing DAO code has to be re-written to ADO. Try this conversion:

Dim DB As New ADODB.Recordset

Set rstt = New ADODB.Recordset

Dim txtsql As String

txtsql = "select * from [Hardinge Products] where productid = " & Trim(machine)

rstt .Open txtsql , CurrentProject.Connection, adOpenKeyset, adLockOptimistic

KB

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

wayne.taylor (10/13/2008)
Hi All
I've got an Access 2003 database that I've upsized to SQL 2005 using an ADP application.
I've got a block on code as below:
Dim rstt As DAO.Recordset
Dim txtsql As String
txtsql = "select * from [Hardinge Products] where productid = " & Trim(machine)
Set rstt = CurrentDb.OpenRecordset(txtsql)
And I get an "Object Variable or with block not set" error at the
Set rstt = CurrentDb.OpenRecordset(txtsql)
line. This works fine in Access, what am I missing???
Thanks
Wayne

Wayne,

I've never worked with ADP's so I may be off-track here. But normally in Access I would use something like this

Dim db As DAO.Database

Dim rstt As DAO.RecordSet

Dim txtsql As String

Set db = CurrentDb

txtsql = "select * from [Hardinge Products] where productid = " & Trim(machine)

Set rstt = db.OpenRecordset(txtsql)

notice that you declare and set the db variable before opening the recordset. But I also have to wonder about the sql string - if (machine) is a number, the Trim is out of place; if (machine) is a string then you need quotes to delimit, like this

txtsql = "select * from [Hardinge Products] where productid = '" & Trim(machine) & "' "

Also, if your table has an identity column, you might need to use

Set rstt = db.OpenRecordset(txtsql, dbOpenDynaset, dbSeeChanges)

wayne.taylor SSC Rookie Points: 36 More actions · Answer 4

Thanks for the replies chaps...

But, still no joy.

I thought that the switching to ADO might work, but it still seems to not work giving me exactly the same error. I have checked the SQL query and that works fine in the SQL management tool, so that is fine.

I'm wondering about the curent database issue, i.e. if after an upsize, do the SQL tables appear as part of the current database so would the CurrentProject.Connection part of the code below work for upsized tables!!

rstt.Open txtsql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

I might try build a separate ADO connection to the SQL server as a separate connection outside of the Access database links..

Unless there are any other ideas!

Cheers

Wayne

Dylan Danich SSC Rookie Points: 43 More actions · Answer 5

Yes, you need to use ADO. If I were you, I'd create the connection and command object. Something like this:

Dim cnn As ADODB.Connection

Dim cmdSQL As ADODB.Command

Dim rstt As ADODB.Recordset

Dim strConnString As String

Dim strSQL As String

Set cnn = New ADODB.Connection

' you can use "User ID=YourUserName;Password=YourPassword" instead of the Integrated Security below if not using Windows login

strConnString = "Provider=SQLOLEDB.1; Data Source=Your Server Name;" & _

"Database=Your Database Name;Initial Catalog=Your Database Name;Integrated Security=SSPI; Trusted Connection=Yes"

cnn.Open strConnString

strSQL = "select * from [Hardinge Products] where productid = " & Trim(machine)

Set cmdSQL = New ADODB.Command

Set cmdSQL.ActiveConnection = cnn

cmdSQL.CommandText = strSQL

cmdSQL.CommandType = adCmdText

Set rstt = cmdSQL.Execute()

There are plenty of examples in BOL. Personally, I usually create a global variable and set the connection object when starting up the application (I usually have a login screen) using a global function. Therefore, I have the connection object (cnn above) available to me throughout my user interface. I also use stored procedures instead of embedded SQL.