ADODB Connection from Excel to SQL Server Database

To  create an ADOBD connection from Excel to SQL Server, first make sure to add a reference to Microsoft ActiveX Data Objects Library X.X Library (choose the highest version available).


Private Sub Worksheet_Activate()
On Error GoTo ErrorHandler
Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.ConnectionString = "Provider='sqloledb';Data Source='SERVERNAME';" & _
        "Initial Catalog='DBNAME';Integrated Security='SSPI';"
    con.Open
    MsgBox "Con state: " & GetState(con.State)
   
   
    ' clean up
    con.Close
    Set con = Nothing
    Exit Sub
   
   
ErrorHandler:
    ' clean up
    If Not con Is Nothing Then
        If con.State = adStateOpen Then con.Close
    End If
    Set con = Nothing


    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub



Public Function GetState(intState As Integer) As String

   Select Case intState
      Case adStateClosed
         GetState = "adStateClosed"
      Case adStateOpen
         GetState = "adStateOpen"
   End Select

End Function

Excellent SQL Server eBook

Found an excellent resource for troubleshooting the most common problems with SQL Server...
http://blogs.msdn.com/b/sqlforum/archive/2011/06/10/sql-server-faqs-ebook-with-database-engine-ssrs-ssis-ssas-.aspx

SQL Server 2005 SSIS Best Practices

I recently came across an excellent blog post from Mr. Kuldeep Chauhan and wanted to keep it handy for my own personal reference. Here it is in it's word-for-word...


Here are the 10 SSIS best practices that would be good to follow during any SSIS package development

The most desired feature in SSIS packages development is re-usability. In other ways, we can call them as standard packages that can be re-used during different ETL component development. In SSIS, this can be easily achieved using template features. SSIS template packages are the re-usable packages that one can use in any SSIS project at any number of times. To know more about how to configure this, please see http://support.microsoft.com/kb/908018

Avoid using dot (.) naming convention for your package names. Dot (.) naming convention sometime confuses with the SQL Server object naming convention and hence should be avoided. Good approach would be to use underscore (_) instead of using dot. Also make sure that package names should not exceed 100 characters. During package deployment in SQL Server type mode, it is noticed that any character over 100 are automatically removed from package name. This might result your SSIS package failure during runtime, especially when you are using ‘Execute Package Tasks’ in your package.

The flow of data from upstream to downstream in a package is a memory intensive task, at most of the steps and component level we have to carefully check and make sure that any unnecessary columns are not passed to downstream. This helps in avoiding extra execution time overhead of package and in turn improves overall performance of package execution.

While configuring any OLEDB connection manager as a source, avoid using ‘Table or view’ as data access mode, this is similar to ‘SELECT * FROM , and as most of us know, SELECT * is our enemy, it takes all the columns in account including those which are not even required. Always try to use ‘SQL command’ data access mode and only include required column names in your SELECT T-SQL statement. In this way you can block passing unnecessary columns to downstream.

In your Data Flow Tasks, use Flat File connection manager very carefully, creating Flat File connection manager with default setting will use data type string [DT_STR] as a default for all the column values. This always might not be a right option because you might have some numeric, integer or Boolean columns in your source, passing them as a string to downstream would take unnecessary memory space and may cause some error at the later stages of package execution.

Sorting of data is a time consuming operation, in SSIS you can sort data coming from upstream using ‘Sort’ transformation, however this is a memory intensive task and sometime result in degrade in overall package execution performance. As a best practice, at most of the places where we know that data is coming from SQL Server database tables, it’s better to perform the sorting operation at the database level where sorting can be performed within the query. This is in fact good because SQL Server database sorting is much refined and happens at SQL Server level. This in turn sometime results overall performance improvement in package execution.

During SSIS packages development, most of the time one has to share his package with other team members or one has to deploy same package to any other dev, UAT or production systems. One thing that a developer has to make sure is to use correct package protection level. If someone goes with the default package protection level ‘EncryptSenstiveWithUserKey’ then same package might not execute as expected in other environments because package was encrypted with user’s personal key. To make package execution smooth across environment, one has to first understand the package protection level property behaviour, please see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsprotectionlevel.aspx . In general, to avoid most of the package deployment error from one system to another system, set package protection level to ‘DontSaveSenstive’.

It’s a best practice to take use of Sequence containers in SSIS packages to group different components at ‘Control Flow’ level. This offers a rich set of facilities o Provides a scope for variables that a group of related tasks and containers can use o Provides facility to manage properties of multiple tasks by setting property at Sequence container level o Provide facility to set transaction isolation level at Sequence container level. For more information on Sequence containers, please see http://msdn2.microsoft.com/en-us/library/ms139855.aspx.

If you are designing an ETL solution for a small, medium or large enterprise business need, it’s always good to have a feature of restarting failed packages from the point of failure. SSIS have an out of the box feature called ‘Checkpoint’ to support restart of failed packages from the point of failure. However, you have to configure the checkpoint feature at the package level. For more information, please see http://msdn2.microsoft.com/en-us/library/ms140226.aspx.

Execute SQL Task is our best friend in SSIS; we can use this to run a single or multiple SQL statement at a time. The beauty of this component is that it can return results in different ways e.g. single row, full result set and XML. You can create different type of connection using this component like OLEDB, ODBC, ADO, ADO.NET and SQL Mobile type etc. I prefer to use this component most of the time with my FOR Each Loop container to define iteration loop on the basis of result returned by Execute SQL Task. For more information, please see http://msdn2.microsoft.com/en-us/library/ms141003.aspx & http://www.sqlis.com/58.aspx.

Filter Combobox based on Another Combobox

How to synchronize two combo boxes in a Microsoft Access Form.


Watch the video and/or follow the step by step instructions below (this will take about 15 minutes of your time but you will get what you came for!)






Requirements

• This example uses MS Access 2007 but it should also work in previous versions.
  
• You will need six objects in your database:




• One form: frmMain
  
• Two combo boxes: The Master combo box: cboProjects and The Slave combo box: cboProjectTasks
  
• One table: tblProjectsAndTasks
  
• Two queries: The Master query: qryProjects and The Slave query: qryProjectTasks

STEP 1: Create the Form:

• Create a new form and name it frmMain.
  
• Save the form.

STEP 2: Create the combo boxes:

• Create the Master combo box.
  
• Name it: cboProjects
  
• Create the Slave combo box.
  
• Name it: cboProjectTasks
  
• Save the form.

STEP 3: Create the data table:

• Create a table and name it: tblProjectsAndTasks
  
• Add these FieldNames (and DataTypes):




• TaskID (Autonumber)
  
• ProjectName (Text)
  
• TaskName (Text)


• Populate the table.




• In this example the projects are: Alpha, Delta and Gamma.
  
• And each project has multiple tasks, for example:




• Project Alpha Task 1
  
• Project Alpha Task 2
  
• Project Gamma Task 3

STEP 4: Create the master query:

• The Master Query should be named: qryProjects
  
• This query will select unique project names from tblProjectsAndTasks.
  
• It will be the data source for the master combo box.
  
• The SQL code for this query is: SELECT DISTINCT ProjectName FROM tblProjectsAndTasks;
  
• Alternatively, you could use the following SQL code: SELECT ProjectName FROM tblProjectsAndTasks GROUP BY ProjectName;
  
• Either way, the output should be:




• Project Alpha
  
• Project Delta
  
• Project Gamma

STEP 5: Create the Slave Query:

• Create a new query and name it: qryProjectTasks
  
• This query will show tasks related to the project that is selected in cboProjects.
  
• The SQL code for this query is: SELECT DISTINCT ProjectName, TaskName FROM tblProjectsAndTasks WHERE ProjectName =[forms]![frmMain].[cboProjects].[value];
  
• At this point, running this query will not return any results because the WHERE clause is looking for the project selected in cboProjects, which is not data bound yet!

STEP 6: Data Binding the Master Combo Box:

• Give cboProjects these properties:




• Row Source: qryProjects
  
• Row Source Type: Table/Query
  
• Column Count: 1
  
• Bound Column: 1

STEP 7: Data Binding the Slave Combo Box:

• Give cboProjectTasks these properties:




• Row Source: qryProjectTasks
  
• Row Source Type: Table/Query
  
• Column Count: 2
  
• Column Width: 0";1"
  
• Bound Column: 2

STEP 8: Refreshing the Slave Combo Box:

• The slave combo box needs to be refreshed after each change in the master combo box. The VBA procedure below will refresh the slave combo box.
  
• This procedure should be tied to the On Change event of the master combo box. Private Sub cboProjects_Change() Me.cboProjectTasks.Requery End Sub

STEP 9: Clearing the Slave Combo Box:

• Refreshing the combo box does not clear the current task selected.

Modify the VBA procedure to clear the combo box:

Private Sub cboProjects_Change()

Me.cboProjectTasks.Requery

Me.cboProjectTasks = ""

End Sub


STEP 10: ENJOY!

SQL APPEND TABLE vs. CREATE TABLE

--TO CREATE A NEW TABLE:

SELECT *
INTO newTableName
FROM sourceTableName



--TO APPEND DATA TO AN EXISTING TABLE:

INSERT INTO existingTableName
SELECT *
FROM sourceTableName

Check for Numeric Values in a String using VBA

I needed to check that the values entered into a textbox called StoreID in an Access Database where numerical and composed of 3 digits. Here is one solution: on the AfterUpdate event of the textbox, put this code:

Private Sub StoreID_AfterUpdate()
Dim intLen, intChar, i As Integer
Dim strTextBox As String

intLen = Len(StoreID) 'Len is a function that counts the amount of characters in a string
If intLen <> 3 Then ' if the length is not 3, give message box
MsgBox "The field 'Store ID' requires 3 digits from 001 to 900"
Else ' if length is 3 then assign variables and start loop for checking numerical values
intLen = Len(Me!StoreID)
'Me! always refers to the active form
strTextBox = Me!StoreID
For i = 1 To 3 'For 1 to 3
intChar = Mid(strTextBox, i, 1) 'Mid function takes middle values (expression,start value, length of selection)
If IsNumeric(intChar) = False Then
MsgBox " ' " & intChar & " ' is not a number, please enter numeric values only from 001 to 900"
Exit Sub
Else
End If
Next
End If
End Sub

Call an Access Database Procedure from another Access Database

Here is an Access VBA example of how to call an external Access Database VBA procedure from within a different access database.

Sub ForeignProcedure()

Dim appAccess As Access.Application

' Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")

' Open MyDatabaseName.mdb database in Microsoft Access window.
appAccess.OpenCurrentDatabase “C:\MyDatabaseName", False
'False so as not to open in exclusive mode
' Run Sub procedure.
appAccess.Run "ForeignProcedureName"
appAccess.Quit
Set appAccess = Nothing

MsgBox "Done!"

End Sub

Call an Access Database VBA Procedure from Excel

In order to call an Access vba procedure from an Excel spreadsheet, the first thing you need in Excel is a Reference for the Microsoft Access Object Library, as seen in the picture below ...




To see this screen, from within Excel, go to VBA (press ALT + F11), then select Tools from the top menu and then References

Public Sub ProcedureInAccess()

‘this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = acApp
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing

End Sub

Delete Rows from Access Database Table Using VBA and SQL

Using the DoCmd property you can delete all rows in an Access Database table using Access VBA and SQL

Sub DeleteRows()

'Deletes all rows from tblName in an Access Database

DoCmd.RunSQL "DELETE tblName.* FROM tblName;"

End Sub

Access Database Programmer Forum

I recently came across a great resource for Access Database programming. It is a forum where you can post questions and receive knowledgeable answers from the forum's members. http://www.utteraccess.com/

Transaction Log for Microsoft Access Database

Here is one of the most useful pieces of code I have every come accross the Internet for programming an access database. This code will track all changes made by a user, meaning it will keep track of what data was changed, who changed it and when it was changed. This functionality is usually called a transaction log or a change log. I have copied the code directly from this page:http://www.tek-tips.com/faqs.cfm?fid=291 . The code was written by Michael Red a regular contributor at Tek-tips, which is a wonderful website that I have used many times for finding answers to many programming questions.

I have learned a few things not explained in his original code:

1) Put this code in a Module.
2) Call the basLogTrans function from the BeforeUpdate event of any form that you want to track changes.
3) Pass 3 values to the basLogTrans function: FormName, Name of Field with Unique ID, and the Unique ID.
In my implementation, I use Me as the form name, "CustomerID" as the Unique ID field, and CustomerID to pass the actual customer id value. So here is how I would call the basLogTrans function:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Me, "CustomerId", CustomerId)

End Sub

4) Mike says the code will work only on a form formated as single form, but I see it also works on a datasheet form.
5) He alos says that Access Security needs to be enabled, but I'm not sure it's true. When I used his original code for CurrentUser(), I got back "Admin" as the user that was making changes. But what I really want to know is the NT User ID of whoever is making the changes, therefore, I use Environ("Username"). If you have problems with the Environ function, please see here how to enable the Environ Function properly.
6) The rest of the instructions are pretty clear, but just to be safe, remember to add a reference for DAO, bind the form to a table, but who's to say this won't work on a Select Query? I don't know, why don't you tell me later? Make 2 tables, one called tblHist and the other tblHistMemo. First create tblHist, with the fields indicated in code comments in the function called basAddHist, then make a copy of it, name it tblHistMemo and change the OldVal and NewVal fields to datatype Memo.

That's it, it should work easily.



OK, HERE IS THE ORIGINAL VBA CODE:
All this needs to be placed in a Module, and called from the BeforeUpdate event of a form.


Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

'Michael Red 8/1/2002 Transaction log for Ms. Access BOUND forms.

'The following three functions and table definitions are
'(should be) sufficient to establish a 'poor mans' transaction
'log for Ms. Access bound forms.

'As noted in basAddHist, you will need to create the tables
'for History (general fields) and Memo History (history of
'JUST memo fields).

'Also, note that these functions are designed to work in
'conjunction with Ms. Access Bound forms where the view is
'set to single form. Further, the history will only capture
'the user if the db App is "secured".

'In the following, "MyKey" refers to the Record ID (Unique value)
'for the single record which the form is 'tied' to.

Dim MyDb As DAO.Database
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String


'In the Statement block below, basFlgValidRec is a routine which
'includes validation of all fields necessary to 'define' a record
'is valid. Commented out here, as I do not know what validation
'rules need to be checked/enforced.

' If (Not basFlgValidRec) Then
' basLogTrans = False 'Let User know of FAILURE!!
' Exit Sub
' End If


For Each MyCtrl In Frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
If (MyCtrl.ControlType = dbMemo) Then
Hist = "tblHistMemo"
Else
Hist = "tblHist"
End If
Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True 'Let User know sucess

End Function
Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType

Case Is = acTextBox
basActiveCtrl = True

Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl

End Select
End Function
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

'tblHist
'FrmName Text 80 Name of the form where change Occured
'FldName Text 80 Field Name of the changed value
'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
'OldVal Text 255 Field Value BEFORE change
'NewVal Text 255 Field Value after change
'UserId Text 50 User who Made Change
'MyKey Variant ?? KeyField as Indentified by Caller
'MyKeyName Text 80 'Key Field Contents

'tblHistMemo is the same structure except the "type" for the fields
'OldContents and NewContents are Memo (and therfore the length is "??")

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

With tblHistTable
.AddNew
!MyKey = Forms(Frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = Frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
' !UserId = Environ("Username") 'Orlando's Way
!UserId =CurrentUser() 'Mike's way, example of current user would be Admin
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function