How to work with Access 2003 in VB.net

7 May 2010 2:00 AM

Ben

Hi all,

Is there a way to work with Access 2003 in VB.net, such as:
1. dynamically create a query from vb.net code and save it into Access?
2. cycle through the query definition as I would be able to using DAO?
3. change a query's SQL dynamically as condition changes based on
parameters?
4. Deleting queries dynamically
5. just being able to use the DAO.

I don't know if ADO.net can do all these things that DAO can do
natively, if it can, can you please share some of your thoughts, code
snippets and web links?

Thanks,

Ben

7 May 2010 5:53 AM

Cor Ligthert[MVP]

AdoNet can, but be aware that the newer OS systems get more and more trouble
with it in 64bit mode.

The connector to Jet (access) is OleDB.

http://www.connectionstrings.com

There are probably a million samples on Internet how to use that.

Show quoteHide quote

"Ben" <Ben@NoSpam.com> wrote in message
news:eD$HOkY7KHA.796@TK2MSFTNGP04.phx.gbl...
> Hi all,
>
> Is there a way to work with Access 2003 in VB.net, such as:
> 1. dynamically create a query from vb.net code and save it into Access?
> 2. cycle through the query definition as I would be able to using DAO?
> 3. change a query's SQL dynamically as condition changes based on
> parameters?
> 4. Deleting queries dynamically
> 5. just being able to use the DAO.
>
> I don't know if ADO.net can do all these things that DAO can do natively,
> if it can, can you please share some of your thoughts, code snippets and
> web links?
>
> Thanks,
>
> Ben
>

7 May 2010 12:21 PM

Ben

Cor,

Thanks for the weblink. I have been googling for the some samples to do
the items 1 -5 below, may be I am not using the right search words. Do
you have some you can share with me?

Thanks,

Ben

On 5/7/2010 1:53 AM, Cor Ligthert[MVP] wrote:
Show quoteHide quote

> AdoNet can, but be aware that the newer OS systems get more and more
> trouble with it in 64bit mode.
>
> The connector to Jet (access) is OleDB.
>
> http://www.connectionstrings.com
>
> There are probably a million samples on Internet how to use that.
>
>
> "Ben" <Ben@NoSpam.com> wrote in message
> news:eD$HOkY7KHA.796@TK2MSFTNGP04.phx.gbl...
>> Hi all,
>>
>> Is there a way to work with Access 2003 in VB.net, such as:
>> 1. dynamically create a query from vb.net code and save it into Access?
>> 2. cycle through the query definition as I would be able to using DAO?
>> 3. change a query's SQL dynamically as condition changes based on
>> parameters?
>> 4. Deleting queries dynamically
>> 5. just being able to use the DAO.
>>
>> I don't know if ADO.net can do all these things that DAO can do
>> natively, if it can, can you please share some of your thoughts, code
>> snippets and web links?
>>
>> Thanks,
>>
>> Ben
>>

7 May 2010 12:48 PM

Armin Zingler

Am 07.05.2010 14:21, schrieb Ben:

> Cor,
>
> Thanks for the weblink. I have been googling for the some samples to do
> the items 1 -5 below, may be I am not using the right search words. Do
> you have some you can share with me?

First thing to look is the documentation:
http://msdn.microsoft.com/en-us/library/e80y5yhx%28VS.90%29.aspx

>>> Is there a way to work with Access 2003 in VB.net, such as:

You must distinguish between the database file (mdb), the Jet engine
and Access.

>>> 1. dynamically create a query from vb.net code and save it into Access?

"CREATE VIEW" statement:
http://office.microsoft.com/en-us/access/CH010410161033.aspx

It must be a SELECT query.

>>> 2. cycle through the query definition as I would be able to using DAO?

"Retrieving Database Schema Information":
http://msdn.microsoft.com/en-us/library/kcax58fh(VS.90).aspx

Method OleDbConnection.GetSchema:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getschema.aspx

especially: (see section "Microsoft Jet OLE DB provider")
http://msdn.microsoft.com/en-us/library/cc668764(VS.90).aspx

"Schema Rowsets for Microsoft Jet":
http://msdn.microsoft.com/en-us/library/ms721248%28VS.85%29.aspx

>>> 3. change a query's SQL dynamically as condition changes based on
>>> parameters?

According to the link above,
http://office.microsoft.com/en-us/access/CH010410161033.aspx
the ALTER VIEW statement is not supported.

>>> 4. Deleting queries dynamically

DROP VIEW statement

>>> 5. just being able to use the DAO.

It's possible but not recommended. However, VB.Net+ADO.Net is worse
for Jet databases than VB6+DAO, but the "worser" option is VB.Net+DAO.

>>> I don't know if ADO.net can do all these things that DAO can do
>>> natively, if it can, can you please share some of your thoughts, code
>>> snippets and web links?

--
Armin