1

Re: Link to Access database broken after move to SQL Server 2005

Not so much a bug as a warning. One of the side-effects of swapping to SQL Server 2005 after a Recorder 6 install is that the link to the Access 97 linked database is broken (as I've just realised). The link remains to the original MSDE database. I guess one could manually set up an ODBC link from a new Access DB to the SQL Server 2005 nbndata, though I haven't tried this.

SQL Server Management Studio has a visual Query Designer similar to that in Access, so to some extent this makes the Access linked database redundant. The SQL Server Query Designer does have some limitations compard to the Access one, however, and the version SQL of course differs in some ways.

Alan

Alan Hale
Aberystwyth

2

Re: Link to Access database broken after move to SQL Server 2005

Yes, as you surmise it's easy enough to setup an ODBC or OLEDB link (I use the Jet OLEDB driver) to the new SQL database and link the tables. If you have Access 2003, you can setup what MS call a "project" (which has an .adp extension, rather than .mdb) which can link directly to the SQL Server and perform queries on it and generally work with in a more direct, efficient manner. You're also able to take advantage of UDFs and stored procs when using an Access project.

There are indeed quite a few differences in SQL. Access uses an odd combination of SQL, VB and Excel like functions for writing queries, whereas SQL Server has the much more consistent and much more powerful Transact-SQL. However, much of what is doable in T-SQL can also be done using a combination of Access's JET SQL and Visual Basic modules, but of course, that wouldn't be as fast as doing it directly in T-SQL.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

3

Re: Link to Access database broken after move to SQL Server 2005

My inclination IS to use SQL Server Studio Management Tools and T-SQL for queries, and forget about the Access linked database. Now I've found out how to export the query results to Excel (albetit indirectly via a csv file), my only quibble with the SSSMT Query Designer is that if you switch to SQL view you can't get back to the query in Design view, which is annoying.

Alan

Alan Hale
Aberystwyth