Run SQL proc from MS Access (my favourite way)

This is my new favourite way to call SQL procedure using VBA (in my case, from MS Access). You will note the use of the ReturnMsg parameter – I get my text for success, info or errors directly from the procedure. For an example, see the previous post on this blog.


Screen.MousePointer = 11
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDBName;Data Source=MyServerName"

cn.Open
cmd.ActiveConnection = cn
cmd.CommandText = "MySchema.MyProcName"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@queryDateParam", adDate, adParamInput, , cboQueryDate.Value) 'example input parameter
cmd.Parameters.Append cmd.CreateParameter("@ReturnMsg", adVarChar, adParamOutput, 500) 'example output parameter (see previous post for use example)
cmd.CommandTimeout = 0
cmd.Execute
MsgBox cmd.Parameters("@ReturnMsg").Value
cn.Close
Screen.MousePointer = 0

Advertisements

Record MS Access field changes to in change log table

I just wrote this little gem which is dynamic enough to withstand all the fields that I am yet to add to my form. It records changes to textboxes and comboboxes. It even takes the displayed content of the comboboxes rather than the bound data so that is end-user friendly.

Steps are:

1. Create the table (mine is in SQL then linked in Access hence the difference in name).

CREATE TABLE [dbo].[tbl_Members_ChangeLog](
[ChangeLog_ID] [int] IDENTITY(1,1) NOT NULL,
[Store_ID] [int] NOT NULL,
[ChangedBy] [varchar](50) NULL,
[DateTime] [smalldatetime] NULL,
[Field] [varchar](50) NULL,
[OldValue] [varchar](500) NULL,
[NewValue] [varchar](500) NULL,
CONSTRAINT [PK_Members_ChangeLog] PRIMARY KEY CLUSTERED
(
[ChangeLog_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_Members_ChangeLog] ADD CONSTRAINT [DF_Members_ChangeLog_DateTime] DEFAULT (getdate()) FOR [DateTime]
GO

2. Added the fOSUserName() function to your VBA module to get the windows user from here: http://access.mvps.org/access/api/api0008.htm

3. Add this code to catch the changes. You can call it from your form’s BeforeUpdate Sub:

Sub UpdateChangeLog()
Dim sSql As String
Dim rs As Recordset
Dim OldVal As String, NewVal As String

Debug.Print “—-”
‘cycle through text and combo boxes
For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If (ctl.Value <> ctl.OldValue) Or (Nz(ctl.OldValue, “”) = “” And ctl.Value <> “”) Then
If ctl.ControlType = acTextBox Then
OldVal = Nz(ctl.OldValue, “”)
NewVal = Nz(ctl.Value, “”)
ElseIf ctl.ControlType = acComboBox Then

‘get text for combobox lookup old value
If Nz(ctl.OldValue, “”) = “” Then
OldVal = “”
Else
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.OldValue
OldVal = rs.Fields(1)
rs.Close
End If

‘get text for combobox lookup new value
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.Value
NewVal = rs.Fields(1)
rs.Close

End If

Debug.Print ctl.Name
sSql = “INSERT INTO dbo_tbl_Members_ChangeLog (Store_ID, ChangedBy, Field, OldValue, NewValue) ” & _
“VALUES (” & Me.Store_ID & “, ‘” & fOSUserName() & “‘, ‘” & ctl.ControlSource & “‘, ‘” & OldVal & “‘, ‘” & NewVal & “‘);”

DoCmd.RunSQL sSql

End If
End If

Next ctl

Set rs = Nothing
End Sub

Get values from an SQL Stored Procedure into Excel (VBA macro)

I’m using the below function in an Excel macro to get the results of a simple stored procedure into an Excel spreadsheet. The function needs the connection string defined within it, but this can easily setup to be dynamic in the function parameters if needed. Also I’m only using one parameter, but I have no doubt that you create a multi-value function parameter to take several stored proc parameters.

In your macro, simply call the function and it will populate cells with your stored proc results beginning at the current active cell.

Function insertStoredProcValues(spName As String, strParameter As String)

    Dim Conn As ADODB.Connection
    Dim ADODBCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim sConnect As String

    '--DEFINE CONNECTION STRING HERE---------------------------------------------------------
    sConnect = "driver={sql server}; server=MYSERVERNAME; Database=MYDATABASENAME; UID=MYUSERNAME; PWD=MYPASSWORD;"
    '----------------------------------------------------------------------------------------

    'Establish connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = sConnect
    Conn.Open

    'Open recordset
    Set ADODBCmd = New ADODB.Command
    ADODBCmd.ActiveConnection = Conn
    ADODBCmd.CommandText = spName
    ADODBCmd.CommandType = adCmdStoredProc
    ADODBCmd.Parameters.Refresh
    ADODBCmd.Parameters(1).Value = strParameter
    Set rs = ADODBCmd.Execute()

    'Loop through recordset and place values
    rs.MoveFirst
    Do While rs.EOF = False
        For i = 0 To rs.Fields.Count - 1
            ActiveCell.Value = rs.Fields(i).Value   'insert value into cell
            ActiveCell.Offset(0, 1).Activate        'move to next cell for next value
        Next i
        ActiveCell.Offset(1, -i).Activate           'move to next row for next record
    rs.MoveNext
    Loop

    'Clean up
    rs.Close
    Set rs = Nothing
End Function
 

MS Access – Link tables with VBA

Just posting this here because I can never remember it myself. I can also never find it online either so it might be simple and handy to others too…

Sub LinkTables()
    Dim tbl As New TableDef
    Dim ConnectString As String

    ConnectString = "ODBC;DRIVER=SQL Server;SERVER=myServerName;DATABASE=myDatabaseName;uid=myUserID;pwd=myPa$$w0rd"

    Set tbl = New TableDef
    On Error Resume Next

    tbl.Name = "destinationTableName"
    tbl.SourceTableName = "sourceTableName"
    tbl.Connect = ConnectString
    CurrentDb.TableDefs.Append tbl
End Sub

Word Macro to separate pages into separate documents

This little piece of code takes a word document and saves each page as its own document with the filname “FILENAME_x”. I haven’t done extensive testing, but please feel free to comment.

    Dim i As Integer
    Dim varNumberPages As Variant
    varNumberPages = ActiveDocument.Content.Information(wdActiveEndAdjustedPageNumber)
    For i = 1 To varNumberPages
        Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Count:=1, Name:=""
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.EndKey Unit:=wdStory, Extend:=wdExtend
        Selection.Cut
        Selection.TypeBackspace
        ActiveDocument.SaveAs FileName:="FILENAME_" & i & ".doc", FileFormat:= _
            wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
            True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
            False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
            SaveAsAOCELetter:=False
        Selection.WholeStory
        Selection.Delete Unit:=wdCharacter, Count:=1
        Selection.PasteAndFormat (wdPasteDefault)
        Selection.HomeKey Unit:=wdStory
    Next i