MS-Access TRANSFORM Pivot into SQL Server T-SQL

-- MS-Access code
-----------------------------------------------------------------------------------
TRANSFORM Avg(q_percent) AS 'AvgOfq_percent'
SELECT [GUID], cast(quality_date as datetime) AS 'quality_date'
FROM #temp
GROUP BY [Guid], cast(quality_date as datetime)
PIVOT [Assessment Type]


-- T-SQL conversion
----------------------------------------------------------------------------------
SELECT *
INTO #temp2
FROM
(
SELECT [GUID], cast(quality_date as datetime) AS 'quality_date', q_percent, [Assessment Type]
FROM #temp
) t
PIVOT
(
  AVG(q_percent)
  FOR [Assessment Type] IN ("HR Call Assessment 2015", "HR Quality Assessment", "HR Ticket Assessment")
) p

MS-Access: Suppress System Messages

MS ACCESS: SUPPRESS SYSTEM MESSAGES (QUERY CONFIRMATIONS) IN ACCESS 2003/XP/2000/97

Question: In Microsoft Access 2003/XP/2000/97, how can I suppress the system messages when I run queries? For example, when a delete query is run, Access will ask you to confirm the number of deletions. How can I suppress these kinds of messages?

Answer: To suppress system messages in Access, you will need to use the "Docmd.SetWarnings" command. You could use the following code.

DoCmd.SetWarnings False

{...run queries...}
   
DoCmd.SetWarnings True

For example, you could create a button on your form and place the following code on the Click event:

Private Sub Command1_Click()

   'Turn system messages off
   DoCmd.SetWarnings False
   
   DoCmd.OpenQuery "Delete all entries"
   DoCmd.OpenQuery "Populate with new entries"
   
   'Turn system messages back on
   DoCmd.SetWarnings True
   
End Sub

In this example, we have a button called Command1. When this button is clicked, the system messages will be turned off. Then two queries are run - one called "Delete all entries" and second query called "Populate with new entries".

After the two queries are run, the system messages are turned back on.

The purpose of turning off the system messages is to hide the following kinds of messages from the users:

Access database engine stopped the process because you and another user are attempting to change the same data

The following text was found in the "Notes" column of tblPPSFOSED.Notes

#Error

The Fix
Manually Compact & Repair, then SQL Update the row

UPDATE tblPPSFOSED SET Notes = '' WHERE PPsFOSEDID = 1644

VBA Data Types

Show/Hide MS-Access 2007 Navigation Pane

See also:   

1. http://msdn.microsoft.com/en-us/library/bb256564(v=office.12).aspx
2. http://support.microsoft.com/kb/826765/en-us

Put this code into a Module of your Access DB.

Public Sub Secure_database()
'  this will require that there is a visible form already displayed!!!    With CurrentDb        .Properties("AllowShortcutMenus") = False        .Properties("AllowFullMenus") = False'        .Properties("AllowBreakIntoCode") = False        .Properties("AllowShortcutMenus") = False        .Properties("AllowSpecialKeys") = False        .Properties("StartupshowDBWindow") = False    End With        DoCmd.Save    DoCmd.CloseDatabase    End Sub
Public Sub UnSecure_database()
    With CurrentDb        .Properties("AllowShortcutMenus") = True        .Properties("AllowFullMenus") = True        '.Properties("AllowBreakIntoCode") = True        .Properties("AllowShortcutMenus") = True        .Properties("AllowSpecialKeys") = True        .Properties("StartupshowDBWindow") = True    End With        DoCmd.Save    DoCmd.CloseDatabase
End Sub

Function ap_DisableShift()'This function disable the shift at startup. This action causes'the Autoexec macro and Startup properties to always be executed.
On Error GoTo errDisableShift
    Dim db As DAO.Database    Dim prop As DAO.Property    Const conPropNotFound = 3270        Set db = CurrentDb()        'This next line disables the shift key on startup.    db.Properties("AllowByPassKey") = False        'The function is successful.Exit Function
errDisableShift:    'The first part of this error routine creates the "AllowByPassKey    'property if it does not exist.    If Err = conPropNotFound Then        Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)        db.Properties.Append prop        Resume Next    Else        MsgBox "Function 'ap_DisableShift' did not complete successfully."        Exit Function    End If
End Function
Function ap_EnableShift()    'This function enables the SHIFT key at startup. This action causes    'the Autoexec macro and the Startup properties to be bypassed    'if the user holds down the SHIFT key when the user opens the database.            'If you want to disable the SHIFT key, type    '    ap_DisableShift in the Immediate window, and then press ENTER.    'If you want to enable the shift key, type    '    ap_EnableShift in the Immediate window, and then press ENTER.
On Error GoTo errEnableShift        Dim db As DAO.Database    Dim prop As DAO.Property    Const conPropNotFound = 3270        Set db = CurrentDb()        'This next line of code disables the SHIFT key on startup.    db.Properties("AllowByPassKey") = True        'function successfulExit Function
errEnableShift:    'The first part of this error routine creates the "AllowByPassKey    'property if it does not exist.    If Err = conPropNotFound Then    Set prop = db.CreateProperty("AllowByPassKey", _    dbBoolean, True)    db.Properties.Append prop    Resume Next    Else    MsgBox "Function 'ap_DisableShift' did not complete successfully."    Exit Function    End If
End Function

Listing all constraints in SQL Server & MS-Access

SELECT     FK_Table  = FK.TABLE_NAME,     FK_Column = CU.COLUMN_NAME,     PK_Table  = PK.TABLE_NAME,     PK_Column = PT.COLUMN_NAME,     Constraint_Name = C.CONSTRAINT_NAME FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C     INNER JOIN     INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK         ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME     INNER JOIN     INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK         ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME     INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU         ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME     INNER JOIN     (         SELECT             i1.TABLE_NAME, i2.COLUMN_NAME         FROM             INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1             INNER JOIN             INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2             ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME             WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'     ) PT     ON PT.TABLE_NAME = PK.TABLE_NAME -- optional: ORDER BY     1,2,3,4

If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:
 

WHERE PK.TABLE_NAME='something'     WHERE FK.TABLE_NAME='something'     WHERE PK.TABLE_NAME IN ('one_thing', 'another')     WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Microsoft Access

Here is some code that uses ADOX.Catalog:
 

<%     Set conn = CreateObject("ADODB.Connection")     Set cat = CreateObject("ADOX.Catalog")     conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=<path to db>"     Set cat.ActiveConnection = conn     Response.Write "<table border=1><tr>" & _         "<th>Parent Table/Column</th>" & _         "<th>Child Table/Column</th>" & _         "<th>Key Name</th></tr>"     For Each tbl in cat.Tables         if left(tbl.Name, 4) <> "MSys" then             For Each key in tbl.Keys                 If key.Type = 2 Then                     For Each col in key.Columns                         Response.Write "<tr><td>" & tbl.Name & "." & _                             col.Name & "</td><td>" & _                             key.RelatedTable & "." & _                             col.RelatedColumn & "</td><td>" & _                             key.Name & "</td></tr>"                     Next                 End If             Next         End If     Next     Response.Write "</table>"     Set cat = Nothing     conn.Close : Set conn = Nothing %>

MS-Access export to Excel

DoCmd.TransferSpreadsheet acExport, , "qryNameHere", "C:\YourFullPathHere\Book1.xls", False, "NewSheetName"

Export Access data into PowerPoint chart

http://support.microsoft.com/kb/200551


Set Pwr_Pnt = CreateObject("Powerpoint.application")
Pwr_Pnt.Activate
Set Presentation = Pwr_Pnt.Presentations.Open (Template_Name)
Pwr_Pnt.ActivePresentation.SaveAs PP_Filename
With Presentation
 DoCmd.OpenForm "RTS_Chart"
 Screen.ActiveForm!TheChart.Action = acOLECopy ' TheChart is the name of my chart in the form
 SlideNum = SlideNum + 1
 .Slides.Add SlideNum, ppLayoutTitleOnly
 .Slides(SlideNum).Shapes
 (1).TextFrame.TextRange.Text = "Actual vs. Projected
 Expenditures"
 .Slides(SlideNum).Shapes.Paste
end with
Here's how to add a slide with bulleted text:
With Presentation
SlideNum = SlideNum + 1
.Slides.Add SlideNum, ppLayoutTitle
.Slides(SlideNum).Shapes
(1).TextFrame.TextRange.Text = "Internal Issues"
.Slides(SlideNum).Shapes(1).Top = 0
.Slides(SlideNum).Shapes(1).Left = 100
.Slides(SlideNum).Shapes.AddTextbox
msoTextOrientationHorizontal, 100, 100, 200, 150
.Slides(SlideNum).Shapes
(2).TextFrame.TextRange.Text = "None"
.Slides(SlideNum).Shapes(2).Top = 100
.Slides(SlideNum).Shapes(2).Left = 20
.Slides(SlideNum).Shapes
(2).TextFrame.TextRange.ParagraphFormat.Bullet.Type =
ppBulletUnnumbered
.Slides(SlideNum).Shapes
(2).TextFrame.TextRange.ParagraphFormat.Alignment =
ppAlignLeft
end with
If you select the MIcrosoft Powerpoint 9.0 Object Library
as one of your references in VB, then you can go into the
object browser (View->Object Browser) and look at all the
classes and properties that are available to you.
I found a lot of information in the microsoft
knowledgebase articles 200551 and 209960
'##########################################################

Option Compare Database
Option Explicit
Sub cmdPowerPoint_Click()
Dim db As Database, rs As Recordset
Dim ppObj As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
On Error GoTo err_cmdOLEPowerPoint
' Open up a recordset on the Project List table.
Set db = CurrentDb
Set rs = db.OpenRecordset("Project List", dbOpenDynaset)

' Open up Powerpoint.
Set ppObj = New PowerPoint.Application
Set ppPres = ppObj.Presentations.Add
' Setup the set of slides and populate them with data from the
' set of records.
With ppPres
While Not rs.EOF
With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
.Shapes(1).TextFrame.TextRange.Text =
CStr(rs.Fields("Topic_Owner
(my:myFields/my:CBT_Topic_Owner)").Value)
.Shapes(2).TextFrame.TextRange.Text =
CStr(rs.Fields("Project_Name").Value)
End With
rs.MoveNext
Wend
End With
Exit Sub
err_cmdOLEPowerPoint:
MsgBox Err.Number & " " & Err.Description
End Sub


'#############################################
Sub cmdPowerPoint_Click()
    Dim db As Database, rs As Recordset
    Dim ppObj As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
   
    On Error GoTo err_cmdOLEPowerPoint
   
    ' Open up a recordset on the Employees table.
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
   
    ' Open up an instance of Powerpoint.
    Set ppObj = New PowerPoint.Application
    Set ppPres = ppObj.Presentations.Add
   
    ' Setup the set of slides and populate them with data from the
    ' set of records.
    With ppPres
        While Not rs.EOF
            With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
                .Shapes(1).TextFrame.TextRange.Text = "Hi!  Page " & rs.AbsolutePosition + 1
                .SlideShowTransition.EntryEffect = ppEffectFade
                With .Shapes(2).TextFrame.TextRange
                    .Text = CStr(rs.Fields("LastName").Value)
                    .Characters.Font.Color.RGB = RGB(255, 0, 255)
                    .Characters.Font.Shadow = True
                End With
                .Shapes(1).TextFrame.TextRange.Characters.Font.Size = 50
            End With
            rs.MoveNext
        Wend
    End With
   
    ' Run the show.
    ppPres.SlideShowSettings.Run
   
    Exit Sub
   
err_cmdOLEPowerPoint:
    MsgBox Err.Number & " " & Err.Description
End Sub

PowerPoint Automation from Access

http://msdn.microsoft.com/en-us/library/aa159920.aspx