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 Thursday, February 23, 2006 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 7, 2010 12:58 PM
Points: 14, Visits: 157

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

Post #260989
Posted Thursday, February 23, 2006 8:10 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: Tuesday, December 16, 2014 10:44 AM
Points: 769, Visits: 258

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
Microsoft
(Former SQL Server MVP)
Post #261007
Posted Thursday, February 23, 2006 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 7, 2010 12:58 PM
Points: 14, Visits: 157

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

Post #261013
Posted Thursday, February 23, 2006 8:38 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: Tuesday, December 16, 2014 10:44 AM
Points: 769, Visits: 258

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
Microsoft
(Former SQL Server MVP)
Post #261022
Posted Tuesday, February 28, 2006 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2008 11:54 AM
Points: 5, Visits: 2

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.

 

Post #262035
Posted Friday, November 17, 2006 7:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:40 PM
Points: 55, Visits: 239

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

Post #323829
Posted Friday, November 17, 2006 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 12, 2007 8:37 AM
Points: 1, Visits: 1

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)

Post #323885
Posted Friday, November 17, 2006 9:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:23 AM
Points: 1,522, Visits: 2,731

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.

Post #323897
Posted Friday, November 17, 2006 10:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 821, Visits: 2,028

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.

 



ATB

Charles Kincaid

Post #323920
Posted Tuesday, October 27, 2009 4:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:47 PM
Points: 132, Visits: 380
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.
Post #809661
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse