Convert t-SQL query with Cursor to Microsoft Access

Question

One of my managers created a Access database and is working on some data analysis - what if scenarios. Based on different conditions, he produces a report in Access.

He asked me to do some data manipulation, so I imported the database into SQL and wrote a routine with a cursor that'll do what he wants. I then export the results back into Access. Before I get any heat for using a cursor, this was supposed to be a one time only deal, so that was the fastest way for me to get it done.

As you'd expect, now he wants me to run it all the time and asked me to convert my routine to Access so he can just run it. Before you tell me to just use SQL, he's very set on Access and is often traveling and off line.

So, my question is: is there a "easy" way to convert a T-SQL query with a cursor into Access? It's been a long time since I worked with Access, but I suspect it'd have to be re-written in VBA. I'm thinking that maybe another solution would be to call the query from Access and run it in SQL, but I don't know if that can be done or if it'd work on my case because of him being off line (maybe install SQL express in his laptop?)

Any suggestions would be appreciated.

Thanks, Alex

Is your preference or your boss' preference to continue with a SQL Server dependency for the Access application? — HansUp
– HansUp, Commented Oct 19, 2013 at 15:44
Keeping the procedure in T-SQL could be problematic if it needs to be run against the live data in the Access database. There's supposedly a way to use OLEDB to attach an Access database as a "Linked Server" in SQL Server, but I've tried on several occasions to set that up and I've never been able to get it to actually work. :( — Gord Thompson
– Gord Thompson, Commented Oct 19, 2013 at 20:02
I used a SQL query with a cursor because I haven't worked with Access in a long time and t-SQL was an easy/quick solution for me. My boss preference is to use Access because he's often off line an familiar with Access reports. I agree with Gord Thompson, SQL just added another level of complexity. Even if I re-write the query to use a CTE or temp table, would that be something that is easily ported to Access? Does Access understand t-sql or do I need to figure out how to re-write the whole thing using VBA? — AlexFreitas
– AlexFreitas, Commented Oct 20, 2013 at 20:24
I changed the query to use SET commands with a loop instead of a cursor. Is there a way I can make that run in Microsoft Access? — AlexFreitas
– AlexFreitas, Commented Oct 26, 2013 at 14:36

AlexFreitas · Accepted Answer · 2013-10-31 15:11:41Z

This is how I got around it:

 1.Downloaded and install SQL server express in the user's machine.
 2.uploaded Access database structure and data to the local SQL.
 3.created the stored procedure that I wanted to run in the local SQL server.
 4.back in Access, deleted all the tables and recreated them as linked tables to SQL
 5.Create a form in Access with a big button that executes the stored procedure
    `Private Sub Command0_Click()
     Dim qdf As DAO.QueryDef
     Set qdf = CurrentDb.CreateQueryDef("")
     qdf.Connect = CurrentDb.TableDefs("ANY TABLE").Connect
     qdf.SQL = "EXEC dbo.[stored procedure name]"
     qdf.ReturnsRecords = False
     qdf.Execute
     Set qdf = Nothing`

The stored procedure truncates one re-populates one of the tables. So after executing it I can open up the table in Access and see the changes. My manager can continue to use Access and SQL server is used in the back-end. Happy Ending! :)

Collectives™ on Stack Overflow

Convert t-SQL query with Cursor to Microsoft Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related