ADO vs DAO

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

I would like to manipulate the data from my tablein my MDB files using query
to get data base on the criteria.

I just wonder that what are the difference between DAO or DAO to get my
recordset and which way is the best way to do it.

Your information is great appreciated,
 
Souris said:
I would like to manipulate the data from my tablein my MDB files
using query to get data base on the criteria.

I just wonder that what are the difference between DAO or DAO to get
my recordset and which way is the best way to do it.

Your information is great appreciated,
Click to expand...

First off it is generally NOT best to manipulate your data using a recordset of
any kind. It is much more efficient to execute UPDATE and INSERT queries
instead. This can be done from code or the GUI.

As to your question... DAO is generally considered superior for use against Jet
(Access) databases whereas ADO is more often recommended against other databases
like SQL Server. There are a few pros and cons to each so the BIGGEST factor to
consider is which one you are most familiar with. In the vast majority of cases
either can be used to accomplish a given task so the one you are most familiar
with is the best choice.
 
Thanks for the message,

I use the following code to open my database and record set to use ADO fro
my MDB database, but I got type mismatch.

Can you please help me where I am wrong?

Your help is great appreciated,

Dim wrkJet As Workspace
Dim dbsCurrent As Database
Dim rstMyRecord As Recordset
Dim i As Integer

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

Set dbsCurrent = wrkJet.OpenDatabase("C:\MyDB.mdb")

Set rstMyRecord = dbsCurrent.OpenRecordset( _
"tblReport", dbOpenTable, dbReadOnly)
 
Hi souris (do you know that in french it means mouse?)
I think you cannot leave blank the first parameter

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

The access help states that the first parameter is name: A String that
uniquely names the new Workspace object. See the Name property for details on
valid Workspace names.

so if you give a name to the workspace I think everything'll works
e.g. Set wrkJet = CreateWorkspace("workspacename", "admin", "", dbUseJet)

HTH Paolo
 
Thanks Paolo,

Yes, Souris is mouse.
I got it works,
I have another question that may I return recordset from my function?
or I have to access them in the same function?
Thanks again,
 
SELECT * FROM tblMyTable where tbl_ID = 1 and MyDate between #12/01/2007# and
#12/31/2007#;

I have above SQL for my record set.

The SQL works on my query, but it does not work on my VBA code.

The SQL works if I do not include date criteria.

Are there any special way to dealing with date criteria for my record set?

Your help is great appreciated,
 
Souris said:
SELECT * FROM tblMyTable where tbl_ID = 1 and MyDate between #12/01/2007#
and
#12/31/2007#;

I have above SQL for my record set.

The SQL works on my query, but it does not work on my VBA code.

The SQL works if I do not include date criteria.

Are there any special way to dealing with date criteria for my record set?
Click to expand...


The only special thing about date criteria is that date literals must be
specified either in an unambiguous format or in US-centric month/day/year
format, as you have done in the SQL statement you posted. If this exact SQL
isn't working in code, there must be something wrong with your code.

Please post the code and the result or error message you are getting.
 
Thanks for the message,
It is my code. I found the problem.

I have an other issue that I tried to add the recordset value to my
collection which works on string

Here is my code

The code executed without error but my collection is empty

Mycollection.Add CStr(rsMyRecord.Fields![MyCode].Value)


I see the value in my debug watches, but the collection does not added.

Thanks again,
 
Souris said:
Thanks for the message,
It is my code. I found the problem.

I have an other issue that I tried to add the recordset value to my
collection which works on string

Here is my code

The code executed without error but my collection is empty

Mycollection.Add CStr(rsMyRecord.Fields![MyCode].Value)


I see the value in my debug watches, but the collection does not added.
Click to expand...


I don't see anything wrong with that line of code, so most likely it's
something in the surrounding code that is causing the problem. We'd need to
see a lot more of the code.
 
Back
Top