Vb.net Ms Access Database 2007 Reusable Procedures/queries/functions

[Rob W]

Greetings,

This question is a mixture of ms access and vb.net I guess.

My application utilises an access 07 database and a frequent sql query is to
count the rows where columnA = textbox.text or combobox.selectedvalue etc..,

This is hardcoded into each module, so took an opportunity to go about
reuses sql code.

I was looking into the OleDD commandtype and noticed it could run a stored
procedure, then found out MS access is not capable of creating a true stored
procedure but can create procedures via DDL.

I was messing around with access and noticed any query I created when
accessed via vb.net existed as a function.

What is the preferred method to reuse and execute SQL for a ms access 07
database in vb.net?
Can I access and run the database function from within vb.net?

Any help or advice would be appreciated

Thanks
Rob

 

[sloan]

The preferred solution is to move to Sql Server CE or Sql Server Express.

You can look here
http://support.microsoft.com/kb/202116

I would look here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
And download the code.

Aspect#1
If you're bent on sticking with a Jet database, then you can look at
CustomerDataBaseLine.cs
It is simple dynamic sql.

Aspect#2
You can also look at the Sql Server CE example.
Aka, consider moving to it.

Aspect#3
Even if you're bent on staying with a Jet database right now, you CAN
architect your code so you're not married to it.
That is the primary reason for the example code. How to use a Jet backend,
but not be married to it forever.
The effort is well worth it up front so you have an option in the future.

 

[sloan]

I believe you can use the EnterpriseLibrary.Data with "parameterized
queries" in Jet.


Create a "query" in the Jet Database through the MS Access Front End
application.

PARAMETERS pEmail Text ( 255 );
INSERT INTO MyTable ( EmailAddress )
SELECT [pEmail];

name the query "procEmailUpdate".


EnterpriseLibrary.Data snipplet below. The syntax might be slightly off,
but its close.


Database db = null;
System.Data.DbCommand dbc = null;
db = this.GetDatabase(); // encapsulated, code not shown here
dbc = db.GetStoredProcCommandWrapper ( "procEmailUpdate" );
dbc.AddInParameter ("pEmail" ,DbType.String , "(e-mail address removed)" );


int result = db.ExecuteNonQuery(dbc);

}

 

[Rob W]

Thanks for the information will have a read and then make a decision for the
way forward.

Thanks again

I believe you can use the EnterpriseLibrary.Data with "parameterized
queries" in Jet.


Create a "query" in the Jet Database through the MS Access Front End
application.

PARAMETERS pEmail Text ( 255 );
INSERT INTO MyTable ( EmailAddress )
SELECT [pEmail];

name the query "procEmailUpdate".


EnterpriseLibrary.Data snipplet below. The syntax might be slightly off,
but its close.


Database db = null;
System.Data.DbCommand dbc = null;
db = this.GetDatabase(); // encapsulated, code not shown here
dbc = db.GetStoredProcCommandWrapper ( "procEmailUpdate" );
dbc.AddInParameter ("pEmail" ,DbType.String , "(e-mail address removed)" );


int result = db.ExecuteNonQuery(dbc);

}

The preferred solution is to move to Sql Server CE or Sql Server Express.

You can look here
http://support.microsoft.com/kb/202116

I would look here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
And download the code.

Aspect#1
If you're bent on sticking with a Jet database, then you can look at
CustomerDataBaseLine.cs
It is simple dynamic sql.

Aspect#2
You can also look at the Sql Server CE example.
Aka, consider moving to it.

Aspect#3
Even if you're bent on staying with a Jet database right now, you CAN
architect your code so you're not married to it.
That is the primary reason for the example code. How to use a Jet
backend, but not be married to it forever.
The effort is well worth it up front so you have an option in the future.

 

[sloan]

Just a nit pick.

//quote// ""My application utilises an access 07 database""

No, your application uses a JET database.
It happens to have a Microsoft Access 2007 front end.

............

http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine


It has since been superseded, however, first by Microsoft Desktop Engine
(MSDE), then later by SQL Server 2005 Express Edition and most recently by
SQL Server 2005 Compact Edition, and no longer exists as a component of
Microsoft Data Access Components (MDAC).


Over the years, Jet has become almost synonymous with Microsoft Access, to
the extent where many people incorrectly refer to a Jet database as an
"Access database". Even Microsoft themselves do this sometimes, but this
nomenclature should always be seen as incorrect. Jet is a database and
Access is a database application development tool (database builder).

Thanks for the information will have a read and then make a decision for
the way forward.

Thanks again

I believe you can use the EnterpriseLibrary.Data with "parameterized
queries" in Jet.


Create a "query" in the Jet Database through the MS Access Front End
application.

PARAMETERS pEmail Text ( 255 );
INSERT INTO MyTable ( EmailAddress )
SELECT [pEmail];

name the query "procEmailUpdate".


EnterpriseLibrary.Data snipplet below. The syntax might be slightly off,
but its close.


Database db = null;
System.Data.DbCommand dbc = null;
db = this.GetDatabase(); // encapsulated, code not shown here
dbc = db.GetStoredProcCommandWrapper ( "procEmailUpdate" );
dbc.AddInParameter ("pEmail" ,DbType.String , "(e-mail address removed)" );


int result = db.ExecuteNonQuery(dbc);

}

The preferred solution is to move to Sql Server CE or Sql Server
Express.

You can look here
http://support.microsoft.com/kb/202116

I would look here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
And download the code.

Aspect#1
If you're bent on sticking with a Jet database, then you can look at
CustomerDataBaseLine.cs
It is simple dynamic sql.

Aspect#2
You can also look at the Sql Server CE example.
Aka, consider moving to it.

Aspect#3
Even if you're bent on staying with a Jet database right now, you CAN
architect your code so you're not married to it.
That is the primary reason for the example code. How to use a Jet
backend, but not be married to it forever.
The effort is well worth it up front so you have an option in the
future.







Greetings,

This question is a mixture of ms access and vb.net I guess.

My application utilises an access 07 database and a frequent sql query
is to count the rows where columnA = textbox.text or
combobox.selectedvalue etc..,

This is hardcoded into each module, so took an opportunity to go about
reuses sql code.

I was looking into the OleDD commandtype and noticed it could run a
stored procedure, then found out MS access is not capable of creating a
true stored procedure but can create procedures via DDL.

I was messing around with access and noticed any query I created when
accessed via vb.net existed as a function.

What is the preferred method to reuse and execute SQL for a ms access
07 database in vb.net?
Can I access and run the database function from within vb.net?

Any help or advice would be appreciated

Thanks
Rob

 

[Cor Ligthert[MVP]]

Rob,

In all ways it is for me preferable that you make a separate data class. If
that is a separate DLL is up to you.

In that class you can put all your data handling you want. You don't need
stored procedures.

If it is SQL Server then you even can use Linq to Sql or Linq to EF but that
is in my opinion not the most important part.

Cor