Solved: Access front end SQL backend recordsets

Hey,


I am in the process of doing some testing to try and move forward on implementing an upscale of our product from a single accdr (not split). To an access front end and SQL 2008 express back end.

However i have some problems as there is alot of custom code utilizing record sets.

[VBA]Dim myrs as dao.recordset
set myrs = currentdb().openrecords("Select * FROM patient")[/VBA]

What is going to be the best way to do something along these lines now that i have a split SQL back end ?

Any help is always appreciated

Well it looks like i will have to go through the whole system and convert it to using ADO instead of DAO.

Quote:

---

Originally Posted by Movian

Hey,


I am in the process of doing some testing to try and move forward on implementing an upscale of our product from a single accdr (not split). To an access front end and SQL 2008 express back end.

However i have some problems as there is alot of custom code utilizing record sets.

[vba]Dim myrs as dao.recordset
set myrs = currentdb().openrecords("Select * FROM patient")[/vba]
What is going to be the best way to do something along these lines now that i have a split SQL back end ?

Any help is always appreciated

---

DAO will work with an SQl Server back end.

Try:
[vba]Dim myrs as dao.recordset
set myrs = currentdb().openrecords("Select * FROM patient", dbOpenDynaset)[/vba]

See: VBA Traps: Working with Recordsets

While you can use the method above to reference the recordset, you're missing out on one of the huge benefits of working with SQL as a backend.

I'd recommend you start evaluating which of these procedures would be better off written as a stored procedure. Depending on what you're doing with the recordset, you can get a dramatic improvement on performance letting SQL handle your data at the server end.

When using DAO with the additional parameter you specified i get the error

Runtime error 91

Object Variable or with block variable not set

[vba]Dim myrs As DAO.Recordset
Set myrs = CurrentDb().OpenRecordset("Settings", dbOpenDynaset)[/vba]
I will look into using stored procedures, however this is a LARGE DB (90 mb without only default table information.)

This is going to be a long process, however im sure it will be worth it in the end. Also i have one other question,

I have the new adp file on my machine connected to a DB. How would i go about setting it up so that i can deliver it to a client so that they can connect it to a different SQL express DB ?


~Ok having some issues with a stored procedure conversion.
[VBA]
SELECT tblLookupValues.Value, tblLookupValues.Order
FROM tblLookupValues
WHERE (((tblLookupValues.Form1)=ActiveControlParentFormName()) AND ((tblLookupValues.Control)=ActiveControlName()))
ORDER BY tblLookupValues.Order;[/VBA]

its complaining that it doesn't know the function activecontrolparentformname()

Which of course the server will not as thats an internet access function. How would i get around this.

I use this to setup user definable dropdown lists. for the whole system based on form name and control name.

As im not too comfortable with SQL yet am having some minor issues (il get through them). Any help in the mean time is appreciated.

You need to pass the value of activecontrolparentformname() as a parameter to the stored query.

I'd also recommend that you check out a book called "Microsoft Access Developers' Guide to SQL Server" - I found this invaluable when I was trying to wrap my head around the differences in SQL coming from Access.