SQL Clone
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
RJ Smith-262986
RJ Smith-262986
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

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


Kathi Kellenberger
Kathi Kellenberger
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 342

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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
RJ Smith-262986
RJ Smith-262986
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

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


Kathi Kellenberger
Kathi Kellenberger
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 342

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
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Len Bearse
Len Bearse
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3

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.


SQLGuy64
SQLGuy64
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 292

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


Paul McMorrow
Paul McMorrow
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: 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)


WILLIAM MITCHELL
WILLIAM MITCHELL
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2124 Visits: 3061

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.


Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 2384

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
Mile Higher Than Sea Level
Mile Higher Than Sea Level
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 465
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.
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