Running action qry from Access

Question

Post reply

Running action qry from Access

Os-984017

SSC Enthusiast

Points: 184
More actions
August 19, 2010 at 11:25 am

#222761

Hi
we are testing an application that is written in Access to run it in SS since we are thinking to migrate to SS.
We are using a test box (SS exp ed). We exported all the tables into SS, then linked them back into Access so we can run queries/macros, etc. Select queries run fine, but we cannot run any delete/update/append qry using ODBC. Which's make sense since the tables are only linked. How can we run this application from Access? We are trying to avoid rewriting it in SS? I'm thinking it might be done via ADO, but I'm not familiar with that.
Any help is greatly appreciated.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 1

You could write TSQL stored procedures on your SQL Server machine, and call them using pass-through queries in Access.

That would involve writing the SPs though.

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 2

Exactly what is the error you get when running the queries?

If you missed creating PKs on SQL Server or did not verify them during the linking process, you will get errors running the queries.

If you have PKs and they are visible when you look at the table schema in Access then there may be another issue. In that case the error message would definately help.

Os-984017 SSC Enthusiast Points: 184 More actions · Answer 3

Thanks Simon.

I thought you can modify a table residing in SS and linked to Access by running an action qry from Access... I just don't know how to do it exactly. The idea is to avoid re-write the application.

Thanks.

Os-984017 (8/19/2010)
Hi
we are testing an application that is written in Access to run it in SS since we are thinking to migrate to SS.
We are using a test box (SS exp ed). We exported all the tables into SS, then linked them back into Access so we can run queries/macros, etc. Select queries run fine, but we cannot run any delete/update/append qry using ODBC. Which's make sense since the tables are only linked. How can we run this application from Access? We are trying to avoid rewriting it in SS? I'm thinking it might be done via ADO, but I'm not familiar with that.
Any help is greatly appreciated.
Thanks.

Os-984017 SSC Enthusiast Points: 184 More actions · Answer 4

Thanks Jerry. No we didn't specify any PK, may that what caused the issue?

We will re-link the tables and select PKs and test it again.

Thanks.

jerry-621596 (8/19/2010)
Exactly what is the error you get when running the queries?
If you missed creating PKs on SQL Server or did not verify them during the linking process, you will get errors running the queries.
If you have PKs and they are visible when you look at the table schema in Access then there may be another issue. In that case the error message would definately help.

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 5

Jerry's right - Acess needs the PK...

Coding SPs from Sql Server is how we do it here, but it does take time to do the coding.

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 6

Oh yeah, that is most likely the issue.

It is a pain to select the PK for every table when linking in Access. I would strongly suggest you create the PKs in SQL Server and re-link. Access will automatically register existing PKs and not make you select them.

Much simpler.