Data Access Layer (1 Viewer)

chilluk7

New member
Local time
Today, 16:04
Joined
Jan 26, 2017
Messages
3
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

MVP / VIP
Local time
Today, 16:04
Joined
Jan 14, 2017
Messages
18,186
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_

Longboard on the internet
Local time
Today, 09:04
Joined
Sep 12, 2017
Messages
2,111
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

Premier Pale Stale Ale
Local time
Today, 12:04
Joined
Oct 17, 2012
Messages
3,276
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

Super Moderator
Staff member
Local time
Tomorrow, 03:04
Joined
Jan 20, 2009
Messages
12,849
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 19, 2002
Messages
42,990
DAL's have been in and out of fashion since at least the 70's. I personally never liked the concept because back then, the data sources were too dispirit so that if you actually changed from VSAM to IMS/DB, you would need to change all the arguments anyway.

Access natively provides a DAL for you and this is one of the most excellent features of Access. You don't need to build your own code on top of the existing Access code. Access does this by providing its own SQL dialect. You can link to tables in any database that supports ODBC and Access allows you to treat them as native Jet/ACE tables. So the same query I use against a linked ACE table will work if I delete the link to ACE and substitute a link to SQL Server or Oracle or DB2 or whatever.
 

Users who are viewing this thread

Top Bottom