how to setup SQL express to use access database file and present itself in odbc ?

[William Gaatjes]

Hi all.
I hope somebody can point me in the right direction.

Microsoft has SQL express for free to use with some limitations.
File size less than 10GB, 1CPU and 1 GB of ram.
But ideal for me.

The idea is that i can replicate the sql server at work.

The background story :
The thing is, i want to practice more and make my own schematics and pcb with Altium , a pcb /schematic designer we use at work.
I am self thaught (with help from the cadence helpdesk and of a colleague) in Cadence orcad/ allegro that we use at work.
Cadence Orcad works with a component database that is setup in access.
But Orcad communicates to the access database through odbc and the sql server we have running at work.
https://en.wikipedia.org/wiki/Open_Database_Connectivity

The thing is that we use both Orcad and although Altium has a converter, it is handier to use Altium through the odbc connection as well. And that is what we have up and running at work.
To minimise any differences i want to make a clone setup of what is going on at work at my home pc.
Also, it will be easy to keep my home database up to date by just using syncback free at home to synchronize my home database, schematic symbol and footprint directories with what is available at work. Just by copying the needed files to an usb stick and then use syncback free at home.

Since we are a small company our IT specialist is a freelance specialist working for several companies.
He is not resident in the company for me to ask question and he is rather busy.
When i see him i will ask him what to do and he is always willing to help when time permits.
So for my own convenience i try to be up to date with knowledge as possible.

At the moment i do not have vpn to the network at work nor do i have a dual boot system to facilitate the windows 10 professional with all the domain related security. I have windows 10 home.

I like it like this because for my own personal computer security at home i prefer not to be connected.
This way i have zero security issues to worry about. Since our company is more of a target than i am.

What i want to do is to setup the sql express to use the access database file (about 10MB in size) and being able to use the file through odbc with altium.
ODBC is pretty easy to setup because of the wizard and Altium has a wizard for connecting to ODBC connected sql+ access database files.

I already installed sql express but i have shut the services down for now until i know more what i am doing.
I bough a book and read it : microsoft sql sever 2005 express in 24 hours. But i have not become much wiser. I also searched the web for answers how to link a access databse to sql server express but al i found were half explanations.

My question:

I understand i have top setup some ports in the firewall that runs on windows to open up the ports to the sql server. I found the management interface to the sql server in the control panel / administrative tools / computer management. But i have no clue how to tell sql express to use the acces database file which is a mdb file.

I have windows 10 home running.
I have installed sql express 2017:
SQLServer2017-SSEI-Expr.exe
https://www.microsoft.com/en-us/download/details.aspx?id=55994

How do i setup sql server express to point to my database file and present the database file through access in ODBC ?

 

[mxnerd]

I think you can use ODBC in Access to link to SQL Server, not the other way around.

Haven't programmed for a while though.

 

[William Gaatjes]

It could be.
But it was explained to me that Orcad accesses the access mdb database file through the sql server that has an odbc connection.
With older versions of Orcad/Allegro, accessing the mdb file directly, the newer 64 bit versions need sql server. At least that was explained to me.

 

[mxnerd]

it was explained to me that Orcad accesses the access mdb database file through the sql server that has an odbc connection.

That means you have to install SQL server in order to use Orcad's Access database.

That does not make any sense.

MS Access MDB file can be a combination of Access code, with UI forms and database (data tables) itself. It also has the ability to connect to SQL Server through ODBC.

SQL Server itself is database only, and you use SQL Server management tools to manage it. Or write program to read & write data to it.

Software program usually will only use either Access or SQL as database when you install the program. And let you upgrade to SQL if you outgrow Access.

Haven't seen , heard or known any software to connect to SQL first and then use SQL connect to Access database via ODBC.

 

[William Gaatjes]

it was explained to me that Orcad accesses the access mdb database file through the sql server that has an odbc connection.

That means you have to install SQL server in order to use Orcad's Access database.

That does not make any sense.

MS Access MDB file can be a combination of Access code, with UI forms and database (data tables) itself. It also has the ability to connect to SQL Server through ODBC.

SQL Server itself is database only, and you use SQL Server management tools to manage it. Or write program to read & write data to it.

Software program usually will only use either Access or SQL as database when you install the program. And let you upgrade to SQL if you outgrow Access.

Haven't seen , heard or known any software to connect to SQL first and then use SQL connect to Access database via ODBC.

I think i know why this is an exceptional case. We already had the access database with thousands of components. When the new 64 bit version of Orcad came out, it needed sql server.
But we were not happy to create a new database. That is why we have this construction.
We already had access. Then Orcad required sql. We did not want to change anything to the access database file we already had. And thus our freelance admistrator made a coupling on recommendation by orcad support :
Orcad CIS (component information system) <> odbc <> SQL server <> Access mdb file. The file is still editted in access though.

I am going to find out more what is going on exactly when i am at my work again.
It is obvious i have not enough clues about what is going on.

 

[mxnerd]

That's interesting. If the new Orcad required SQL, it should be able to upgrade old Orcad with Access database and convert everything. Why didn't the tech do this?

The only possibility is that old version of Orcad allowed you to create extra components Access database and store data in it and use ODBC to access it. When the tech installed the new version of Orcad, either he did an in-place upgrade, or installed the new version of Orcad in an new location and ignored the old version, the info about ODBC info to the extra Access database is still retained, however.

So, the actual situation is that the new Orcad is read/writing everything in SQL now, except that extra Access database. It has connections to both simultaneously. It does not read / write Access via SQL.

Software can be designed to read/write to several different types of databases at the same time (MS SQL, MySQL, Access, DB2, PostgreSQL, Oracle), all through ODBC, or via individual database's native connection. It's the software doing ODBC connection, not SQL Server doing the ODBC connection.

I'm not Orcad user, so I don't know how exactly they designed it to access components database end users created. It's also possible that Orcad uses Access database for old and new version to store end users' components.

 

[William Gaatjes]

I finally had the time and chance to talk to our IT Tech.
He explained that Orcad does not require SQL but SQL was used for another reason. Multiuser environment. As it seems, SQL is way faster and efficient for multiuser than Microsoft access is.
Orcad CIS just needs an ODBC connection to a database and orcad CIS does not care what is behind that ODBC connection as long as the relevant fields like partnumber , description, footprints, schematic symbols are there.
That is cleared up.
At work we use Microsoft SQL server.
For practicing and hobbying at home, i do not need SQL. Because there will only be one user and that is me.
At home access will suffice perfectly.

Now Altium that we use on work also connects to the same component database and here i still am trying to understand it.
Because we have different schematic symbol fields for orcad and altium and also for pcb footprints. But everything else is the same like partnumbers, description, etcetera.
I am not sure how the orcad odbc and the altium odbc are different and if they are different.
I know from orcad CIS that when we setup the installation, we have to map inside ORCAD CIS which database field maps to a given orcad cis field like part number.
That works extremely well.
But with Altium i am still in the dark and the online website is not that much clearer.
I will ask around more.