Works with runsql but not with .Execute

[Robert]

I have the following code:

Dim db As DAO.Database
strSQL = "INSERT INTO tblRentaldetails ( Rentalid, Itemid ) "
strSQL = strSQL & "SELECT '177' As Expr1, modelid AS Expr2 FROM
tblReservations WHERE "
strSQL = strSQL & "tblReservations.reservationsmainid = 64"
Set db = CurrentDb
db.Execute SQLstr, dbFailonError

which generates the error:

The Microsoft Jet database engine cannot find the input table or query.
Make sure it exists and that its name is spelled correctly.

But if I chanage it to docmd.runsql it works fine. I have similar code
working elsewhere in the database and Microsoft 2.5/3.5 compatability
Library is checked on in tools/references. So why do I get this message
when i use .Execute?

Robert

 

[Allen Browne]

Is Rentalid a Number field (not a Text field) in table design?
If so, omit the quotes around the 177.

Does tblReservations have a field named modelid?
Is it the same data type as tblRentaldetails.Itemid?

Does tblReservations have a field named reservationsmainid?
Is it a Number field (not a Text field)?

If that doesn't solve the problem, perhaps Name AutoCorrect is confusing
Access about the names of the tables/fields. Try this sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

 

[Robert]

Yes, to all questions.

I'll give it a try. Thanks.

 

[Gary Walter]

Hi Robert,

Was it just a typo to newsgroup that

you defined "strSQL"
but tried to execute "SQLstr"

good luck,

gary

 

[Robert]

No. Unfortunately it wasn't a typo in the newsgroup. Do you know of any
option that requires defining all variables. AutoCorrect?

 

[John Spencer]

Option Explicit
at the top of each module forces all variables to be declared in the module.

You can set that to automatically happen for every module when it is
created.

Tool: Options: Editor Tab
Require Variable Declaration

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Robert]

There is no Editor tab on my Access 2002. There is an Edit/Find tab. ?

 

[Allen Browne]

You're looking in the main Access window.

The option John is referring to is on the Tools menu of the code window.

And you *always* want Option Explicit turned on.

 

[Robert]

Thanks!

You're looking in the main Access window.

The option John is referring to is on the Tools menu of the code window.

And you *always* want Option Explicit turned on.