March 9, 2005 at 9:38 am
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.
March 9, 2005 at 9:43 am
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 Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
March 9, 2005 at 1:10 pm
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.
March 18, 2005 at 12:16 pm
I bet a nickel that the upper limit is 32,767 tables (maximum value of an Integer data type).
February 23, 2006 at 6:46 am
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".
February 23, 2006 at 8:00 am
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.
RJ
February 23, 2006 at 8:10 am
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.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 23, 2006 at 8:16 am
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.
RJ
February 23, 2006 at 8:38 am
Sorry, I'm making assumptions that you are using linked forms cause that is where I usually hear about locking problems with linked tables.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 28, 2006 at 10:38 am
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.
November 17, 2006 at 7:50 am
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 database
End 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
November 17, 2006 at 9:00 am
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)
November 17, 2006 at 9:26 am
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.
November 17, 2006 at 10:05 am
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.
ATBCharles Kincaid
October 27, 2009 at 4:51 pm
Access 2003 Programmer
Upgrading linked tables from SQL 2005 to SQL 2008
I posted this set of very helpful information on the Access site.
http://www.access-programmers.co.uk/forums/showthread.php?t=182076
It would have been usefule to me had it been available.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply