Object variable or block variable ( error 91) not set

DevAccess

Registered User.
Local time
Today, 15:27
Joined
Jun 27, 2016
Messages
321
If RecordFound = False Then
DB.Execute "INSERT INTO tbl_DeletedBasic ([UNIQUE ID],[FIRST NAME],[LAST NAME],[STREET 1],[STREET 2],CITY,STATE,[ZIP CODE],COUNTRY,REGION,[MED CLASS],[MED CLASS],[MED EXP DATE])SELECT * FROM tbl_NewBasic where [UNIQUE ID] =" + field_value
End If

I am getting error number 91 on DB.execute statement,.

what is wrong here.
 
quit using db.execute,
use: docmd.runsql sSql

stop using sql, use queries.
 
in addition to Ranman's advice, it appears you have [MED CLASS] twice and using + instead of & may have unintended consequences.
 
I think you need to declare and set DB.
Something like below:
Code:
Dim DB as DAO.Database

Set DB = CurrentDb
 
In addition to the advice by moke123 & JHB, you need a space before the word SELECT.

No reason to stop using SQL to achieve your outcome
I also suggest that CurrentDB.Execute is preferable to ranman's suggestion of DoCmd.RunSQL
 
I disagree with Ranman.
1) Yes, use the DAO.Database.Execute method over DoCmd.
2) Yes, construct your SQL in VBA if you want to.
Also, if you are posting about a problem in code, post all the code.
Mark
 
To add to the others,

Declare a string variable.
Fill the string variable with your SQL.
SHOW your SQL (msgbox, debug.print, fill a text box, what ever)
Make sure your SQL is correct BEFORE you try to execute it.

Often seeing exactly what you are going to do helps avoid many issues.
 
I'll step in to muddy the waters. There are two common ways to execute an SQL-syntax string as a query in Access VBA.

First, you could use DoCmd.RunSQL to execute the string. This executes FIRST in the context of Access and can therefore perform concatenation on-the-fly. It can call various functions including locally defined functions. It can also see any open forms (while still in the Access phase) and so could in theory contain a WHERE-clause component based on a control from an open form. When the Access phase is complete, the resultant string is passed to JET/ACE and gets executed.

Second, you could use <DAO database>.Execute to execute the string. This putative SQL string MUST be completely built before you pass it in to the .Execute method because that is going straight to the DB engine. There are a couple of advantages here vs. the DoCmd option.

(a) .Execute is faster to execute. (Less overhead.)

(b) if you include the option parameter dbFailOnError as the second parameter of the .Execute (the first being the string itself) and then if there IS an error, it gets signaled AND the engine does an automatic full rollback, so the error doesn't change your database.

(c) If the SQL is in the form of an action query (which for this case, it should be), you have the ability to check the <DAO database>.RecordsAffected value to see how many records (if any) were altered by the action.

I generally PREFER the <DAO database>.Execute method but if you are doing a quick-n-dirty based on form controls or if you need to use home-grown functions in the query, then the .RunSQL option might be better.
 
If performance matters then use parameterised queries. Doesn't matter much for a single execution but is important for repeated executions. For example, when writing values from a recordset to a table in a loop.

The Execute method of a parameterised QueryDef uses a saved query plan and keeps Access right out of the action. This technique should be preferred wherever practical.

Many developers prefer to use VBA to avoid cluttering the list of objects but it does cost in performance.

RunSQL throws action warnings. Turning warnings off means you get no warnings at all. During development I sometimes use it because it warns the records proposed to be affected and I can decide if this is what I expect.

However I generally wouldn't use RunSQL for a production database.

Execute behaves better but the values must be concatenated into the command.
 
you need a space before the word SELECT.
You don't actually. The SQL parser doesn't hold a space as any more special as a delineator than many other characters. The close parenthesis perfectly delineates the SELECT key word from the previous expression.

I often concatenate field and values without spaces before and after the equals operator. I don't find that the spaces always enhance the readability so I leave out the extra keystrokes.
 
You don't actually. The SQL parser doesn't hold a space as any more special as a delineator than many other characters. The close parenthesis perfectly delineates the SELECT key word from the previous expression.

I often concatenate field and values without spaces before and after the equals operator. I don't find that the spaces always enhance the readability so I leave out the extra keystrokes.

Having just tested it, I can see you are correct that the space isn't required.

However IMO, the space definitely make it easier to read.
More importantly, when writing SQL queries, getting into the habit of always adding a space at the start of each continuation line (or end if preferred) prevents unnecessary errors.
 
However IMO, the space definitely make it easier to read.

I don't leave out spaces around keywords but places like this:

Code:
WHERE fielda=3 AND fieldb=4 AND fieldc=5

rather than

Code:
WHERE fielda = 3 AND fieldb = 4 AND fieldc = 5

See where I'm coming from? The expressions hang together better. It is just a personal preference. Might not be everyone's cup of tea.

More importantly, when writing SQL queries, getting into the habit of always adding a space at the start of each continuation line (or end if preferred) prevents unnecessary errors.

I like them at the beginning and lining them up so that they are easy to confirm

Code:
strSQL = "SELECT blah" _
       & " FROM whatever" _
       & " WHERE ...."
 
I don't leave out spaces around keywords but places like this:

Code:
WHERE fielda=3 AND fieldb=4 AND fieldc=5

rather than

Code:
WHERE fielda = 3 AND fieldb = 4 AND fieldc = 5

See where I'm coming from? The expressions hang together better. It is just a personal preference. Might not be everyone's cup of tea.

I like them at the beginning and lining them up so that they are easy to confirm

Code:
strSQL = "SELECT blah" _
       & " FROM whatever" _
       & " WHERE ...."

Yup - I agree with all the above
 
Just be thankful you don't program in LISP. The nickname for THAT beast was "Lots of Insipid and Silly Parentheses."
 

Users who are viewing this thread

Back
Top Bottom