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 IntegerDim DateInv As DateDim SqlStr As StringCusInf = 3DateInv = 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
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
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.
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