SQL Server linked tables (Access 2003 xp SS 2005)

Topic

New Reply

Hi everybody:

I’m planning an upgrade of a complex, 2-tier Access database application to SQL Server 2005 with the existing .mdb client, and have questions as to the best strategy to use for the SQL Server tables. In addition to my custom Access tables, my app back-end currently imports 2 tables, one quite large (over 300000 records), from a SQL Server commercial software, and does various updates to the custom tables from them, in addition to using them in many queries for forms and reports in the client. I have been importing those tables so as to be able to establish PK/FK relationships in the back-end to expedite query performance. Once I move the Access back-end tables to SQL Server, would it be better to just link them to the original tables already on the SQL Server (and if so, how would this best be done), or would this impact performance? It seems wasteful to have two copies of the same tables on the SQL Server. If instead of importing copies the original tables were to be linked in from the SQL Server, would it then be best to link directly to them from the Access.mdb client, or would this also impact performance? I have not been able to find information on how SQL Server treats distributed queries with joins to local tables.

Please advise.

Thank you, in advance, for your help.

Reply | Quote

Replies

Once you’ve moved everything to the backend, you will want to have everything linked there. Use the SQL Server Management Studio to establish relationships and indexes as required.

From Access, you will want to use pass-through queries as much as possible (especially for reports), to take advantage of the SQL Server speed. I generally create a View in SQL server (to make sure my syntax is correct), then copy and paste it to my pass-through query. The important difference to using pass-through queries (aside from some syntax differences and other quirks) is that you can’t have any reference to any forms or tables local to Access. Also, pass-through queries aren’t updatable, so you can’t use them as the recordsource for a form that you want to allow user changes to the records.

Reply | Quote

Hi Mark:

Thank you for responding.

I understand about using passthrough queries and linking back-end tables to the client via ODBC. My question concerns whether performance would be faster if I import some tables from a different (commercial) database into the backend so I can establish PK/FK relationships to my custom tables, or whether I could save some space on the server by linking the back-end tables between two different SQL Server databases.

I understand that I can create synonyms to access remote database tables on SQL Server 2005 and query on them as if they were local tables in the current SQL Server database. However, I don’t see a way to select them using an ODBC DSN from the Access client, as they don’t show up as views. (I don’t see a way to save these queries as views in SSMS, and the Synonyms don’t show up in the DSN table list.) So, I was wondering if distributed queries using OLE DB would work. However, I am unsure if either approach would create a major performance hit, especially via ODBC connections to the client, since PK/FK relationships would not be possible on the tables, just in the query joins.

Do you know how SQL Server processes queries joining tables from different databases? And, do you know if one could write either regular remote Access queries or passthrough queries that use such joined tables that could be used from an Access .mdb client, and if so, how to do so and what the performance hit would be?

Reply | Quote

From Access, you can link to tables in different backend databases, so you could write an Access query that could get info from the different databases; but your performance would likely be greatly diminished.

A pass-through query from Access, however, is specific to a single SQL Server. I suppose it is possible from one SQL db to see tables in another SQL db, I just don’t know the syntax.

Reply | Quote

The syntax is easy:

First, you create a synonym:

USE localdatabasename
GO
CREATE SYNONYM nameit FOR remotedatabasename.dbo.tablename
GO

And then, you can query it:

SELECT * FROM localtable INNER JOIN nameit ON localtable.Keyfield = nameit.Keyfield;

I did a little testing and found out that to access the above from an Access client, you must write a passthrough query. The SQL Server 2005 synonym and any queries based on it are not visible in the ODBC DSN from Access. In SQL Server 2005, the synonyms are visible in the Synonyms folder. In order to save views based on them, you have to right-click the Views folder, select New View, and then select the synonyms from the synonyms tab to drop them into the query grid.

This ran really fast from my Access .mdb client. I was even able to link the pass-through query to a local .mdb table, and it still ran really fast.

Has anyone used this functionality as a basis for an application? I might give it a try . . .

Reply | Quote

I assume the creation of the SYNONYM is permanent, in which case the pass-through query will see it as just another table.

I even tried this:

USE localdatabasename
CREATE SYNONYM nameit FOR remotedatabasename.dbo.tablename
SELECT * FROM localtable INNER JOIN nameit ON localtable.Keyfield = nameit.Keyfield

And it worked! Although when I tried to run it again, it produce an error because “nameit” already existed. But I don’t know what the scope of “nameit” is in this situation.

Reply | Quote

You only have to create the synonym once. It persists unless you DROP it in the local SQL Server database. (DROP SYNONYM nameit)
It will show up in the Synonyms folder once you have created it, but only after closing and reopening SSMS for some reason.

I think one of the advantages to this technique is that you don’t have to write fully qualified names all the time, just once in the synonym creation. You can use the synonyms in code. Another is that if, say, your systems guy decided to move your target database to a different server, you could just drop the synonym and create another one that included the remote server, and it would be transparent to all your queries. (Of course, your systems guy would never do such a thing . . .)

The syntax for joining tables from different servers is:

CREATE SYNONYM nameit FOR RemoteServerName.DatabaseName.schema_name.Tablename

Reply | Quote

Yes, but that means you have to be aware of whether or not it was created, or at least be able to check for its existance before creating it.

Reply | Quote

Right. So, in code, you would use IF EXISTS etc. But you can also browse the Synonyms folder.

Reply | Quote