Accessing the SOLIDWORKS Electrical Database

When creating the reports in SOLIDWORKS Electrical, eventually you’ll want to venture into the territory of activating expert mode and messing with the sql query. This article isn’t going to delve into creating those queries and how they work, unfortunately (Or fortunately, depending on how you look at it). I’m going to assume you have received SQL training of some kind and know how you are supposed to build the query. But even then, it can get confusing to know what variables you need to add from the available tables to get the outcome you desire. It was nice of the developers to add some descriptions, but take for example the “translated text” table. This contains the descriptions of… well… everything. However, that means you may find the variables in there and the descriptions of what those variables contain to be a little less than helpful. Because that could be the text for a part, for a wire, document, etc, there isn’t really a way they could have improved it.

So I’m going to tell you how you can browse through the tables in MS Access to see what exactly is in the tables for a specific project. This has helped me more than just knowing which of the translated texts are English. It has helped me to know if the variable I’m picking is going to contain the full mark of a component, just the number, or the parent. A terminal block and terminal, for example, could be listed as X1-1, X1, 1, X, …you get the picture. I could add a bunch of the variables, test the query and figure out which ones I want to keep, but that can get tedious if you are building a big report from scratch. I like browsing and figuring out what I need from the beginning.

We’ll need to map the project database to MS Access in order to do this.

First thing is to know what the project ID of the project for which you want to view the SQL tables. Open project manager to find that information.

Best Practice: Duplicate the project and perform the rest of this using the duplicated project. We are going to be working in a way that allows us to directly access the SQL database and although you should not edit the data in the database, it is possible. If you accidentally or purposefully change something in the database you will mess up the project in SOLIDWORKS Electrical. Duplicating the project will prevent you from messing up something important. Odds are, you aren’t building new reports for every project so you won’t be mapping (and therefore duplicating) every project to Access. Once you’re done, delete the duplicated project.

Open MS Access. Create a new blank desktop database. You’ll have to give it a name and tell it where to save it.

On the external data tab, select ODBC Database.

Select Link to the data source by creating a linked table.

You will get the following window:

Select the New… button to create a new driver that will tell Access how to connect to the SQL database.

In the wizard that starts, scroll down to select SQL Server Native Client 10.0 as the driver type you want to set up, and click Next. Now you need to come up with a name for the driver file and where you want to store it. Click Browse, and navigate to a folder to save in. Then give it a name. One possible naming scheme is to name it based on the project ID.

Click save, next, and finish.

Now we’ll be specifying which SQL server to connect to. You can give it a description. The important thing is to select the TEW_SQLEXPRESS server (or whichever name you used for the SQL database).

Click Next.

Select the SQL Server Authentication option and input the server login identification.

If you did not change the default on installation then it will be as follows

Login ID: tew

Password: SQLpwd4ew

Click Next

VERY IMPORTANT! Change the default database from master, and select the database for the project you are working on. This is where the project ID comes into play. For example, if the project ID is 67, then select tew_project_data_67.

Click Next, then Finish.

Test Data Source… then click OK.

You’ll be back in the original driver selection window. Select the driver you just created and click OK. You’ll then need to enter the SQL password again.

This window should pop up. 

There are more tables than are needed in the list. Select all of the tables that begin with dbo.tew_. Unfortunately, Microsoft doesn’t allow shift+click to quickly select them. You’ll have to click each one individually.

Tip: Select Save password so you won’t have to type the SQL password every time you open the database.

 Click OK. If you had the save password box checked you’ll have to confirm that you want to save the password. Not just once, but for every table you selected…

 There are a couple tables that you will get a popup that looks like this:

Just select all of the fields and click OK to get back to saving that password again and again.

 Now all of the tables available for creating reports in SOLIDWORKS Electrical are on the left side of the screen. Double clicking on the one will open it so you can see everything in it.

 I’d like to reiterate that you will be able to edit the values in the table. DO NOT DO THAT! You will break the project in SOLIDWORKS Electrical, and that is especially bad if you did not duplicate your project before doing all of this.

If at this point you are like, “Great. Now What?” because you have no clue how to build a SQL query, we do cover that in our advance course, which we happen to be offering in early September, that you can sign up for.