Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access to SQL Server: Linking Tables


Access to SQL Server: Linking Tables

Author
Message
Kathi Kellenberger
Kathi Kellenberger
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 341

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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Kathi Kellenberger
Kathi Kellenberger
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 341

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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
David.Poole
David.Poole
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4369 Visits: 3174
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
Kathi Kellenberger
Kathi Kellenberger
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 341

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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Kathi Kellenberger
Kathi Kellenberger
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 341
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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Dustin Bishop-213230
Dustin Bishop-213230
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Kathi Kellenberger
Kathi Kellenberger
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 341
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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Dustin Bishop-213230
Dustin Bishop-213230
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Scott Hutchinson
Scott Hutchinson
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 2

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





Barrie Gray
Barrie Gray
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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".


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search