Data Access Layer

[chilluk7]

Has anyone got any decent samples of a Data Access layer (DAL) created in Access VBA for communicating with a SQL db?

I have been doing a lot of .Net / C# dev and want to apply the same kind of modelling to a new Access project I am doing.

I have often written classes in my VBA code to model the objects / tables but they have had specific methods to pull data.

I guess I am looking for some multi purpose / vanilla code to let me call SQL Stored Procedures so I can throw it the procedure name and a bunch of params, kind of abstracting the communication with the server.

Rather than reinventing the wheel I felt sure someone would have done something similar?

Thanks.

 

[isladogs]

The fact that you haven't had a reply despite 40 or so views suggests you may have to do this yourself. If nothing else, this reply may give the thread a 'bump'.

If I remember correctly from many years ago, a DAL is used with Access Data Projects (ADP) which were deprecated with Access 2013. You can use existing ADPs up to A2010 but to create new ADPs you need e.g Access 2003.

I've found what looks like an extremely thorough article on the use of ADPs and DAL at http://www.joakimdalby.dk/HTM/ADPX.htm
I haven't read it all and, if you have questions, I suggest you contact the author direct.

However before you go any further with this, there are other methods of connecting Access with SQL tables that don't require the use of DAL. I prefer to use DSN less connection strings. It works with both old and current versions of Access and SQL Server

 

[Mark_]

A long time ago (90's) I had a rather lengthy conversation with another programmer about getting stored procedures to fire in SQL without the front end being able to directly access the stored procedures.

His solution worked very well in his situation;
He had a table with procedure name and "parameters".
Front end entered a record into the table then the back end did the work and deleted the entry (In reality marked processed as he had it set up for logging to know who was doing what when).

Not a direct answer but is this an approach that could solve your issue?

 

[Frothingslosh]

I believe ADODB connections can send parameters directly to stored procedures, but I'm not 100% sure. I'm afraid ADODB is something I've virtually never had need to touch. That would be a solid starting point, however.

 

[Galaxiom]

I believe ADODB connections can send parameters directly to stored procedures, but I'm not 100% sure. I'm afraid ADODB is something I've virtually never had need to touch. That would be a solid starting point, however.

Yes. I've done that. The command can return a recordset.

I have used this technique to display data from a huge table in SQL server on a subform in Access. The performance is vastly faster than using linked tables and LinkFields on the subform control.