Avatar of bill201
bill201
 asked on

i try to run a sql statement in vba in access 2010 and i get an error "A RunSQL action requires an argument consisting of an SQL"

i tried to run some sql code and it's don't worked from vba but when i put the same string in regular query its worked why it this and if there is some solution to run this sql statement from vba


and this is my  error message:
Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL
Dim CusInf As Integer
Dim DateInv As Date
Dim SqlStr As String
CusInf = 3
DateInv = DateSerial(2010, 8, 25)
SqlStr = "SELECT  CustomerID,InvoiceID, InvoiceDate, Product, Qty, Price, Total, Maam, TotalIncludeTaxb "
SqlStr = SqlStr & "FROM [;DATABASE=C:\Users\books\sample.mdb ;PWD=121].[invoices1]"
SqlStr = SqlStr & " WHERE CustomerID=" & CusInf & " AND InvoiceDate<#" & DateInv & "#"

DoCmd.RunSQL SqlStr

Open in new window

DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment
bill201

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
TinTombStone

The RunSQL command is used to run Action queries (If I remember correctly) not Select Queries

Perhaps an update query, like:

strSQL = "UPDATE Employees " 
strSQL = strSQL &  "SET Employees.Title = 'Regional Sales Manager' " 
strSQL = strSQL &  "WHERE Employees.Title = 'Sales Manager'"

DoCmd.RunSQL strSQL

You can use DoCmd.OpenQuery to run a saved Select Query
I you are running the code from a form then the query criteria cound examine the form directly


Manfred35

Docmd.runsql is only for action sql, to update or add data. Here, your code invoque a select query that do nothing in this context.
You must precise your goal for me to help you more pecisely.
your query will work if you assign it to a Recordset variable.
Add this in your code to test :
Dim MyRs as recordset
SET MyRs = Currentdb.openrecordset(SqlStr)
debug.print MyRs!InvoiceId
...
bill201

ASKER
i prefer not to use recordset because meny times after i use the method currentdb it's corrupted the database and i haved to make a new database and import the current database

look there where i wrote about the problem
https://www.experts-exchange.com/questions/27188650/error-message-in-access-2010-You-do-not-have-exclusive-access-to-the-database-after-using-a-current-database-recordset.html
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Manfred35

recordsets are one of the most used method for using data in a lot of cases. if your Database get corrupted, you must verify the stability of your environment, especially if you are under a old PC or your network is not as good as access need. Yes network problems are one of the most usual reason of database corruption. Updating data, you must control that the operation is not too long not to have to deal with network disconnection and so database corruption. For example a wifi network was not recommended for network sharing access databases althow now it can work more easily than before. Be careful of your environment with access and save and compact you database each end of the day especially if it is big and very used. After that, as a heavy access user with simultanéous users (less than 10  users updating data but mutch more viewing), i very rarely use the repair tools since a lot of years. No matter the way you update data. You can show the code you use  with recordset, perhaps it leaves you database in an inconsitent state and générates conflicts and so on...
bill201

ASKER
i don't have any network and the computer is not old at all so i can't see any reason why it's make me problems
Manfred35

A usual problem is to try to update data from a form and  by vba (recordset) at the same time, in the current record in a form with a linked table to it so it generates conflict that sometime (rarely) corrupt database. Is it your case? i sometimes had corrupted installation of access that led to unusual error too.
If you intend to develop in access you must deal with recordsets it's a very good way to associate the best of SQL with the best of VBA.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bill201

ASKER
thanks manfred for your comment, it's a update quey but maybe is something corrupted in the installation i will check it

any way you are right i will most to become used to recordsets
bill201

ASKER
ohho i maked a mistake in the previous comment it's not a update query