Create multiple queries in Access with a single sql statement

Sorry I had just woke up and I agree I do not know as much as I would like to
know about Access. To create an Access Query in C#, is System.Data.OleDb the
namespace to create an Access Query via C#. Do you by chance know a code
snippliet to create a Query ?

Thank you,

Corby

If you mean in C#, I can't help. I am still ignorant with a capitol IG, but
I am learning. If you mean in Access, then see VBA Help for the
CreateQueryDef method. Basically, you write the SQL for the query, then you
use the CreateQueryDef method to save it as a stored query.

Here is a small example. In this case, I delete the old querydef and create
a new one with the same name. This is because it is doing the filtering for
a complex report.

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_BPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

If Len(strWhere) 0 Then
strWhere = "HAVING " & strWhere & " ORDER BY "
strSQL = Replace(strSQL, "ORDER BY", strWhere)

End If

Set qdf = dbf.CreateQueryDef("_BPOTemp", strSQL)

'Now there is a query in the querydefs collection named _BPOTemp
--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:

Sorry I had just woke up and I agree I do not know as much as I would like to
know about Access. To create an Access Query in C#, is System.Data.OleDb the
namespace to create an Access Query via C#. Do you by chance know a code
snippliet to create a Query ?

Thank you,

Corby

I found out how to accomplish creating queries in Access using C# so I
thought I would post my solution to this in case anyone else ever has a need
to do this via C#.

I had to add a reference to DAO.dll version 3.6 (found on my machine in
c:\Program Files\Common Files\Microsoft Shared\DAO).

Some of this code is part of my source code generator but I am sure you can
follow the code.

/// summary
/// This method creates a query for an Access Database Table.
/// /summary
/// param name="dataTable"/param
/// returns/returns
public static bool CreateQuery(DataTable dataTable, QueryTypeEnum
queryType)
{
// initial value
bool queryCreated = false;

// locals
string queryName = "";
string queryText = "";
string path = @"D:\RAD
Studio\RADStudioClient\DataClassBuilder.mdb";

try
{

// if the dataTable exists
if ((dataTable != null) && (dataTable.HasPrimaryKey))
{
// Create DAO Connection, Workspace & Database.
dao._DBEngine dbEngine = new dao.DBEngine();
dao.Workspace workSpace =
dbEngine.CreateWorkspace("MainWorkspace", "admin", "",
dao.WorkspaceTypeEnum.dbUseJet);
dao.Database db = workSpace.OpenDatabase(path, false,
false, Type.Missing);

// Determine the action to take based upon the
// queryType.
switch(queryType)
{
case QueryTypeEnum.Delete:

// set queryName
queryName = dataTable.Name + "_Delete";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Delete);

// required break
break;

case QueryTypeEnum.FetchAll:

// set queryName
queryName = dataTable.Name + "_FetchAll";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.FetchAll);

// required break
break;

case QueryTypeEnum.Find:

// set queryName
queryName = dataTable.Name + "_Find";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Find);

// required break
break;

case QueryTypeEnum.Insert:

// set queryName
queryName = dataTable.Name + "_Insert";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Insert);

// required break
break;

case QueryTypeEnum.Update:

// set queryName
queryName = dataTable.Name + "_Update";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Update);

// required break
break;
}

// Delete Query In Case It Already Exists
DeleteQuery(queryName, db);

// Create QueryDef
QueryDef query = db.CreateQueryDef(queryName, queryText);

// if the query was created
if (query != null)
{
// query was created
queryCreated = true;
}
}
}
catch(Exception error)
{
// Inform User of error
MessageBox.Show("An error occurred connecting to the local
database." + Environment.NewLine + error.ToString(), "Data Error",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}

// return value
return queryCreated;
}

Thanks, Corby. I will keep this. I'm pretty sure I will need it in the not
too distant future.
--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:


I found out how to accomplish creating queries in Access using C# so I
thought I would post my solution to this in case anyone else ever has a need
to do this via C#.

I had to add a reference to DAO.dll version 3.6 (found on my machine in
c:\Program Files\Common Files\Microsoft Shared\DAO).

Some of this code is part of my source code generator but I am sure you can
follow the code.

/// summary
/// This method creates a query for an Access Database Table.
/// /summary
/// param name="dataTable"/param
/// returns/returns
public static bool CreateQuery(DataTable dataTable, QueryTypeEnum
queryType)
{
// initial value
bool queryCreated = false;

// locals
string queryName = "";
string queryText = "";
string path = @"D:\RAD
Studio\RADStudioClient\DataClassBuilder.mdb";

try
{

// if the dataTable exists
if ((dataTable != null) && (dataTable.HasPrimaryKey))
{
// Create DAO Connection, Workspace & Database.
dao._DBEngine dbEngine = new dao.DBEngine();
dao.Workspace workSpace =
dbEngine.CreateWorkspace("MainWorkspace", "admin", "",
dao.WorkspaceTypeEnum.dbUseJet);
dao.Database db = workSpace.OpenDatabase(path, false,
false, Type.Missing);

// Determine the action to take based upon the
// queryType.
switch(queryType)
{
case QueryTypeEnum.Delete:

// set queryName
queryName = dataTable.Name + "_Delete";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Delete);

// required break
break;

case QueryTypeEnum.FetchAll:

// set queryName
queryName = dataTable.Name + "_FetchAll";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.FetchAll);

// required break
break;

case QueryTypeEnum.Find:

// set queryName
queryName = dataTable.Name + "_Find";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Find);

// required break
break;

case QueryTypeEnum.Insert:

// set queryName
queryName = dataTable.Name + "_Insert";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Insert);

// required break
break;

case QueryTypeEnum.Update:

// set queryName
queryName = dataTable.Name + "_Update";
queryText = CreateQueryText(dataTable,
QueryTypeEnum.Update);

// required break
break;
}

// Delete Query In Case It Already Exists
DeleteQuery(queryName, db);

// Create QueryDef
QueryDef query = db.CreateQueryDef(queryName, queryText);

// if the query was created
if (query != null)
{
// query was created
queryCreated = true;
}
}
}
catch(Exception error)
{
// Inform User of error
MessageBox.Show("An error occurred connecting to the local
database." + Environment.NewLine + error.ToString(), "Data Error",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}

// return value
return queryCreated;
}

Hi David,Contact me, Ron Binette

"Klatuu" wrote:

you can create a query in Access, but you cannot create an .exe. All Access
applications are either mdb or mde and require either a full implementation
of Access or the runtime version. The runtime will not allow any
modifications, it is only for distributing applications.

It would be possible to create an mdb to use as an Add In.

But back to the point. All queries in Access are stored as text SQL
statements. You can read and write to queries using their SQL properties.
You can also create a new query by using the CreateQueryDef method.

Here is an example where I create a querydef based on an original querydef.
In this case, it is a way to modify the filtering criteria for a complex
report. In it you will see all the techniques you should need for creating
the code to write and save your queries.

***************************************
Dim strSQL As String
Dim qdf As QueryDef
Dim qdfXl As QueryDef
Dim dbf As DAO.Database
Dim qdfs As QueryDefs

'Export the Query
If strSaveFileName "" Then
Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdfXl = CurrentDb.QueryDefs(strXlQuery)
strSQL = qdfXl.SQL

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_BPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

If Len(strWhere) 0 Then
strWhere = "HAVING " & strWhere & " ORDER BY "
strSQL = Replace(strSQL, "ORDER BY", strWhere)

End If

Set qdf = dbf.CreateQueryDef("_BPOTemp", strSQL)
*************************************


--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:

Thank you for replying, Is there a way via code or VBA to create a query
programatically? In other words could an .exe be created that creates a
query?

Thanks again,

Corby Nichols

Ron,
I wondered why I had not heard back from you.
Email me, please.
--
Dave Hargis, Microsoft Access MVP


"Ron" wrote:

Hi David,Contact me, Ron Binette

"Klatuu" wrote:

you can create a query in Access, but you cannot create an .exe. All Access
applications are either mdb or mde and require either a full implementation
of Access or the runtime version. The runtime will not allow any
modifications, it is only for distributing applications.

It would be possible to create an mdb to use as an Add In.

But back to the point. All queries in Access are stored as text SQL
statements. You can read and write to queries using their SQL properties.
You can also create a new query by using the CreateQueryDef method.

Here is an example where I create a querydef based on an original querydef.
In this case, it is a way to modify the filtering criteria for a complex
report. In it you will see all the techniques you should need for creating
the code to write and save your queries.

***************************************
Dim strSQL As String
Dim qdf As QueryDef
Dim qdfXl As QueryDef
Dim dbf As DAO.Database
Dim qdfs As QueryDefs

'Export the Query
If strSaveFileName "" Then
Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdfXl = CurrentDb.QueryDefs(strXlQuery)
strSQL = qdfXl.SQL

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_BPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

If Len(strWhere) 0 Then
strWhere = "HAVING " & strWhere & " ORDER BY "
strSQL = Replace(strSQL, "ORDER BY", strWhere)

End If

Set qdf = dbf.CreateQueryDef("_BPOTemp", strSQL)
*************************************


--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:

Thank you for replying, Is there a way via code or VBA to create a query
programatically? In other words could an .exe be created that creates a
query?

Thanks again,

Corby Nichols