After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.
First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:
lsSQL = "INSERT INTO tblUsers CenterID, WorkerID..."
DoCmd.RunSQL (lsSQL)
Most other coders seem to use some recordset approach, e.g:
The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.
So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?
I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing. The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)") anyone know what I'm doing wrong? thanx
i have two tables table1,table2 each with one same field Country.
In my RunSQl query, i want to delete all records in table1 and table2 simultaneously from one button with a certain country. The problem is how can i do this from may be one RunSQL statement i tried to use two deletion runSQl commands, but only one is executed. Actually the one that comes second. If i put doevents in the middle of these statements, only the first is executed.
Here is my code:
Private Sub RemoveCtry_Click() docmd.setwarnings false Docmd.RunSQL "DELETE * FROM table1 WHERE (Country='" & USA & "')" Doevents Docmd.RunSQL "DELETE * FROM table2 WHERE (Country='" & USA & "')" docmd.setwarnings true End Sub
if I use One Statement:
Docmd.RunSQL "DELETE table1.Country,table2.Country FROM table1,table2 WHERE ((table1.Country='" & USA & "' ) AND (table2.Country=' " & USA & " '))" I get an error that i have to specify the table to delete from!!!!
im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,
basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book :)
intWeight = Me.txtWeight
Select Case intWeight Case 0 To 100 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 101 To 250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 251 To 500 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 501 To 750 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 751 To 1000 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 1001 To 1250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" End Select
Me.txtEstimate = strParcelType & intCost
many thanks to any gurus who can point me in the right direction or show me a easier way to do this
I have a VBA application i am writing. I am trying to use an sql statement with docmd.runsql to insert a value into my table that matches a particular record. (which will be the one open). For testing purposed i have stripped down my code.
When running the code it works, however it puts chinese symbols in all the other fields of the record and dose not insert the record into the CapExFileName Field. Then when you try and delete the record it comes up with no search index found.
Any idea on why it would be doing this ?
here is the code.
.......
Dim SQLstring As String
SQLstring = "UPDATE Assets SET Assets.CapExFileName = 'Test' WHERE Assets.Barcode = 'Testies1234'"
