Access query to SQL

[BridgeRE]

I'm looking for a way to run queries and whatnot from MS Access to SQL Server 2005 Databases. I do not want to convert anything, just want to use Access to connect and query. I'm not sure how to get started on this. any help?
Also is there way to make a program out of it, such as click on an icon, input you query and run?

Thanks,
Marc

 

[GingerR]

You are looking for an ODBC Connection.

You would get permission from the SQL Server admin to connect to their database; they may set up certain "Views" or other permissions for you to connect to. They will give you connection information, such as db name, server, login ID and password.

Then set up in Control Panel + Administrative Options + Data Sources (ODBC).

Then in your Access db, you'd hit

FILE+GET EXTERNAL DATA + LINK TABLES and scroll to the bottom and pick ODBC Databases and your thing will be there to pick from a list.

Another way is to write a "pass through query", so look that up as well.


As for "is there a way to make a program out of it", well, just write a query based on the table or the pass-thru query. This is a basic question, maybe you do not have any MS Access experience? There could be a ton of data tho, and take a long time. I use some SQL Server data and it takes 20 minutes for the query design to open.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Banyardi1]

Marc,

GingerR describes two of the three ways to run queries in Access to pull data from SQL Server 2005. The third way is to open an Access Data Project (ADP), which has advantages and disadvantages. An ADP project is using Access as a GUI interface with SQL Server, and all the real processing occurs on the server instead of the client. When you link tables with ODBC, the queries run on the client, so all the data from a view or table will come over the wire to your desktop to be filtered and sorted. Pass-thru queries do run on the SQL server, so they are an intermediate step between Access ADP and ODBC linked tables in an Access MDB.

The key to deciding which strategy to pursue is to understand what you need to do. If you are just pulling a few thousand rows from a view or table and then making a local table in an Access MDB file, then ODBC linked tables are sufficient. If you are running a massive select query with multiple joins and where clause criteria and you need the results locally in your MDB, then you should use a pass-thru query. If you are develeping a two/three tier application with SQL Server as the back-end, then you should probably use an Access ADP. Just my two cents.

Brandon Forest
Database Administrator

 

[BridgeRE]

Thank you all for the information. I'm a Network Admin and was asked to figure this out. I am by no means a DB person, my expirience with Access is pretty pathetic and can only really administer (backup, restore, attach, mirror)SQL Server.

 

[Banyardi1]

BridgeRE,

No problem. Access is a great tool, but like all tools it has it's strengths and weaknesses. I've been programming Access applications since verson 1.0. I use it to prototype advanced SQL Server applications, complex interdepartmental apps, desktop apps, quickie DBA solutions and everything in between.

The development process always starts with a simple question to ask: "What business problem are you trying to solve?". Sometimes the answer to that question leads to an Access application, sometimes it leads to a 3-tier client server Dotnet windows application, and sometimes it may lead to a Java web application. The key is to not think about the solution when you ask the question, but ask it in abstract business terms, divorced from the technology. Once the business problem has been defined then you can start defining a technological solution based on the real-world constraints, such as time, money and available resources.

I enjoy answering questions like this because it makes me think things through, so please keep'm coming.

Just my two cents.

Brandon_Forest@sbcglobal.net