Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Access to SQL Server: Linking Tables Expand / Collapse
Author
Message
Posted Wednesday, February 23, 2005 11:19 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257

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.

 

 



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #163557
Posted Wednesday, February 23, 2005 11:43 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257

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.

 



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #163559
Posted Thursday, February 24, 2005 3:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 2,913, Visits: 1,842
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #163693
Posted Thursday, February 24, 2005 8:54 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257

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. 



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #163788
Posted Thursday, February 24, 2005 9:00 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257
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.

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #163790
Posted Wednesday, March 9, 2005 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:53 PM
Points: 4, Visits: 2
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.
Post #166610
Posted Wednesday, March 9, 2005 9:43 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257
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.

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #166613
Posted Wednesday, March 9, 2005 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:53 PM
Points: 4, Visits: 2
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.
Post #166688
Posted Friday, March 18, 2005 12:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2008 10:53 AM
Points: 292, Visits: 2

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




Post #168759
Posted Thursday, February 23, 2006 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 17, 2009 2:28 PM
Points: 1, Visits: 4

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".

Post #260950
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse