How to work with Access 2003 in VB.net

[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

 

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

 

[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

 

[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(VS.90).aspx


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

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

It must be a SELECT query.

"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(VS.85).aspx

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


DROP VIEW statement

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.