MS Access: Hide columns with Access VBA or VB Script

I needed to hide specified columns in an access table by clicking a button in the form through VBA code. I could do with the below code.

Public Sub SetColumnHidden()

    Dim dbs As DAO.Database
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Const conErrPropertyNotFound = 3270

    ' Turn off error trapping.
    On Error Resume Next

    Set dbs = CurrentDb
   
    ' Set field property.
    Set fld = dbs.TableDefs!Products.Fields!ProductID
    fld.Properties("ColumnHidden") = True
   
    ' Error may have occurred when value was set.
    If Err.Number <> 0 Then
        If Err.Number <> conErrPropertyNotFound Then
            On Error GoTo 0
            MsgBox "Couldn't set property 'ColumnHidden' " & _
                   "on field '" & fld.Name & "'", vbCritical
        Else
            On Error GoTo 0
            Set prp = fld.CreateProperty("ColumnHidden", dbLong, True)
            fld.Properties.Append prp
        End If
    End If
   
    Set prp = Nothing
    Set fld = Nothing
    Set dbs = Nothing
   
End Sub

Source: Microsoft
http://msdn.microsoft.com/en-us/library/office/aa224064(v=office.11).aspx
http://msdn.microsoft.com/en-us/library/office/ff194134(v=office.14).aspx

MS Access: Call Stored Procedure in SQL Server from MS Access VBA

I had a requirement of downloading a table from SQL server into access. But before downloading I had to run a SP in SQL Server manually and then run the form. Then I thought of calling SQL Server SP from Access itself and I found the script as below

Dim db As DAO.Database
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=yourServername;Initial Catalog=yourDatabasename;User ID=yourUsername;Password=yourPassword"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("EXEC SSP_YOUR_SP_NAME")
Set rs = Nothing
cnn.Close

If you are need to call the SP which is available in local SQL Server with Windows Authentication(No password required), Then you need to use the below script

Dim db As DAO.Database
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=yourdatabasename;Persist Security Info=False; Integrated Security=SSPI;"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("EXEC SSP_YOUR_SP_NAME")
Set rs = Nothing
cnn.Close

If Your SP requires parameters, supposing two parameters in this case you can modify the above script as below

Set rs = cnn.Execute("EXEC SSP_YOUR_SP_NAME " & parm1 & "," & parm2 & "")

MS Access: Extract Special characters only to a new column

I stuck up with a peculiar access task to extract only special characters available in the given string. Usually we always remove junk characters and normalize the data eliminating special chars. But here I needed to extract junk and put them in to the adjacent column.I achieved it by developing this below code.

Public Sub ExtrctspecialCharsOnly()
Dim n As Integer
Dim tbl As TableDef, fld As Field
Set db = CurrentDb
Dim str(7) As String

Dim i As Integer
db.Execute "UPDATE Table1 SET SpecialChars= len([InputString])"
db.Execute "UPDATE Table1 SET SpecialChars= '0' WHERE (((SpecialChars) Is Null))"
n = DMax("CInt([SpecialChars])", "Table1")
db.Execute "UPDATE Table1 SET SpecialChars= ''"
db.Execute "UPDATE Table1 SET InputString = trim([InputString])"
For i = 1 To n
db.Execute "UPDATE Table1 SET SpecialChars= [SpecialChars]+Mid([InputString]," & i & ",1) WHERE ((Mid([InputString]," & i & ",1) Not Like '*[^a-z]*') and Mid([InputString]," & i & ",1) Not Like '*[^0-9]*')"
Next i

End Sub