Does anybody know how to connect to an MSSQL Database with Access?

MSSQL database using Access? Wouldn't you use something like ASP to connect as Access is only a database itself, not a language.

RedForest, it is impossible to connect a database to a database.

Why you would want to is also beyond me :D

You would use a programming language (ASP, CFM, VB.NET, whatever floats your boat) to connect to either MS Access or MSSQL.

If you have a choice I would advise you choose MSSQL since it outperforms Access substantially performance wise.

Well, you can do it with ODBC.

1. Create a blank database.
2. Choose File/Get External Data/Link Tables
3. Select Files of Type: ODBC Databases()
4. Select the DSN for your MSSQL database
5. Select the table you want
...

With linked tables, you can build an access front end to any ODBC database.

I get the distinct feeling Redforest wasn't talking about connecting tables from two databases together.
But what do I know?

Originally posted by Bulldog
I get the distinct feeling Redforest wasn't talking about connecting tables from two databases together.
But what do I know?

Access is more than just a database, it is a complete database environment and you can build forms (including web forms) and reports that work with any ODBC database. "Linked Tables" just means that the table shows up in the list within Access -- MSSQL still does all the back end work -- there isn't an Access table.

Now I wouldn't recommend using Access as a Web development tool, but it does a good job for desktop front ends. And yes, I know this is WebHostingTalk.

I just wanted to offer that it is possible to "connect to an MSSQL Database with Access", in case anyone thought otherwise.

GPFault

So you mean you can build query forms inside Access that can be used against other databases?

Well, you learn something new everyday! Wouldn't doing this slow down the performance of each considerably? I mean why wouldn't you just make straight SQL queries in ASP? Or is this for people who don't know anything about SQL?

:D