ADO vs DAO

[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,

 

[Rick Brandt]

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,

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.

 

[Souris]

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)

 

[Allen Browne]

You are trying to create a recordset using the Database object, which is
DAO. Therefore specify that you want a DAO recordset, i.e.:
Dim rstMyRecord As DAO.Recordset

Here's an example of working with a DAO recordset:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Here's more info about avoiding problems with the libraries:
http://allenbrowne.com/ser-38.html

 

[Paolo]

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

 

[Souris]

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,

 

[Souris]

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,

 

[Dirk Goldgar]

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?

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.

 

[Souris]

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,

 

[Dirk Goldgar]

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.

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.