Posted on

MS Access call Open File Dialog Window

Microsoft Access

Some time ago I was working on an MS Access project which allowed the importing of various files to have processed. Well, the situation came up again recently, so I thought I would explain how I resolved it. One of the first hurdles I ran into, was to give the user a way to pick the file they wish to import. The basic Macro functionality in Access allows you to specify a file to use, but it doesn’t give you a way to choose one during runtime.

However, with a couple lines of VBA code I was able to accomplish what I had set out to do.  This may be a little long winded explanation, but here are the steps that I took.

1) Added a Command Button to my Form.

2) In VBA editor, I created the following function. Notice the bolded section under ‘Prompt User for File Path’. This is where it pops the file dialog for you to choose the file.

Function ImportOpp()
On Error GoTo mcrImport_Err
Dim strFile_Path As String

'Prompt user for file path
Application.FileDialog(msoFileDialogOpen).Show
strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)

DoCmd.TransferSpreadsheet acImport, 10, "tblMissedOpportunity", strFile_Path, True, ""

MsgBox "Import Complete", vbInformation
mcrImport_Exit:
    Exit Function

mcrImport_Err:
    MsgBox Error$
    Resume mcrImport_Exit

End Function
 

3) Add the following call to the ‘OnClick’ event procedure to the command button.

Private Sub cmdImport_Click()
    Call ImportOpp
End Sub

Now when you click on the command button, the Open File Dialog will open so that you can choose your file.

Posted on

SQL Server – Remove All Tables from Database with this command

sql server

Here’s a quick little statement I discovered today that lets you drop all the tables from the SQL database you’re working in. By using the SQL statement, you don’t have to specify table names, or select them by using SQL Enterprise manager. Just remember, you CAN’T ROLLBACK after running this.

Just simply execute the following statement:

EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

It’s a hidden stored procedure found in SQL Sever. There are a lot more use cases for this hidden procedure which are discussed on the MS TechNet wiki here. Essentially, the commands passed into the parameters are performed on each of the tables within the database. Caution, the undocumented procedures ARE NOT recommended for use on production environments.

A little background on why I needed this. I was trying to move database tables to another SQL server and had used the Import task (yes, I realize I could have just copied the mdf file and reattached. Didn’t want to do that) Well, as I soon found out, it does copy the tables for you, but it doesn’t copy any of the indexes, primary keys, NULL settings, constraints, etc. After a brief panic attack on what I thought was going to be a lot of work, I remembered you can script the creation of the tables along with the data. By generating the scripts, it scripts the indexes, primary keys, settings, and so on. But before I could script these tables, I had to get rid of the tables from the botched copy attempt. I did not want to sit there and type DROP TABLE table1, DROP TABLE table2… That’s when I discovered the handy procedure to delete all tables in the database.

 

Posted on

VB Script To Restart A Remote Machines Service

vbscript

During my ‘Day Job’, I maintain a group of 25 servers. From time to time services have to be restarted on the servers for various reasons. The problem that I run into is I have to log into each computer manually one by one and then find and restart the service. As you can imagine this can is not only a time suck, but a very tedious and painful routine.

So the other day I set out to make this process a little smoother. So began the search for a VB script. To my delight, I was able to find what I needed to ease my pain. To help others out that are in this situation I’ve posted the code below. After putting the script below into a .vbs file, just put in the name of the service you need restarted and save. When you run the script, it will prompt you for the server name. After the service has been restarted, you receive a completion message.

strComputer = InputBox ("Enter Name")
strServiceName = "SERVICE NAME GOES HERE"


Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colServiceList = objWMIService.ExecQuery ("Select * from Win32_Service " _
& "Where Name='" & strServiceName & "'")


For Each objService in colServiceList
objService.StopService()
Wscript.Sleep 500
objService.StartService()
Next

Wscript.Echo "The "& strServiceName & " Has Been Restarted on " & strComputer
Wscript.Quit

Posted on

How To Setup Amazon Echo for Multi-Room Music

If you’re like us, one of the first things you want to do after purchasing multiple Amazon Echo devices, is to configure them for multi-room music. This enables you to setup groups which contain sets of Echo devices to play the same music throughout your house, or in specific rooms (groups).

Getting Started

In order to setup multi-room music, you’ll need an Amazon Echo, Echo Dot or Echo Show

To get started, open the Alexa App on your phon and click on Settings

amazon alexa app

After going into Settings, click on Multi-Room Music

multi-room music

The Alexa app will now give you the option to setup a Smart Home Group or an Amazon Multi-Room Music Group. Click on Amazon Multi-Room Music Group.

Alexa App setup groups

The next screen asks you to create or choose a suggested group name. For this example, I’ve selected ‘Everywhere’ as the group name. Any Echo devices that are online will then be listed to be selected as part of the group. Select the Amazon Echo devices that you want to be part of the group and click Save.

Amazon Alexa App setup

Finally, you can now play music by saying “Alexa, play [Music Station] on [Group Name]” and the music will be played on the selected devices throughout your house.