SQL vs. VBA

I am learning (slowly) how and where to use VBA and SQL.  I've noticed that 
some jobs can be done using either.  For example, I can have a button with 
'ON CLICK' code including the SQL fragment:

   UPDATE tblData SET  Description = "Not Available"     ,     

but I can do the same thing with a Sub containing a code fragment like:

   With rstData
      Do While Not .EOF
         !Description = "Not Available"
         .Update
         .MoveNext
      Loop
   End With

It seems like there are probably many jobs that can be done with VBA alone 
or by involving SQL.  Are there guidelines out there as to when it might be 
best to adopt one approach over the other?

thanks in advance
Sarah

0
Utf
1/26/2010 1:20:04 AM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1150 Views

Similar Articles

[PageSpeed] 33

The personal guideline is use SQL when you are doing multiple records.  It is 
more efficient and tends to keep the database from bloating.

Use VBA when I cannot do it with SQL or when I am working with one or very few 
records and it is difficult to do it with an SQL statement.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Sarah wrote:
> I am learning (slowly) how and where to use VBA and SQL.  I've noticed that 
> some jobs can be done using either.  For example, I can have a button with 
> 'ON CLICK' code including the SQL fragment:
> 
>    UPDATE tblData SET  Description = "Not Available"     ,     
> 
> but I can do the same thing with a Sub containing a code fragment like:
> 
>    With rstData
>       Do While Not .EOF
>          !Description = "Not Available"
>          .Update
>          .MoveNext
>       Loop
>    End With
> 
> It seems like there are probably many jobs that can be done with VBA alone 
> or by involving SQL.  Are there guidelines out there as to when it might be 
> best to adopt one approach over the other?
> 
> thanks in advance
> Sarah
> 
0
John
1/26/2010 2:13:09 AM
On Mon, 25 Jan 2010 17:20:04 -0800, Sarah
<Sarah@discussions.microsoft.com> wrote:

I agree with John.
Also consider that in your example the UPDATE statement is a very
efficient statement which database engines can execute very quickly,
even with many records. Compare that with your VBA code which runs
linearly slower with more records.

-Tom.
Microsoft Access MVP


>I am learning (slowly) how and where to use VBA and SQL.  I've noticed that 
>some jobs can be done using either.  For example, I can have a button with 
>'ON CLICK' code including the SQL fragment:
>
>   UPDATE tblData SET  Description = "Not Available"     ,     
>
>but I can do the same thing with a Sub containing a code fragment like:
>
>   With rstData
>      Do While Not .EOF
>         !Description = "Not Available"
>         .Update
>         .MoveNext
>      Loop
>   End With
>
>It seems like there are probably many jobs that can be done with VBA alone 
>or by involving SQL.  Are there guidelines out there as to when it might be 
>best to adopt one approach over the other?
>
>thanks in advance
>Sarah
0
Tom
1/26/2010 3:57:41 AM
Sarah, that's a good question, so I'll chip in too as another voice 
supporting John and Tom.

In general, anything you can do straightforwardly with a DML query/SQL 
statement will be more efficient than looping records in VBA. Often the VBA 
event procedure will just execute the SQL string. Personally I find it 
better to put the SQL string in the VBA rather than use a saved query. 
Consequently I find myself using this little utility quite often to bring a 
SQL statement into VBA code:
    Copy SQL statement from query to VBA
at:
    http://allenbrowne.com/ser-71.html

The SQL standard also includes DDL (Data Definition Language), for 
manipulating the data schema (creating/modifying/deleting 
tables/fields/indexes/constraints.) This is too restricted in Access (JET) 
to be much use, e.g. you can't set some important field properties this way. 
Consequently, you'll find it more useful to use DAO to manipulate or 
enumerate TableDefs, Fields, Indexes, Relations, and their properties. (In 
practice DML is 99% of the SQL needed in a normalized database at runtime 
anyway.)

If you are interested in manipulating the schema (not merely Select or 
Action queries), this link leads to examples of DAO, ADO, ADOX, and DDL SQL:
    http://allenbrowne.com/tips.html#Examples%20by%20Library

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sarah" <Sarah@discussions.microsoft.com> wrote in message 
news:BB6ED13D-C3D9-4BE6-996F-EC6D12CBDDB6@microsoft.com...
> I am learning (slowly) how and where to use VBA and SQL.  I've noticed 
> that
> some jobs can be done using either.  For example, I can have a button with
> 'ON CLICK' code including the SQL fragment:
>
>   UPDATE tblData SET  Description = "Not Available"     ,
>
> but I can do the same thing with a Sub containing a code fragment like:
>
>   With rstData
>      Do While Not .EOF
>         !Description = "Not Available"
>         .Update
>         .MoveNext
>      Loop
>   End With
>
> It seems like there are probably many jobs that can be done with VBA alone
> or by involving SQL.  Are there guidelines out there as to when it might 
> be
> best to adopt one approach over the other?
>
> thanks in advance
> Sarah
> 
0
Allen
1/26/2010 4:43:35 AM
> Consequently I find myself using this little utility quite often to bring 
> a SQL statement into VBA code:
>    Copy SQL statement from query to VBA
> at:
>    http://allenbrowne.com/ser-71.html

I use that quite a lot too, thanks Allen. 


0
Jellifish
1/26/2010 6:34:16 PM
=?Utf-8?B?U2FyYWg=?= <Sarah@discussions.microsoft.com> wrote in
news:BB6ED13D-C3D9-4BE6-996F-EC6D12CBDDB6@microsoft.com: 

> Are there guidelines out there as to when it might be 
> best to adopt one approach over the other?

The simplest guideline is this:

If you're making exactly the same change to all the records, a SQL
UPDATE will definitely be faster. 

If you're making a change based on logic that is specific to each
row but that draws all of its criteria from the row that's being
updated, a SQL UPDATE will almost always be faster. 

If you're doing your update based on information drawn from another
table or from other records in the same table, it depends. I'd
always try SQL first before attempting to code it sequentially. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
1/27/2010 2:47:51 AM
Reply:

Similar Artilces:

Changing Front End Back End Connection Through VBA
Hi, In a form in the front-end the location of the backend is mentioned. (this is the result of a dlookup from a table that holds the locations of all backends; user chooses which backend to connect to) How can I define that when the user clicks a connect button, all the tables in the front end are connected to the back end which is chosen? Thnx for your help! hi Rishi, On 04.02.2010 12:12, Rishi wrote: > How can I define that when the user clicks a connect button, all the tables > in the front end are connected to the back end which is chosen? http://www.mvps.o...

jet + odbc sql 2005 expr- cannot delete record
Hi, I'm creating a front end ms access form to manage a sql 2005 express database via odbc. I'm using a main table called dbo_tab_interv. As far I create records manually ( fast input directly into the table ) there are no problems and I can delete records aswell, but if I create a record with the followiond query: query1: INSERT INTO dbo_tab_interv ( interv_data, interv_client ) VALUES (now,'acme s.r.l.'); I cannot delete it no more from the table unless I use another query such the following: query2: DELETE FROM dbo_tab_interv WHERE interv_id = 48 If I try to ...

excel vba
I am trying to devise a code that will allow me to show only particula columns in a spreadsheet, or that will hide particular columns in spreadsheet. I have set up a password form and i have a question. When I run this and the person puts the name in as "john" and th selection clears its contents, what code can i use so that when someon enters the name "joe" then the values that were in those cells wil return to their original selves. I guess sort of like a temporar hiding of the values? Private Sub CommandButton1_Click() If TextBox1.Text = "john" Then UserFor...

publisher vs quark
Do you know if Microsoft Publisher would be able to open existing QuarkXPress files? ...

SQL Error: 512 in GP8 Mfg Module
Hello Everybody. The past few days, we get this error when working in the Component Trx Entry window (every trx) After clicking Ok on all the error windows, we are able to complete the Trx. We found KB Article 914891 that recommends an upgrade to the latest service pack, and will do this a.s.a.p. In the meantime, what is the impact of the error? Is data written correctly to the tables, etc .. Thx for your help. Maria. ...

class wizard in VS 2005
Hi, In VC++ 6.0, I can get the class wizard by hitting ctrl-C if I want to add an event handler (say a mouse down event) to a class (say a CView derived class). What should I do in VS 2005? I tried to review the document but didn't find one that introduces a 6.0 user about where they can find their old familiar features. Your help is much appreciated. JD "JD Young" <jdt_young@yahoo.com> ha scritto nel messaggio news:uumVDvkyHHA.4928@TK2MSFTNGP03.phx.gbl... > Hi, > > In VC++ 6.0, I can get the class wizard by hitting ctrl-C if I want to add > an e...

Mouse vs. Keyboard Shortcut Keys
Hi, We have Windows XP and use Microsoft Office 2003. We will be upgrading to Office 2007 the end of this year. There is an ongoing debate in my department (word processing operators) between those who like to use the mouse, and those who prefer keyboard shorcuts. Those who prefer keyboard shortcuts say it's faster than the mouse because you don't have to take your hands off the keyboard. (Those who use the mouse do also use some keyboard shortcuts; by contrast those who prefer keyboard shortcuts tend to not use the mouse at all.) The research I have done does i...

New Balloon
New to this I have followed the instruction to set up an help balloon, copied and pasted the example into my module box but it does not work. I do not really know where to start. I go into VBA and then Help am I supposed to do something else Thanks john -- JohnM So, what have you got in your module box? "JohnM" wrote: > New to this > > I have followed the instruction to set up an help balloon, copied and pasted > the example into my module box but it does not work. > I do not really know where to start. I go into VBA and then Help am I > supposed to do...

MDB to ADP with SQL
With the next ShortCut I open an ADP with SQL tables "C:\Archivos de programa\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Mis documentos\Sistemas\ToSQL\ClienteCU4.adp" /wrkgrp S:\BASE\system.mdw . In this Work Group (MDW) I definied the security groups and users with permission over forms and macros. In ADP this security no works, no shows the logon. How I made security Access to an Proyect?? "Jackson Romero" <JacksonRomero@discussions.microsoft.com> escribi� en el mensaje news:38756650-C28F-48EC-8FD3-FB12AC1496C7@microsoft.com... > With the next S...

row striping with VBA
I have used the followinf CF to shade every other row a certain colour: =MOD(ROW(),2)=0 When data is copied and pasted (xlPasteFormats) in to this sheet, this row striping is overwritten. Rather than tweak the copying code, I was wondering if there is a VBA method which will achieve the same result as the formula above and stripe alternate rows? -- Traa Dy Liooar Jock ASAP Utilities has a procedure to do this (have a look under the Columns + Rows Menu. See "Shading Alternate Rows in an Excel Worksheet" in the Excel help menu. "Jock" <Jock@discuss...

Sharepoint 3.0 and SQL
I am getting ready to install Sharepoint 3.0 for a customer. I have installed sharepoint 2.0 and the default creates an MSDE database. The customer has SQL so I want sharepoint to install to the current SQL. Does 3.0 give you the option to where you want to create the SQL database or does it also create an MSDE database. If the default is the MSDE how do you override this option? Thanks, Amy Amy, I believe what you are looking for is described in this Microsoft Support KB article http://support.microsoft.com/kb/843580 Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Glo...

altering XML on sql server and getting information on them
Hello there I have an sql process which getting XML from one source or more. IT should get the XML and its attributes, nodes, ect and alter it to a diffrerent xml for example declare @XM xml set @xml = (select Product_id, product_name from products for xml auto) the result shoud be: <Products total="30"> <Product Product_ID="1" ... /> <Product Product_ID="2" ... /> </Products> in essuming i don't know the structure first how can i know what is the name of the node, its attributes? On Sun, 20...

Equivalent to the Least function in SQL (or min in Excel)
Is there an equivalent to the Least function inSQL (or min in Excel) within Access? I'm trying to find the Min value in a series of numbers calculated by an expression. for example Min([Field1]-10,[Field2]-10,[Field3]-10) Also, would there be a similar function to get at the Max as well? How about Min and Max just like you use in Excel, these also work in Access... -- Maurice Ausum "CH" wrote: > Is there an equivalent to the Least function inSQL (or min in Excel) within > Access? > > I'm trying to find the Min value in a seri...

SQL DBA Position Available in NW Atlanta
Hi All, My client is searching for a SQL DBA to provide administration and day to day support for company databases and related systems. This person will also establish standards, processes and procedures for the creation, maintenance and long term archiving of data resources. We are seeking 5+ years experience managing corporate databases and data warehouses; diverse database admin skills; excellent verbal and written communication skills and organizational skills. Some experience in the insurance industry is preferred as is the MCDBA certification. For more details, please email me with...

SubForm using as resource a pass through from SQL Server 2000
Using: mdb Access 2002+ and SQL Server 2000 I want a subform with data from a pass through. I get the error about pass throughs don't work for subforms. So I tried a listbox as a subform which sorta works. The listbox data doesn't change when the main form changes. My problem is I need a subform or listbox that will change as the main form changes using a pass through for the information. I really don't want to create tables because of security purposes. Any ideas? -- Message posted via http://www.accessmonster.com ...

User Defined Function vs. Named formula
I have used Named Formulas and am now learning about User Defined Functions. One thing I found with named formulas is that the naming conventions around global/local selections. Reading about UDFs makes me think they might be a better option for a lot of my work. Any insight as to When to use one over the other will be appreciated. Thanks Robert IMO they are different beasts. A named formula would be used to shorten formulae by including a name rather than the sub-formula (especially where it is repeated), and to improve readability/maintainability, as the name would/should be meanin...

Windows 7 x64 vs. 32bit games
I got a barebones kit last week with the idea of making it a game monster that will still be good 5+ years from now, so I beefed up the memory to 8GB. *now* I understand that only 64bit OSes can see beyond 4GB. So fine, I'll install the 64bit version of Windows 7. The thing is, mostly I got it to run games already in my collection -- Call of Duty, Age of Empires, etc. Will those run OK on the 64bit platform? Do I need do run them in XP Compatibility mode or otherwise do anything funky beyond install and play? Conversely, if they will NOT run on the 64bit version, what...

Lead vs Non-Lead sales process
I would like to implement a different sales process depending on whether the opportunity was generated from a lead or not (i.e. lead conversion has an automatic qualification step, whereas an opportunity created without a lead does not - and the process is therefore a little different). To do so, I am looking for a field that I can use to differentiate between the 2 and the obvious choice is 'originating lead'. However since the type is 'lookup' it does not appear in the create condition drop down field list. Anyone else come up against this issue? If so, how did you about ad...

MenuBar like VS IDE
Hi, I have made a hello world MDI MFC application. I want to change the menu to the bitmap menu as displayed in VS 2003 IDE. I want to know the strategy for doing this. I know I have to override the following API in the mainframe:- 1) OnDrawItem 2) OnMeasureItem 3) OnMenuPopUp. Accoding to me, it should not include much effort. When the menu pops below, I need to change the background color to blue and put the bitmap there. If any one has done, Can he/she share his/her experience, so that I can do it fast ? ...

Which SQL Server?
I am considering moving my BE to SQL Server 2005 (I have Access 2003), but I don't get all the versions they have. I will have 20 or so users talking to SQL Server tables via Access front ends on their desktop. Can I get away with the free developer version? (I know -- I wish).Also, should I NOT go ADP with the idea that the company will someday upgrade to VISTA, which seems to be ADP hostile?Thanks in advance for your help. PhotoFinish <PhotoFinish@discussions.microsoft.com> wrote:>Thanks. That is good to know. I think Microsoft's comparison chart is >lacking a simpl...

SQL OPtions when setting up new user
In version 7.5, there was options for updating the SQL server with user information, (checkboxes) I cant find that in version 9. What is happening is we add users to PDK, and have that program update the SQL database with the user information. Now when I try and add a user to Great Plains, I get the message that the user is already a user in the database. Where can you turn the option off to update the SQL database and just add the user to Dynamics? thanks! -- Doug Same question. Please advise! "Doug" wrote: > In version 7.5, there was options for updating the SQL...

VBA in Excel to operate word
In excel VBA I need to open up a particular word document, save it as a different name then step back into vba for excel. Can anyone right an example piece of code that works for this? Thanks If that's all you're doing, it might be easier just using filecopy. FileCopy Source:="c:\myoldfolder\doc1.doc", _ Destination:="c:\mynewfolder\doc2.doc" HHH wrote: > > In excel VBA I need to open up a particular word document, save it as a different name then step back into vba for excel. Can anyone right an example piece of code that works for this? > Than...

Deleting Sheet with VBA
Hi, I have this code that allows me to delete all sheet but the one i have selected: Sub DelSheet() For Each sheete In Sheets If ActiveSheet.Index <> sheete.Index Then Application.DisplayAlerts = False sheete.Delete Application.DisplayAlerts = True End If Next sheete End Sub Is there a way to change this code to allow me to delete all but the currently selected plus a sheet called "SS"? Thank you all, Bre-x One way: Option Explicit Sub DelSheet() Dim sheete As Worksheet For Each sheete In Worksheets If sheete.Name = ActiveSheet.Name _ Or L...

VBA
dear community: Everytime I update the chart data range, I have to Select i first: ActiveSheet.ChartObjects("Chart 1").Select ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R35C13:R" & iROWS "C13" ActiveChart.SeriesCollection(1).Values = "=Sheet1!R35C19:R" & iROWS "C19" ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R35C13:R" & iROWS "C13" ActiveChart.SeriesCollection(2).Values = "=Sheet1!R35C20:R" & iROWS "C20" I tried this but not working: Sheet("Sheet1").Ch...

Run SQL-Server Query in Excel
Hi guys, is there a way to run a SQL-query on a SQL-Server within Excel? (Is it possible to create any JOIN-querys and DATEDIFF-functions within Excel-Microsoft Query?) Thanks much guys, Mike Maik Richter wrote: > Hi guys, > > is there a way to run a SQL-query on a SQL-Server > within Excel? Yes. I don't know it in English but in Dutch it's in Data - Externe gegevens ophalen - Nieuwe databasequery... > (Is it possible to create any JOIN-querys and DATEDIFF-functions > within Excel-Microsoft Query?) I have not needed it before, but I couldn't imagine ...