Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-20-2017, 12:44 PM   #1
Kronix
Newly Registered User
 
Join Date: Nov 2017
Posts: 99
Thanks: 26
Thanked 0 Times in 0 Posts
Kronix is on a distinguished road
Accessing ERP Software Database based on SQL Server

So I see that several types of Enterprise Resource Management software use MS SQL Server as their database platform. I was wondering, is it possible to use MS Access to access the database that these programs use the same way that you would access any other MS SQL Server database?

I think what I need to know is, when a piece of software says it is based on the SQL Server database platform, does that mean it stores its data in an MDF file that can be opened the same way as any other SQL Server database? Or is it possible that the database can somehow be encrypted and unable to access from a program outside of the ERP software, even if the programmer owns the ERP Software that created the database?

I thought this wasn't possible until I heard someone say that competing ERP software can convert their competitor's database since they are both based on SQL Server. Is it really that easy and if so how difficult would it be to instead write an Access program to work with the database that my ERP software creates (for example, if I don't like the ERP's front end or I want custom-made modules/features)?


Last edited by Kronix; 12-20-2017 at 12:54 PM.
Kronix is offline   Reply With Quote
Old 12-20-2017, 01:16 PM   #2
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 245
Thanks: 15
Thanked 58 Times in 58 Posts
jleach will become famous soon enough
Re: Accessing ERP Software Database based on SQL Server

It's entirely up to the vendor and/or IT department that's hosting the server.

Pretty much any database (SQL Server, PostgreSQL, MySQL/MariaDB, etc) can be configured to allow external connections. So, if you can talk whoever's in charge of the thing to allow you to connect to it, then yes, you should be able to (all of them have ODBC support, which is what Access would need to connect).

If it's something that gets installed on-site and the db is managed by an internal IT team, that'd be who to talk to. Although, the software vendor themselves could restrict it so noone can get to it except through certain channels.

If it's the software vendor itself or a 3rd party cloud host, you'd need to talk to those people accordingly. (Often times for cloud based services, they might expose a REST API or SOAP implementation for web service access to it, but not to the db directly).

Some vendors try to control the database itself to try and force the buyer/users to stay with them (EMR/EHR is notorious for this). They want to own the data so you can't take it and move elsewhere. ERPs tend to be a bit better about that though.

So really, it depends. Technically, it is possible to connect to any ODBC data source (which includes pretty much any version of SQL Server), but whether or not you can in practice is up to whoever is in charge of the database and their willingness to allow you to do so.

hth
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
The Following User Says Thank You to jleach For This Useful Post:
Kronix (12-21-2017)
Old 12-21-2017, 03:44 AM   #3
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,883
Thanks: 0
Thanked 629 Times in 614 Posts
Ranman256 will become famous soon enough
Re: Accessing ERP Software Database based on SQL Server

as long as you have an ODBC driver, it should connect.

Ranman256 is offline   Reply With Quote
Old 12-21-2017, 03:47 AM   #4
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 245
Thanks: 15
Thanked 58 Times in 58 Posts
jleach will become famous soon enough
Re: Accessing ERP Software Database based on SQL Server

That's completely untrue. Please read my prior reply.

Just because he has an ODBC driver does NOT mean he can connect. He needs to have appropriate permissions set up in the server, which is not always doable.
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 12-21-2017, 12:53 PM   #5
Kronix
Newly Registered User
 
Join Date: Nov 2017
Posts: 99
Thanks: 26
Thanked 0 Times in 0 Posts
Kronix is on a distinguished road
Re: Accessing ERP Software Database based on SQL Server

Thanks. Before I talk to the vendors I want to poke around on my own a bit. The vendor will only try to sell me something.

I'm trying to understand what methods are available to the software vendor to prevent access to the database (even though I own and use the license for the software). Hard database password protection that only the program knows to access the database file (theoretically hackable)? Changing the data format of the database file itself? On the fly converting of the database file into a proprietary database format that only the program can read?

In general I'm trying to understand if all of the measures they can take to lock the SQL Server database are features that are built into MS SQL Server and therefore predictable, or whether programming the frontend in Visual Studio allows them to create custom locks and/or communication protocols with the database?

Assuming the ERP software is on our local network and I have full access to the computers, will the SQL Server database always be stored in an MDF file, in which case I could just try to connect with that file to see if access is allowed? Will the SQL Server database portion of the ERP software always be stored in a separate SQL Server file?

Can I assume competing ERP vendors sometimes have to work around proprietary locks by "hacking" the competing database if they want to transfer the data to their own database?
Kronix is offline   Reply With Quote
Old 12-22-2017, 12:39 AM   #6
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 245
Thanks: 15
Thanked 58 Times in 58 Posts
jleach will become famous soon enough
Re: Accessing ERP Software Database based on SQL Server

It's not always that easy. There's lots of ways for them to prevent access to it: SQL Server is pretty robust as far as security goes and if they want to, they can lock if down pretty hard. Besides which, you might find yourself in a fairly nasty legal battle for trying to do so if it's not permitted.

They could use a proprietary format, but not sure anyone would really bother to do so as it'd be very difficult even for the simplest of cases.

Bottom line: don't count on being able to do it all the time. Have to go on a case by case basis to see if it's possible.
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 12-22-2017, 02:03 PM   #7
Kronix
Newly Registered User
 
Join Date: Nov 2017
Posts: 99
Thanks: 26
Thanked 0 Times in 0 Posts
Kronix is on a distinguished road
Re: Accessing ERP Software Database based on SQL Server

Okay, but can I get an answer on the MDF file question? Do these front end programs normally still save the data in a standard SQL Server file, or is it realistically possible that the database can be stored in a way that can't even be detected by outsiders, for example by merging the data file into another proprietary file?

This is more a question of how SQL Server works -- whether it allows programs to dump the database data into a larger proprietary data file (and thus rely on the program to "feed" the data stream back to SQL Server), or the contrary if SQL Server always demands that the database itself is stored in a standard way determined by Microsoft or industry standards that makes it universally accessible (password protection notwithstanding).

Kronix is offline   Reply With Quote
Old 12-22-2017, 04:22 PM   #8
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 245
Thanks: 15
Thanked 58 Times in 58 Posts
jleach will become famous soon enough
Re: Accessing ERP Software Database based on SQL Server

I don't know for sure, but if someone put a gun to my head and told me to take my best guess, I'd say no, it's not possible to introduce a proprietary data file format for SQL Server. Databases are an extremely fine-tuned engine humming along with all parts in harmonic motion (think about it: millions of pieces of data consulted and resolved in less time than it takes to snap your fingers...) I can't imagine that any RDBMS would support any sort of swappable persistence format. I could be wrong, but...

I would further note that just because you can get access to an .mdf may not necessarily mean you can do something with it. SQL Server is robust in security and it's layered thickly.

This all smells of someone trying to do something that shouldn't be done. Why not just check with the vendor in question an ask? (or, if you want a general idea, check with 10 vendors and get a reasonable idea).

I know no further and will bow out of this one now. Cheers,
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
The Following User Says Thank You to jleach For This Useful Post:
Kronix (12-27-2017)
Old 12-23-2017, 02:25 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,989
Thanks: 38
Thanked 3,234 Times in 3,132 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Accessing ERP Software Database based on SQL Server

you also have to consider what you want to do with the access once you have it. You may be able to achieve read access but I doubt you will get any other access without the required permissions/licences from the vendor - they won't want you messing with tables which if design or data is modified might well break the system. i.e. they will expect you to be a certified developer for that particular system.

I have done a fair number of data migrations over the years and in all cases, the data has to be downloaded from the old system by way of report into .csv/.xls and other formats, then modified to match (map to) the requirements of the new system. Often the upload to a new system will be denormalised tables which the new system will break back down into a normalised state suitable for its own purposes when imported. This is so it can ensure relationships are correctly created.

The fact that a number of applications all use the same backend is immaterial.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Kronix (12-27-2017)
Old 02-12-2018, 11:03 PM   #10
Pop Alexandra
Newly Registered User
 
Join Date: Jan 2018
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Pop Alexandra is on a distinguished road
Re: Accessing ERP Software Database based on SQL Server

Quote:
Originally Posted by Ranman256 View Post
as long as you have an ODBC driver, it should connect.
It always worked for me, but maybe try replacing the driver.
Pop Alexandra is offline   Reply With Quote
Old 06-04-2018, 04:38 AM   #11
studyabroadlife
Newly Registered User
 
Join Date: May 2018
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
studyabroadlife is on a distinguished road
Re: Accessing ERP Software Database based on SQL Server

Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005 server?
studyabroadlife is offline   Reply With Quote
Old 06-04-2018, 05:29 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,989
Thanks: 38
Thanked 3,234 Times in 3,132 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Accessing ERP Software Database based on SQL Server

Iíve never tried it but I would not be surprised if you canít since it is unlikely to that 2005 will be forward compatible with 2008
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-09-2018, 01:15 PM   #13
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,076
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: Accessing ERP Software Database based on SQL Server

Kronix,

If your application data resides in SQL Server it will be contained in SQL Server's
data files (.mdf, .ndf, .ldf).

Their front-end software is accessing the database using some ODBC driver.
Whether they use Windows Authentication or SQL Server Authentication you can emulate
that through MS-Access linked tables (providing you have appropriate access).

Regardless, with the appropriate SQL Server Admin account, you can be granted access
to their SQL Server data.

For full application functionality, the front-end might have some customized views and/or
functions that you will not have access to. But you can see the data.

Additionally, if the application uses Identity (sort of AutoNumber) or BIT columns,
your Access "view" into the data might have problems.

You CAN NOT move the Raw Data files (.mdf) or backup files (.bak) to a PRIOR version
of SQL Server. You CAN move the .BAK forward "roughly" a few versions.

You could move a .BAK file from 2005 --> 2008 --> 2014.

hth,
Wayne

__________________
Pool Players Know All The Angles
WayneRyan is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] You must use the dbSeeChanges option with OpenRecordset when accessing a sql server johnkrytus Modules & VBA 15 10-09-2014 01:51 PM
Accessing local SQL server from Access Shortcut Peek General 3 09-30-2010 01:13 PM
Problem in accessing crystal report from BO Server sk27ahmed Crystal Reports 1 01-07-2009 08:44 AM
Displaying Software information based on SoftwareID based on SerialNumber Coded_Frustrations Forms 5 11-08-2007 10:41 PM
Accessing a SQL Server database from DAO Funger Modules & VBA 1 04-23-2004 02:55 PM




All times are GMT -8. The time now is 08:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World