Re: DAO problems, and client server RDBMSs

Programming wrote:
>
> Does anyone out there know how to get
> DAOs (used with Access and Visual Basic)
> to work correctly with a minimum of coding?
>
> When I work in Oracle, I can do what I
> want to do in only two statements!!!
>
> update/insert/delete ...
> commit
>
> If I have a screen in Foxpro, I have a number
> of memory variables. When I enter the screen,
> it is
> scatter memvar
> After any changes, I use the command:
> gather memver
> which writes everything to the database.
>
> Again, two commands. (However, in Foxpro, you do have to be
> aware if you delete the last record, BOF, and EOF, and
> put some if statements in.)
>
> However, I am finding a LOT of problems with DAOs.
>
> 1)
> If you just open a recordset, the count is always 1,
> even though the recordset can have hundreds of records.
> You have to put other lines of code in order to move the
> pointer around, and back again, before the count will
> work right.

R: I believe that, if you open a Table recrodset you get the real number of records.

>
> 2)
> With DAOs, standard SQL queries must be made into an object first.
> So even a simple count requires a number of lines of coding.
> In Foxpro, or Oracle, you just do the query. It's very simple.

R: Once you have opened the database you can execute queries like this:

	Dim Db AS Database
	Dim Sp As Recordset

	Set Db = Workspaces(0).DbEngine.OpenDatabase("MYDATABASE")
	Db.Execute "Delete From MYTABLE Where Code <> 100"
	Set Rs = Db.CreateRecordset("MYTABLE",dbOpenTable")

	It's not so much code...

>
> 3)
> If you use the update command, then the pointer
> is actually moved to another record!
> Therefore, if you want to still be positioned on the
> same record, then you have to use searching techniques
> to find the same record again.

R: Rs.Update

   Rs.BookMark = RS.LastModified

        Again, not so much code...
>
> Delete:
>
> - if you delete the last record in a set, move next doesn't
> work. So, you need an if statement, and a movelast.
> - the problem is, if you delete the very last record, so that
> there are no more records. In this case, you can't use
> movelast. You have to use refresh. And another if statement.
> - another problem is, you can't rely on the recordcount,
> because it's not reliable! So, you need to have some more
> lines of code to check for things that really shouldn't
> need to be checked.
>
> There are other problems too, but they are not fresh
> in my mind right now.
>
> At first I thought that Visual Basic might make a good
> front end for a client server application.
> However, having worked with it, I am having second
> thoughts.
>
> My question is, does anyone use Visual Basic, or Access,
> to work with a REAL RDBMS system such as Oracle?
> What has your experience been?
>
> Rodger Lepinsky
> ADP Systems Partnership
> Winnipeg, Manitoba, Canada
>
> Opinions are my own, not necessarily my employer's.
 

-- 
Cláudio Avolio Rodrigues
   Systems Engineer
    VARIG AIRLINES

Get PR-Tracker -- tracks problem reports, defects, bugs
INFORMATION:  http://www.prtracker.com/info.html
DOWNLOAD:     http://www.prtracker.com/download.html