Help with Building SQL for MSACCESS Data Layer

[Alex Stevens]

Hi all,

I've already written a DAL for SQL server which I'm pretty happy with.
I want to use this as the basis for a MSACCESS flavour and would appreciate
som guidance with the SQL others are using to build their MSACCESS DAL's.

As I see it, in my MSSQL DAL I have three methods for interaction with the
database.

They are:

Load() - which uses a stored procedure with a SELECT statement to populate a
datatable with a row.
Delete() - which uses a stored procedure with a DELETE SQL statement on a
command to delete a row.
Save() - which uses a stored procedure which has an If to decide whether a
UPDATE or INSERT statement is carried out.

The problem that I have is with the save method, as with the Load() and
Delete() methods I just execute SELECT AND DELETE SQL statements against the
access database.

In my MSSQL DAL when the SAVE method is called, it executes a stored
procedure which has a parameter for each field and passes in the values. If
the primary key parameter/field is populated (ie not 0) it executes a UPDATE
statement as the row already exists, and if it doesn't exist it executes a
INSERT statement.

How do I create the same in Access SQL, as the INSERT statement requries a
table in the FROM clause?
I'm not inserting from a table, I'm using parameter values!!!!

How can I execute a single SQL statement which will insert a record into an
MSACCESS table?
How should I write the UPDATE statement.

I don't want to have to create queries in the access database, I want to use
in-line SQL called using a OLEDB command??

If you need any further embelishment on any of the issues feel free to post,
and I'll promptly reply.
Any help or guidance is gratefully received!!!!!

Thanks

Alex Stevens

 

[Alex Stevens]

Cowboy,

Many thanks for that, as you have put me bang on the right course.
For reference:

I've ended up using ?'s in the Values Clause for the INSERT INTO and the =
parts of the UPDATE statement, and then appended oledbparameters to the
command, specifying the source column (in the vain hope the provider will
map them according should they be in the wrong order).

In this instance I just trying to create myself a workable Access DAL, and I
don't want to flip back and forth.

I can see many problems doing this anyway:

The sticking block is that for a SQL and Access back-end to the interface is
so different to perform the same actions that it wouldn't be viable.
The first is that they use different dotnet object SQLClient and OLEDB.

Furthermore:

To update an SQL table you would use a stored procedure and append
parameters,
To update an Access Table you would use a command string and then append
parameters (as I have done above).
Stored procedures may carry out numerous actions, which you aren't able to
do in one Access-SQL statement.

I'm open to discussion on this thought!!!!!


Thanks again!

Alex

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

Creating a generic DAL for a variety of types of databases is always a fun
endeavor. You get one finished and then find that the other database is from
another planet, so I can sympathize. In order to further ease of use for my
junior devs, I use hashtables for parameters, but it can be rather
problematic for some types of access.

To simply further, we have settled on DataSets exclusively. As of this time,
we have not needed the extra few cycles that a Reader will give, so it works
well. I also have it set up where a dev can get data in and out with 5+
lines of code (the + is for additional params).

With both SQL and Oracle, it works well, although I have to assume Oracle in
the equation or naturally null out all REF_CURSORS (if that flies a bit too
high, I was lost at first, as well). I also have to add a single char to
Oracle param starts to match the @Param format of SQL Server. Other than
that, I can (and have) switch databases with a simple config change. As we
plan to switch from Oracle to SQL Server, those are the only two DAL classes
I am overly concerned with.

Access is a real booger to work with. If I can avoid it, I avoid it. ;->

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************