chaverly
asked on
MS SQL Sever to MS Access Front end linking of editable queries
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Maybe this will help
http://www.sqlservercentral.com/Forums/Topic372351-131-1.aspx#bm373453
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.
ASKER
Number, thanks for the response. Let me try this out and get back to you!! Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!