Hi Folks,

I'm currently working on a scanning system project which uses an SQL database to record statistical data.

I have been told by the company IT Team that I can not use MS Access to hook into the SQL database because it causes it problems.

I would be most grateful if anyone could confirm or hopefully know of a way that would allow me to use access to extract data from an SQL database that is running live.

Any assistance would be greatly appreciated.

John

News to me. I have several Access front end databases that hook into SQL without a problem. The key issue to get the ODBC call set up correctly.
Of course, I may have been lucky so perhaps best to wait and see if anyone else responds having had difficulties.
HTH

Perhaps your IT people are worried about integrity of their data

you can use Access to write directly to SQL tables

if this is done without care, you may change /delete/add data incorrectly, which they would not want

As long as you only read data there shouldnt be any problems.

The other thing might be resources - if you want to make very demanding usage of the SQL dbs they may feel it might be detrimental to the operation of the database.

Talk to them nicely!

Hi,

Thanks for that, can you supply some details of how you did that using ODBC, then I might be able to prove otherwise to the IT Team.

I will of course await other replies as well, just in case other knowledge is out there.

John

to get to odbc

start/control panel/admin tools/data sources

probably system dsn, or file dsn, but your it people would need to give you connection details to find it. This will be a published odbc driver to connect to the database of the type you want eg SQL Server, Sage Driver etc

then its similar to using external Jet database - select the file you want to link to etc

Hi,

Thank you both for your replies. It is only my intention to have the ability to read the data in the SQL database and to copy out of it [i.e. append data to an Access table certain types of information].

Thanks once again.

John

The problems arise when people use Access improperly. It is not fair to blame Access since you can cause the same problems with any other front end if you don't know what you are doing. Typical Access applications developed by novices use tables as recordsources for forms because that technique works fine for Jet tables and they don't know any better. The effect of this is to suck every single row of the table over the wire to the Access app. This is seriously undesirable when dealing with tables of more than a few thousand rows. Other issues revolve around the use of functions or other features that cannot be directly converted to the SQL server variant of SQL and so Jet requests way too much data from the server to process locally.

If you read the KB article on client/server optimization you should get some idea of what are considered good practices and they will avoid causing responsiveness problems for the DBA.

Almost all of my applications use SQL server back ends without a problem. You only have to know what you are doing.

I totally agree with your IT people. If the people who are creating the Access front end don't know why there could be problems, this is a real danger sign.

If you just need to report on the data in SQL, ask your IT people to put it in a separate data warehouse for reporting in Access or a BI tool and to refresh it every now and then. I think that would go over a lot better.