Using Access to Hook Into SQL Database - Urgent

[JohnLee]

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

 

[Malcy]

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

 

[gemma-the-husky]

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!

 

[JohnLee]

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

 

[gemma-the-husky]

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

 

[JohnLee]

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

 

[georgedwilkinson]

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.