Is there a way to get SQL Server 2005 to query 3 or more separate Access databases (each returning 1 record) in one SQL statement?
+5
A:
Yes, but it will require you to create a Linked Server instance for each Access database. See here for details about creating Linked Server instances on SQL Server 2005 to MS Access.
Once you have those in place, you can query SQL Server and it will pass on the queries to the respective Access databases based on using the Linked Server instance notation when specifying tables in your SQL Server queries.
OMG Ponies
2010-01-28 20:44:45
+1
A:
What you want is a Linked Server for each of the Access databases.
Robert Harvey
2010-01-28 20:45:01
+1
A:
Just be aware that in SQL 2005 64-bit you won't be querying current versions of Access or Excel through linked servers any time soon.
Emtucifor
2010-01-28 21:10:15
Why would that be? Can 64-bit SQL Server not utilize 32-bit components, like Jet/ACE? Do note that Access 2010 will have a 64-bit version, and thus, there is a 64-bit version of the ACE in beta. It's also available separately as a download (though it's still in beta, of course).
David-W-Fenton
2010-01-29 19:48:20
@David-W-Fenton: Correct, though I'd say the 32-bit components can't provide 64-bit functionality. "Microsoft Jet" just isn't an available option on a 64-bit server. It's nice to hear about the 64-bit version of Access 2010, but I'll remain skeptical until I see it work.
Emtucifor
2010-01-30 00:08:31
Other people have used the beta of the 64-bit ACE and seem to be having good results. Yes, it's a beta, but you should give it a try as it might resolve the problems you're having.
David-W-Fenton
2010-01-30 17:24:37
@David-W-Fenton: Our organization is still on Access 2003. Will the 64-bit ACE drivers work with that? If not, no go. :)
Emtucifor
2010-02-01 17:43:02
I'm not sure I understand. The fact that the data tables you are querying are in A2003 format does not in any way preclude using the ACE to work with those databases, as A2003 is a native format for the ACE. Jet/ACE has always been backward-compatible, i.e., later Jet engines can read/write data files in earlier versions. So ACE should have no difficulties with Jet 4.0 data files.
David-W-Fenton
2010-02-01 20:58:07
@David-W-Fenton: Great, I'll look into it. I know Access is generally backward compatible, but the 64-bit / 32-bit part calls into question all my old assumptions.
Emtucifor
2010-02-01 22:42:38
@Emtucifor: data files are neither 32- nor 64-bit. It's only the programs that read and write to them that are N-bit.
David-W-Fenton
2010-02-02 22:36:09
@David-W-Fenton: I didn't mean to imply the files themselves were 32-bit or 64-bit, but that it would be hasty to assume a 64-bit driver for Jet would work with older versions of the Jet database. It seems to make sense, but I've learned that it's better to live in uncertainty than in false confidence.
Emtucifor
2010-02-03 00:38:55
@Emtucifor: Jet/ACE has always been backward compatible with its older file formats. It would be a huge exception if the 64-bit ACE were to fail to support Jet 4 data access. Of course, Jet/ACE don't support all data formats going back all the way, but it certainly supports at least the previous 2 versions.
David-W-Fenton
2010-02-04 00:37:25
@David-W-Fenton: It's always been done that way before, so it will always continue to be done that way, eh? Even when new factors are introduced! Sounds like a fun way to understand the world. Though, not a reliable one. Having a good sense of knowing when one doesn't know (that is, not having unjustified confidence) is, in my mind, a crucial hallmark of great intelligence.
Emtucifor
2010-02-04 00:41:59
@Emtucifor: Microsoft is very good with backward compatibility. For instance, they explicitly committed to always supporting any implemented feature in the Jet system tables in future versions (i.e., if you write SQL that depends on a field being in a particular system table, that SQL will always work in future versions). Assuming that MS would change its practices of a sudden after 15 years of handling Jet/ACE in a particular way seems, er, weird.
David-W-Fenton
2010-02-04 00:45:58
@David-W-Fenton: I understand what you're saying, but I guess I'm going to continue to maintain an I'll-believe-it-when-I-see-it attitude. I initially got burned assuming the 64-bit version of SQL server could continue to query Jet databases (hey, as you say, MS promises backward compatibility, right?), so next time I'm going to be more cautious. I appreciate your perspective, though! I just don't trust MS to do what I would want them to do: every business is constrained in its decisions no matter what it would sometimes like to do.
Emtucifor
2010-02-04 01:54:58