Why can't I assign my db to a variable?

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

I'm reposting this from another message thread. I think it was probably too
old for anyone to notice the new question. And it's basically a new issue
anyway.

When I do this, I get an error at the .execute statement, "Object variable
or With block variable not set."
I've had problems in the past trying to Dim the database. I have referenced
DA0 3.6. I have not referenced any ADO libraries. (Although I've played
around with that, referencing both.. in different orders. But not for this
problem here). Finally I just gave up and used "CurrentDB." But apparently
I can't get away with that and still use RecordsAffected


Here is my code, cut and pasted (ls_SQL is legit... defined elsewhere)

Dim dbCurr As DAO.Database
With dbCurr
.Execute "Select * from tlbScores"
li_safety = .RecordsAffected
End With
 
Hi,
After you Dim a database variable, you have to set it:
Dim dbCurr As DAO.Database
Set dbCurr = CurrentDb()


**I'm pretty sure this code will not work**
With dbCurr
.Execute "Select * from tlbScores"
li_safety = .RecordsAffected
End With

Not sure what your goal is here, but .Execute is for 'action queries'.
Update .....
Insert Into ......

If you're simply trying to get a record count, I suggest you use the DCount
function.

HTH
Dan Artuso, MVP
 
Hi

Even with

Set dbCurr = CurrentDb()

the code will not compile. Access throws a runtime error "cannot execute a
select query".

Immanuel Sibero
 
Immanuel Sibero said:
Hi

Even with

Set dbCurr = CurrentDb()

the code will not compile. Access throws a runtime error "cannot execute a
select query".
Click to expand...

Well, that is a different issue. The EXECUTE method is for action queries
that either append or update data. It cannot be used on a SELECT query or
SQL Statement.

What is it you want to do? Are you wanting to open a query on the screen
or do you want to manipulate the data in a query in your code routine? For
the former use DoCmd.OpenQuery. For the latter you need to open a
Recordset based on the query.
 
What I want to do is successfully set my database to a variable. There are
a variety of reasons to do this, one, apparently, in the selection below.
I've never been able to do it, whatever the reason I need to. The variable
ends up null (I think that's usually the situation). So I've settled for
using CurrentDB. There are things you can't do with that. One example is
use .RecordsAffected.

I am not having problems with the Execute method. If there are problems in
my sample code realted to that, it was inadvertant. I have a working
CurrentDb.Execute statement.
 
You had several replies that suggested you add the line:
Set dbCurr = CurrentDb()
If you haven't done this then you are wasting our time. If you have done
this and it continues to not work then post your full code that doesn't work
and tell us which line is generating the error and what the error message
is.
 
Back
Top