SQLServerCentral / Discuss Content Posted by Kathi Kellenberger / Article Discussions / Article Discussions by Author / Access to SQL Server: Linking Tables / Latest Posts

RE: Access to SQL Server: Linking Tables

Mile Higher Than Sea Level — Thu, 18 Feb 2010 10:59:43 GMT

http://www.access-programmers.co.uk/forums/showthread.php?t=182076Welcome to the world of Access clients that use SQL Server back-ends.While I did not transfer my Access 2003 (run time) application from Oracle to SQL.I did recentlly update it from SQL 2005 to SQL 2008This is one of a few posts.The ODBC did require some tweaks since there is a client update from 2005 to 2008 SQL.Also, during my conversion, it appeard (but I can't prove it) that MS pushed out a change that added some compatibility.Bottom line - most ODBC solutions for Access are on the site above.This, however is the best site for SQL Server.

RE: Access to SQL Server: Linking Tables

sqlsinger — Thu, 18 Feb 2010 09:10:01 GMT

I have an Access database that previously had linked tables to Oracle. With our Oracle retirement, these tables have to be relinked to the same tables that have been migrated to SQL Server 2008. A file DSN with SQL Server login and password is provided to users for this purpose (same login and password as Oracle), and works.... once. When they close and reopen Access and try to open a table, they get an ODBC error because they don't have access via Trusted Connection. The sql login and password information evaporates.If I uncheck Trusted Connection and type in the sql login and password, it works until the next time Access is opened.How do I get the login information to stick?Thanks,Lee Anne

RE: Access to SQL Server: Linking Tables

Mile Higher Than Sea Level — Tue, 27 Oct 2009 16:51:45 GMT

Access 2003 Programmer Upgrading linked tables from SQL 2005 to SQL 2008I posted this set of very helpful information on the Access site.http://www.access-programmers.co.uk/forums/showthread.php?t=182076It would have been usefule to me had it been available.

RE: Access to SQL Server: Linking Tables

Charles Kincaid — Fri, 17 Nov 2006 10:05:00 GMT

I've done a couple of projects where we upgraded Access to link to SQL server. I would not do it again.

With .Net click once deployment I would just code the front end in your favorite (C#, VB, whatever) and query SQL server directly. I don't use DSN's any more either. We have a parameterized connection class that works like a charm.

Reporting? Crystal is built into .Net these days. There are some good thrid party tools as well. My clients love that they can preview a report and then exort to PDF. They then include that in an e-mail. We have one app that automatically prepares PDF reports for client customers and e-mails to the customer and account manager at the same time. The fact that I can, and do, write services in VB.Net makes this a snap.

They fact that I can kiss Repare/Compact on a weekly basis goodbye is enoug for me not to look back.

RE: Access to SQL Server: Linking Tables

WILLIAM MITCHELL — Fri, 17 Nov 2006 09:26:00 GMT

Personally I use DAO within Access because you can manipulate schema, data, and Access objects within the same environment. I may use linked tables or pass-thru queries, depending on whether SQL or Access is more efficient at a particular task.

BTW nobody mentioned a design change that is required if you're using Autonumbers in an Access app - because Access returns the new ID right away, but SQL Identity values don't exist until AFTER the record is saved. There are lots of Access apps that rely on that "right away" behavior.

RE: Access to SQL Server: Linking Tables

Paul McMorrow — Fri, 17 Nov 2006 09:00:00 GMT

I've been using ADP's for years now. The performance is great.

I work on my .adp file, and then I create and distribute to users their own .ade file.

You can get fancy and have the .ade file check and see if it's the latest version (and if not, it will copy the most current .ade file down to their local machine)

RE: Access to SQL Server: Linking Tables

Andy Brons — Fri, 17 Nov 2006 07:50:00 GMT

I have done a lot of access programming; and the method I like best is using .mdb files as front ends and writing my own adodb connection object and then a function that returns an adodb recordset. I use the recordset to fill list boxes, combo boxes and tables that are bound to forms. This is a totally DSNless way to program and you can hit as many different database as you like. My connection and ado logic is in a seperate module or class. Below are the stubs.

It can be time consuming scripting out the inserts to the form bound tables. However, you can see everything in the debugger and you can utilize record sets in nested loops for some powerfull data crunching when set based logic is not an answer.

Public Function Connection(ConType As Integer) As ADODB.Connection conType is used in a select case so the Connection function works for more that one databaseEnd Function

Public Function QueryReturn(strSelect As String, intCon As Integer) As ADODB.Recordset

End Function

Public Sub Command(Command As String, intCon As Integer)

End Sub

Public Function CheckRecordSet(rs As ADODB.Recordset) As Boolean

End Function

RE: Access to SQL Server: Linking Tables

Len Bearse — Tue, 28 Feb 2006 10:38:00 GMT

I have worked with Access 97 and 2000 over the last few years and found that Access Linked table to SQL is fast approach to solving business needs especially when Scope is not well defined. The Linking table approach is the basic method but will bite you if your SQL table structure changes or your office version upgrades. Deployment and modifications are tricky to manage. You can reference SQL stored proc in Pass-Through queries and really have a robust method of accessing data to your Access front-end without compromising resources on your SQL Server. I also recommend a well defined connection string to SQL so you can trace calls from the Access front-end.

In a nut shell I would use linked table in Develpment only to get forms running and convert to Stored Procs for production. The development cycle is short for Access compared to .net and c# interfaces. But Access is a memory hog on your client so be careful with long term planning.

I'm looking forward to article 3 with access project. I have not had time to explore this method and from what I have read seems to be the answer.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Thu, 23 Feb 2006 08:38:00 GMT

Sorry, I'm making assumptions that you are using linked forms cause that is where I usually hear about locking problems with linked tables.

RE: Access to SQL Server: Linking Tables

RJ Smith-262986 — Thu, 23 Feb 2006 08:16:00 GMT

Kathi-

Was your post above a reply to my post?

*In my post, I am not referring to linking tables or recordsets to forms. I am trying to process data from disparate sources.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Thu, 23 Feb 2006 08:10:00 GMT

I have done this with linked tables. What I have found is that you SHOULD NOT just link the tables to a form and let the user scroll from row to row.

You should only allow a form to link to one row at a time. In the apps that I created (about 5 years ago!) I always had a form that allowed the user to choose the row and then filter the form. That way you are not locking up rows and you are not pulling the whole table from SQL Server.

RE: Access to SQL Server: Linking Tables

RJ Smith-262986 — Thu, 23 Feb 2006 08:00:00 GMT

What is the best way to set up a recordset from Access that needs to join Access tables with tables residing in SQL? Currently we have found the only way that works for us is under the umbrella of CurrentProject and utilizing linked tables. I believe we are having locking issues because of the methods we are using, so I am looking to move away from the linked tables.

RE: Access to SQL Server: Linking Tables

Barrie Gray — Thu, 23 Feb 2006 06:46:00 GMT

I have been using Access Data Projects for several years now. IMHO, it is about the fastest and most efficient method of doing client-server SQL Server applications that can scale to literally hundreds of concurrent users (yes using Access as the client, but just for the GUI and the excellent report writer of course). I would not consider using ODBC DSNs etc. as this article suggests. I am surprised that Microsoft and the development community generally has missed the boat on this marvellous technology combination. My apps are lightening fast and the users love their responsiveness, performance and functionality.

It is truly a "rich user experience" that everyone wants in their systems. I even have one application - no bound controls, all forms and controls populated in ADO code using stored procedures, of course, as the back-end retrieval mechanism - that was able to run 10 concurrent users quite satifactorily over a 56Kbps communication line between the East and West coasts. None of our other Corporate applications (except for web applications of course) would work over that line. Anyone who wants to use Access as the GUI for the ease of development and the best-of-breed report writer that everyone is trying to emulate lately, should check out ADP technology. It is that, or use VS 2005 and .Net 2, in terms of speed of development and developer-friendly technology that results in efficient, scalable apps and a "rich user experience".

RE: Access to SQL Server: Linking Tables

Scott Hutchinson — Fri, 18 Mar 2005 12:16:00 GMT

I bet a nickel that the upper limit is 32,767 tables (maximum value of an Integer data type).

RE: Access to SQL Server: Linking Tables

Dustin Bishop-213230 — Wed, 09 Mar 2005 13:10:00 GMT

Thanks for your response. I think I figured out the problem. It appears that Access can't populate the list when the SQL db has 33,000 tables. I did succesfully test it on a copy of the db that was reduced to 27,500 tables. I would guess the max Access can handle lies between those two numbers. Thanks again for your assistance.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Wed, 09 Mar 2005 09:43:00 GMT

I have never seen this problem unless it was a rights issue. If you are using the same ODBC connection, then that is not the case. Not many people will see your question in this particular forum. If you haven't already, post the question in the Access forum. I think you'll be forgiven for cross-posting in this case.

RE: Access to SQL Server: Linking Tables

Dustin Bishop-213230 — Wed, 09 Mar 2005 09:38:00 GMT

This is a great topic. I am seeing some strange behavior when trying to link to certain SQL Server DBs. I have two databases on different servers. When trying to link tables to Access Xp (or 2000) only one table shows in the list. This table happens to be the first table owned by dbo in alphabetical order. Both SQL Servers have other databases that are not giving me any trouble. If I open a project using the same odbc connection all the tables are visible. I would prefer not to use a project at this time but can find no reference to this type of problem. Any suggestions would be great.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Thu, 24 Feb 2005 09:00:00 GMT

I knew writing Access articles on a SQL Server site was going to be interesting. The only comment so far about my Access Project article is that they can only be used by one user at a time. Have any of you Access Project fans run into problems with this? My thought is that you give each user a copy of the ADP file.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Thu, 24 Feb 2005 08:54:00 GMT

As far as updating data, I am equally comfortable using either ADO or DAO. If ADO is more efficient, that is what should be used. But when manipulating objects inside Access, DAO is a perfect fit when using the traditional MDB applications.

I am so impressed with the Access Data Projects, that any future Access/SQL work I do will be with this technology.

RE: Access to SQL Server: Linking Tables

David.Poole — Thu, 24 Feb 2005 03:17:00 GMT

I found what I considered to be a bug in ADOX and therefore stopped using it. The exact details as far as I remember were as follows.As far as ADOX is concerned a SELECT query with no parameters was considered to be a VIEW. An action query or a SELECT with parameters was considered to be a PROCEDURE.I was using ADOX to try and modify a QueryDef. The query should have been a view but the view collection said it didn't exist. Great, the Proc collection said it contained the query, but when I tried to edit it and save it I got a message basically saying that although it was in the proc collection it was a view.I found DAO to be more stable and faster and so gave up on ADO within Access.On the subject of linked tables, the problem is that you have to be very careful how much data you bind to your controls. Access gives all encompassing functionality to bound data and it is this that causes the locking problems.The idea of limiting what is brought back from SQL to display on a form is basic good practice.One of the problems I had when I first switched to VB was how to get the functionality that was standard in Access within my VB app. It wasn't long before I worked out that a completely different design approach was needed and that a VB front end to an Access database allowed far greater concurrency than an Access front end to an Access data file.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Wed, 23 Feb 2005 11:43:00 GMT

I decided that maybe I should check my idea of only allowing one record at a time to be attached to a form to make sure that it is really not pulling the whole table and that is the case. Opening the linked tables and attaching a form to a linked table is bad. Filtering, not so bad. I used Profiler to check.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Wed, 23 Feb 2005 11:19:00 GMT

I agree, the Access Projects are the way to go.

Back when I was doing developement in Access 2000, if I recall correctly, there was no way to import other types of data, like text files, using Projects. That functionality was important for several projects I was working on, so I stayed with the traditional approach. But, the functionality is there in Access 2003. So, if I had a new application to write, I would consider using Access Projects.

As far as traditional linking to SQL, I suggest that you keep the user from ever seeing the linked tables. I also always designed the app so that the user would only have one record attached to a form at a time. That worked very well.

RE: Access to SQL Server: Linking Tables

Steve Rosenbach — Wed, 23 Feb 2005 11:12:00 GMT

I strongly agree with NoelD and Kieth Henry - Access Data Projects (.adp) is the way to go for a number of reasons.

One of them is that each linked table uses a connection to SQL Server - with an .adp, you're generally using only two or so connections regardless of how many tables are in the database.

I refer to Access Database Projects as "Access on Steroids." As a longtime Access developer (I started in early 1993 with Access 1.0) and a big proponent of Access, I am convinced that using linked tables and DAO with SQL Server is a bad idea.

Stay with DAO for applications that will never migrate from JET - but for SQL Server front ends - go with ADPs and OLE DB.

Best regards,SteveR

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Wed, 23 Feb 2005 11:06:00 GMT

I have written several successful applications that did update SQL data using linked tables using Access queries, DAO or ADO code. The last big project I worked on I actually translated all of the DAO to ADO using Access 2000/SQL 2000. I think that like any programming environment, things can be done correctly or incorrectly, with good performance or bad depending on the tecniques used. I did primarily Access/SQL programming for about 5 years so I learned a lot about making it work.

Tomorrow there will be an article about Access Projects. I think that is probably the way to go if you want an Access GUI to your SQL apps.

RE: Access to SQL Server: Linking Tables

Keith Henry — Wed, 23 Feb 2005 10:10:00 GMT

As a rule 99% of the time you should never use linked tables from Access to SQL. It's just a very good way to snarl up your SQL server.

I always advise using Access projects instead, they give you the easy forms and macros, but with SQL as the backend (rather than Jet accessing SQL)

RE: Access to SQL Server: Linking Tables

Jags2001 — Wed, 23 Feb 2005 10:06:00 GMT

Luis and Lee's problems are caused by linked tables -- because you can not control the locking of linked tables (which by default will give you all the contents of th whole table). The table is locked while the linked table is open. (correct me if am wrong)

For an access app, I would suggest not linking but querying the SQL Server table (if possible filtering the rows with a select) using ADO as a recordset then release the recordset as soon as possible to reduce locking- which is handled by ADP.

The other minor problem is that you have to relink the table if there is a table change like adding or deleting a table.

RE: Access to SQL Server: Linking Tables

Kathi Kellenberger — Wed, 23 Feb 2005 08:39:00 GMT

While working on this article, I found that Access 2003 had references to DAO and ADO by default. I haven't done a lot with Access for the last 2 years and don't have any inside information on Microsoft's plans. I tried using ADOX to manipulate objects, but it always seemed much more difficult, it was always missing something. I'll have to see if I can come up with the same examples using ADO code.

RE: Access to SQL Server: Linking Tables

noeld — Wed, 23 Feb 2005 07:07:00 GMT

Just to add my $0.02 ADP (Access Data Projects) are the ones that in my opinion should be used if you plan to use SQL Server on the Back end and MSACCESS on the front end.

It can handle sp, functions, views, Transaction Processing and by default uses ADO.

HTH

RE: Access to SQL Server: Linking Tables

LeeFAR — Wed, 23 Feb 2005 06:59:00 GMT

I want to share a problem we have recently encountered. We have an Access application that is connecting to SQL Server using ODBC just as this article shows. This application has been working this way for more than two years. The SQL Server this app’s database is on also hosts a number of other production databases.

About six months ago, we began to see a problem with our server. Out of the blue, the server would become very unresponsive. All applications will have very slow query execution times. Looking at the resources of the server, the only thing that would stand out is an excessive I/O utilization. The only “cure” to the problem was to fail the server over (it is a cluster server) to the other node and things would return to normal for a while. This problem had no pattern and could not be predicated. After about three months of our own investigation, we had to bring Microsoft into the picture. They have spent around three months pouring over trace logs and all kinds of information. They finally pointed out the little Access application I mentioned above. They showed concern with this application because it was showing extensive “transaction” times on tables. They told us to investigate this application. After six months of having our production server go down at least once a week, management decided to move this application to a server by itself to see if the problem on our production server would go away. We have been running over three weeks now without a problem on our production server.

We haven’t really dug into the application in question, but I do know this is an Access front end that is linked to SQL tables. And I know the application’s forms are bound to the tables. And I know that when you open one of the forms, you will see an “open transaction” to the table. As long as the form is open, the transaction will remain open. We at this point suspect this kind of behavior is indicative to Access linked tables and nothing really can be done about it, short of rewriting the application.

I do want to point out the Access application is written in Access 97 (…yes I know). It is using MDAC 2.8 ODBC to connect to a SQL Server 2000 server. It is connecting using the TCP/IP protocol. There are about 15 continuous users of the application.

If anyone out there has ever seen or heard of problems like this, I would love to hear from you.

RE: Access to SQL Server: Linking Tables

Luis Fernando Robledano Esteban — Wed, 23 Feb 2005 04:47:00 GMT

I've been around Linked tables, but found -testing and forums- a great problem when using.1. Mainly, DBs has to be updated2. Mainly, DBs operations has to be protected in transactions3. Passthrough queries are needed for updating linked tables4. Passthrough queries cannot be (properly) used in transactions=> Mainly you cannot use linked tables !?!?! AmazingEven if you dont want to use transactions, updating (inserting new records or updating existing) seems to need changing the code of existing applications.I've been many times sugested to rebuilt the application just to work directly to SQL-server (by the way, using ADO instead of DAO).

RE: Access to SQL Server: Linking Tables

David.Poole — Wed, 23 Feb 2005 01:46:00 GMT

I notice that you are using DAO.I last touched MS Access 6 years ago and at the time Microsoft were pushing ADOX as the way to manipulate MS Access objects and were actively trying to kill DAO. Is this still the case or is it that DAO works and if it ain't broke don't fix it?

Access to SQL Server: Linking Tables

Kathi Kellenberger — Thu, 10 Feb 2005 10:58:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlserverlinkingtables.asp