?
Solved

MS SQL Sever to MS Access Front end linking of editable queries

Posted on 2012-09-04
7
Medium Priority
?
539 Views
Last Modified: 2012-09-12
I am trying to create a link between MS SQL Server (2005) and Access (2010) with the use of editable queries. I have researched how to use pass-through queries and stored procedures with parameters, however they both return an error that does not allow me to edit the data-set that was returned.

Is there any way to populate a data-set that is updateable in an Access front end with a SQL Back end while using parameters? If so, what is the best way to do it? Is there an example?

Than you!
0
Comment
Question by:chaverly
  • 3
  • 2
  • 2
7 Comments
 
LVL 30

Accepted Solution

by:
Randy Downs earned 668 total points
ID: 38364655
Maybe this will help

http://stackoverflow.com/questions/7574219/access-as-the-front-end-and-sql-server-as-the-backend


      

You will probably want to start with an empty Access database (since the table structures and any existing forms and reports will not match what you created in SQL server.

First step is to establish an ODBC connection to your SQL Server database. Then you will "link" the tables in SQL Server to your Access database.

Now, you have an Access database with all the tables that you linked from SQL Server. Those tables still "live" in SQL Server and when you edit them in Access the data will be stored in SQL Server.

You can then build Access forms and reports using these tables just as if the tables were native to Access.
0
 

Author Comment

by:chaverly
ID: 38364711
Thanks for the reply.

I already have a ODBC connection set up with the linked tables. I also have the ODBC connection set up in the pass through and am able to establish it with Access VBA. However, they all still return an un-updatable dataset.

Perhaps I will provide more information. The back-end is in a server at our office and several individuals will be working from home. However - they need to be able to access the database. The linked tables, as the exist, return the values and makes the front end extremely slow. Thus why I am trying to establish a way for SQL to process the queries and filters on the Server, and only transmit records that the user needs. Otherwise, we re talking about A LOT of data being thrown all over the place - which slows things down.

I would use a view, however I would need to transmit the User ID as a parameter.

Hopefully that information helps.

Thanks again!
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1332 total points
ID: 38364818
In many cases you need a TimeStamp column in the SQL Server table to work with that table in Access (as well as a Primary Key index as well).

Note that working with Access across VPN connections is known to be notoriously slow and unreliable. Using SQL Server as the datastore and taking care to avoid large recordsets can help, but you're trying to do things that aren't really feasible in most cases. If you're working with standard bound forms, you are almost certainly barking up the wrong tree.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chaverly
ID: 38364948
Thanks for the response LSM.

I am aware that Access and VPN are not best of friends. Unfortunatly, this is the result of budget constraints; and thus the requirements are that I utilize a Access front end with a SQL Sever over a VPN; trust me - not my choice.

Also, most of my forms, at least the ones with the larger record sizes, are unbound. I will look into where I can throw in some additional Indexes, however I think I have most of them.

Are we saying that this is not possible? So what would be some solutions or work arounds?
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 38365058
Maybe this will help

http://www.sqlservercentral.com/Forums/Topic372351-131-1.aspx#bm373453

was opening the recordset I was setting it as dbOpenDynaset.  I tried dbPessimistic and dbOptimistic and it seems to have worked.
0
 

Author Comment

by:chaverly
ID: 38365088
Number, thanks for the response. Let me try this out and get back to you!! Thanks!
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1332 total points
ID: 38365671
Are you using ONLY SQL Server as the datastore? If so, then you may be okay with this.

If you're using a mix of Access and SQL Server links then all bets are off.

Proper indexing is very important to good performance, but it's not the only thing.

dbOpenDynaset is a Cursor type. dbPessimistic and dbOptimistic are Lock types, so I'm not sure what the earlier post means. You can't define those on your pass through queries anyway (you just define the connection).
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question