Sub delnonbold()
'
'
For ii = 1 To 211
ActiveSheet.Cells(ii, 2).Select
If Selection.Font.Bold = True Then
Else
Rows(CStr(ii) + ":" + CStr(ii)).Select
Selection.Delete Shift:=xlUp
ii = ii - 1
End If
Next ii
End Sub
SQL Optimizations
Sometimes you need to fasten your sql(s) a bit, or sometimes they just take too much time to be tested.
Working along, there are things you can do that would reduce the run time. It is a shocker how much you can optimize your queries. I have been able to reduce total runtime for large queries of massive data to a 1/10!
A few things that will work on all platforms, but especially proven on Access & SQL Server are:
1-Eliminate all type conversions. Especially on data with a large number of rows, this will help a lot.
2-Do NOT use iif. Terrible performance.
3-ALWAYS put joins inside one another. Do not join two different sets of tables and join the joint tables.
WRONG (a INNER JOIN b) INNER JOIN (c INNER JOIN d)
CORRECT (((a INNER JOIN b) INNER JOIN c) INNER JOIN d)
4- If you have an external source or linked data, try to do the calculations or conditions or joins of external data on the external platform. Say you have Oracle-linked tables a,b, joined with local SQL Server tables c,d. Make sure you do all eliminations and joins of Oracle tables SEPERATELY, not joining with locals at first. Only after you take all you take from Oracle and do the calculations, take the result to the local and do the rest. For example:
CORRECT :
sql1- SELECT FROM (a INNER JOIN b ON whatever) into localTempTable
sql2- SELECT FROM localTempTable,c,d ......
WRONG:
SELECT FROM a,b,c,d WHERE .....
5-Try to use numbers for conditions, selects, comparing etc. rather than text. Numbers are indexed better than texts and looping through them is easier.
6-When doing multiple joins, first join the tables that eliminate most data. Do not put in the first step a join of huge tables with little or no filtering. KEEP YOUR INTERMEDIARY JOIN RESULT TABLES AS SMALL AS POSSIBLE.
7-When doing multiple joins, try not to join two large tables directly together. (actually this is an example of number 6)
Working along, there are things you can do that would reduce the run time. It is a shocker how much you can optimize your queries. I have been able to reduce total runtime for large queries of massive data to a 1/10!
A few things that will work on all platforms, but especially proven on Access & SQL Server are:
1-Eliminate all type conversions. Especially on data with a large number of rows, this will help a lot.
2-Do NOT use iif. Terrible performance.
3-ALWAYS put joins inside one another. Do not join two different sets of tables and join the joint tables.
WRONG (a INNER JOIN b) INNER JOIN (c INNER JOIN d)
CORRECT (((a INNER JOIN b) INNER JOIN c) INNER JOIN d)
4- If you have an external source or linked data, try to do the calculations or conditions or joins of external data on the external platform. Say you have Oracle-linked tables a,b, joined with local SQL Server tables c,d. Make sure you do all eliminations and joins of Oracle tables SEPERATELY, not joining with locals at first. Only after you take all you take from Oracle and do the calculations, take the result to the local and do the rest. For example:
CORRECT :
sql1- SELECT
sql2- SELECT
WRONG:
SELECT
5-Try to use numbers for conditions, selects, comparing etc. rather than text. Numbers are indexed better than texts and looping through them is easier.
6-When doing multiple joins, first join the tables that eliminate most data. Do not put in the first step a join of huge tables with little or no filtering. KEEP YOUR INTERMEDIARY JOIN RESULT TABLES AS SMALL AS POSSIBLE.
7-When doing multiple joins, try not to join two large tables directly together. (actually this is an example of number 6)
VBA in Office 2010 - Changes
VBA projects you have previously done are still usable in Office 2010 (at least on x86) although you will need several changes. Since VBA never has reasonable error messages to tell you the error source, I'll tell you before you pull out your precious hair:
Format() for Date objects will not work now, and as usual your editor will not give you any reasonable error message. So here is how MS wants it now: VBA.Format()
Same thing for Now().
Although you can not use Now() in VBA or com/activex object properties. Time() is the way to go.
Will tell more as I come through.
BTW, just start using VSTA in future projects.
Here is a quote from Dijkstra the Father of the Computer Science, about BASIC, which is the basis for VBA (Visual Basic for Applications)
Format() for Date objects will not work now, and as usual your editor will not give you any reasonable error message. So here is how MS wants it now: VBA.Format()
Same thing for Now().
Although you can not use Now() in VBA or com/activex object properties. Time() is the way to go.
Will tell more as I come through.
BTW, just start using VSTA in future projects.
Here is a quote from Dijkstra the Father of the Computer Science, about BASIC, which is the basis for VBA (Visual Basic for Applications)
I think of the company advertising "Thought Processors" or the college pretending that learning BASIC suffices or at least helps, whereas the teaching of BASIC should be rated as a criminal offence: it mutilates the mind beyond recovery.and he said that in 1984.
Access VBA using transactions
So easy, so useful...
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
ws.BeginTrans
'Do all update/insert query executions here
'CurrentDb.Execute(...) etc.
ws.CommitTrans (or .Rollback)
ws.Close
--- Here, wksp.CommitTrans will commit the queries between BeginTrans and CommitTrans.
--- Unless CommitTrans is called, no change in your data will take place.
--- ws.RollBack will cancel previous actions in the transaction, and transaction is free to be filled again.
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
ws.BeginTrans
'Do all update/insert query executions here
'CurrentDb.Execute(...) etc.
ws.CommitTrans (or .Rollback)
ws.Close
--- Here, wksp.CommitTrans will commit the queries between BeginTrans and CommitTrans.
--- Unless CommitTrans is called, no change in your data will take place.
--- ws.RollBack will cancel previous actions in the transaction, and transaction is free to be filled again.
ASP.NET website Sample & Tutorial
THIS ENTRY IS TO BE CONTINUED...
This tutorial is for creating a basic ASP.NET website with usage of masterpage and stylesheets.
-> Download and install Visual Studio 2008
-> Choose from the menu File>New>Website
-> Choose ASP.NET Web Site, click OK
-> Go to solution Exporer in the right pane, right click on your project name, click "Add New Item". Choose "Master Page" in the upcoming panel. Click ok.
MasterPages are for defining a consistent layout for every page that uses the masterpage. Say you want a logo and a title on every page in your website. Masterpages work for you in two ways:
--Code Reuse: Instead of adding it in every page, you add the logo and title once to the masterpage and just use masterpage on every page
--Maintainability: When you need to change the logo, you will not need to change every page one by one, you will only change the masterpage and you're done.
-->Find a logo image file, rename it "logo.jpg". Drag and drop it to your project's name in the right panel. It is now a part of your project.
--> Open your masterpage file (MasterPage.Master) Find the html tags. In between the lines that starts with "
"
it will look like this:
"
-->Now go to your Default.aspx file. First line will look like this:
Replace it with
This tutorial is for creating a basic ASP.NET website with usage of masterpage and stylesheets.
-> Download and install Visual Studio 2008
-> Choose from the menu File>New>Website
-> Choose ASP.NET Web Site, click OK
-> Go to solution Exporer in the right pane, right click on your project name, click "Add New Item". Choose "Master Page" in the upcoming panel. Click ok.
MasterPages are for defining a consistent layout for every page that uses the masterpage. Say you want a logo and a title on every page in your website. Masterpages work for you in two ways:
--Code Reuse: Instead of adding it in every page, you add the logo and title once to the masterpage and just use masterpage on every page
--Maintainability: When you need to change the logo, you will not need to change every page one by one, you will only change the masterpage and you're done.
-->Find a logo image file, rename it "logo.jpg". Drag and drop it to your project's name in the right panel. It is now a part of your project.
--> Open your masterpage file (MasterPage.Master) Find the html tags. In between the lines that starts with "
"
it will look like this:
"
-->Now go to your Default.aspx file. First line will look like this:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
Replace it with
<%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/MasterPage.master" CodeFile="Default.aspx.cs" Inherits="_Default" %>
Excel: Count characters in a text (say parantheses of an Access sql)
paste the sql into an excel file in A1
run the macro:
Sub subsub()
Dim a As String
a = ActiveSheet.Cells(1, 1).Value2
' ActiveSheet.Cells(1, 2).Value2 = a
b = 0
c = 0
For ii = 1 To Len(a) + 2
If Mid(a, ii, 1) = "(" Then
b = b + 1
End If
If Mid(a, ii, 1) = ")" Then
c = c + 1
End If
Next ii
MsgBox "Result: " & b & " and" & c
End Sub
This will count paranthesis. For counting something else, change accordingly.
run the macro:
Sub subsub()
Dim a As String
a = ActiveSheet.Cells(1, 1).Value2
' ActiveSheet.Cells(1, 2).Value2 = a
b = 0
c = 0
For ii = 1 To Len(a) + 2
If Mid(a, ii, 1) = "(" Then
b = b + 1
End If
If Mid(a, ii, 1) = ")" Then
c = c + 1
End If
Next ii
MsgBox "Result: " & b & " and" & c
End Sub
This will count paranthesis. For counting something else, change accordingly.
SQL tip
When you run a query with joins, tables are joined first, and only then the WHERE conditions apply.
When you have a join at FROM and then a WHERE clause, the tables will be joined first, and only after then, the WHERE conditions will be applied.
Say you have
SELECt blah FROM tbl1 LEFT JOIN tbl2 ON blah
WHERE tbl1.somefield = 'sometext'
Firstly tbl1 and tbl2 will be joined. Then the condition will apply.
This does not only cause you to use up unnecessary amounts of memory especially for large tables, but it also alters the results. This may make a huge difference, note it :)
Note: This is proven for at least JetSql(Access) and MS SQL 2008.
When you have a join at FROM and then a WHERE clause, the tables will be joined first, and only after then, the WHERE conditions will be applied.
Say you have
SELECt blah FROM tbl1 LEFT JOIN tbl2 ON blah
WHERE tbl1.somefield = 'sometext'
Firstly tbl1 and tbl2 will be joined. Then the condition will apply.
This does not only cause you to use up unnecessary amounts of memory especially for large tables, but it also alters the results. This may make a huge difference, note it :)
Note: This is proven for at least JetSql(Access) and MS SQL 2008.
Get project folder path into .aspx
In the masterpage, we define
and we set it at Page_Load as
Then we call to it in the aspx file and reach wherever we want to reach in main folder.
public string psAppPath;
and we set it at Page_Load as
psAppPath = Request.ApplicationPath;
Then we call to it in the aspx file and reach wherever we want to reach in main folder.
.NET, import a variable from .cs to .aspx or .js
Here, psAppPath is a global string variable in the .cs file, as
public string psAppPath;
Similarly, you can reach your .cs variables from javascript, but only after importing them as:
on your .js file (or the javascript extension of your .aspx))
Access VBA Open Parametrized Query As Recordset
In Access, you can open recordsets for a parametrized query only this way:
where you inject your parametries instantaneously hardcoded. The reason is, it won't allow vba to populate parameters AND open a recordset. VBA will still ask you to fill parameters, or it will prompt "Too few parameters" error. So you have to inject parametries into your actual SQL, if you need a recordset.
If you do not need a recordset and just need Query output into a datasheet etc, you can use your regular ways, as such:
Populate your parameters like:
or use form object links that will populate automatically, when you code your SQL as:
where you have a combobox named usercombo in a form named LoginForm.
So the only way you can open a recordset for a parametrized query is as the first code, where you inject the values instantaneously.
I guess that's another problem in MS Access, so don't freak out trying.
Dim qdef As New QueryDef
Dim rs As Recordset
qdef.SQL = "SELECT Users.* FROM Users WHERE Users.Usertype= '" + usercombo.Value + "' AND Users.Password= '" + passbox.Value + "';"
Set rs = CurrentDb.OpenRecordset(qdef.SQL)
where you inject your parametries instantaneously hardcoded. The reason is, it won't allow vba to populate parameters AND open a recordset. VBA will still ask you to fill parameters, or it will prompt "Too few parameters" error. So you have to inject parametries into your actual SQL, if you need a recordset.
If you do not need a recordset and just need Query output into a datasheet etc, you can use your regular ways, as such:
Populate your parameters like:
Dim qdef As New QueryDef
set qdef = CurrentDb.QueryDefs("PasswordCheck")
qdef.Parameter("UserName") = usercombo.Value
qdef.OpenQuery
or use form object links that will populate automatically, when you code your SQL as:
SELECT Users.* FROM Users WHERE Users.Usertype=Forms!LoginForm!usercombo
where you have a combobox named usercombo in a form named LoginForm.
So the only way you can open a recordset for a parametrized query is as the first code, where you inject the values instantaneously.
I guess that's another problem in MS Access, so don't freak out trying.
Lex & Yacc - Compile And Run
Compiling lex & yacc (on unix command line)
Note: You must have written your lex and yacc codes beforehand. More on this later.
1)Compile and run a standalone lex code
-open command line
-write "emacs example.l"
(emacs, a text editor, opens. paste your code into there by right clicking the mouse once. ( yes, linux is weird))
-press ctrl-x ctrl-s to save
-press ctrl-x ctrl-c to close
-write "lex example.l"
-write "gcc -o example lex.yy.c" to compile
-write "./example" to run
2) compile and run a lex and yacc together
-open command line
-write "emacs example.l", press enter end right-click on space to paste your code
-press ctrl-x ctrl-s to save
-press ctrl-x ctrl-c to close
-write "emacs example.y", paste your yacc code and do the same as above
-write "lex example.l"
-write "yacc example.y"
-write "gcc -o example y.tab.c" to compile both your files
-write "./example" to run
Note: You must have written your lex and yacc codes beforehand. More on this later.
1)Compile and run a standalone lex code
-open command line
-write "emacs example.l"
(emacs, a text editor, opens. paste your code into there by right clicking the mouse once. ( yes, linux is weird))
-press ctrl-x ctrl-s to save
-press ctrl-x ctrl-c to close
-write "lex example.l"
-write "gcc -o example lex.yy.c" to compile
-write "./example" to run
2) compile and run a lex and yacc together
-open command line
-write "emacs example.l", press enter end right-click on space to paste your code
-press ctrl-x ctrl-s to save
-press ctrl-x ctrl-c to close
-write "emacs example.y", paste your yacc code and do the same as above
-write "lex example.l"
-write "yacc example.y"
-write "gcc -o example y.tab.c" to compile both your files
-write "./example" to run
About The Blog
Hey,
This blog is for sharing time-saving stuff and basic tutorials for coders.
It will contain:
-basic, simplified, do-it-yourself kind of tutorials or walk-throughs
-time-saving experiences, tips
-frequent deadlocks and workarounds
-frequently used pieces of fixed code that you won't bother to memorize
-some witty algorithm you had to figure out by yourself, and want to keep record just in case
-some other basic code
it essentially will not contain:
-anything you can google out
-anything you have to read more than three minutes
-any tutorial too complex or too detailed (you can unhappily find plenty of that on the web)
-any uncommented exhausting code
Comments and participation is most welcome. Even a "this helped me" will do a lot to keep this going.
Ideas come and go, practical knowledge is forgot. It's only what's written that stays.
Enjoy.
This blog is for sharing time-saving stuff and basic tutorials for coders.
It will contain:
-basic, simplified, do-it-yourself kind of tutorials or walk-throughs
-time-saving experiences, tips
-frequent deadlocks and workarounds
-frequently used pieces of fixed code that you won't bother to memorize
-some witty algorithm you had to figure out by yourself, and want to keep record just in case
-some other basic code
it essentially will not contain:
-anything you can google out
-anything you have to read more than three minutes
-any tutorial too complex or too detailed (you can unhappily find plenty of that on the web)
-any uncommented exhausting code
Comments and participation is most welcome. Even a "this helped me" will do a lot to keep this going.
Ideas come and go, practical knowledge is forgot. It's only what's written that stays.
Enjoy.
Subscribe to:
Posts (Atom)