Create multiple queries in Access with a single sql statement

[Corby Nichols]

Is there a way to create multiple queries with a single sql script, similiar
to SQL Server 'Create Procedure' statement.

I have a code generator that generates Stored Procedures for SQL Server and
code generates a data writer to set the parameters, and this works fine for
SQL Server. For Access I only know how to create queries one at a time, and
since each table requires five querries (Insert, Update, Find, FetchAll, &
Delete).

In SQL Server I know how to seperate individual SQL Statements with the 'GO'
command, is there a way to create querries in Access. I would like to ideally
be able to create a single file that can be copied to the clipboard and
executed in the Access query designer window. I have googled this and not
found very much information on this type of thing.

Thank you,
Corby Nichols
Houston, TX

 

[Klatuu]

About the only thing you can do to save some time is to create the Select
query first, the use it as a template to create the other versions you need.

 

[Corby Nichols]

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

 

[Klatuu]

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)
*************************************

 

[John W. Vinson]

Is there a way to create multiple queries with a single sql script

Only as a PassThrough query. Create the query from scratch in SQL view (don't
ever go into the query design window!) and set its Properties to Pass Through.

John W. Vinson [MVP]

 

[Corby Nichols]

You answered:

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.

By make an .exe I mean in either C# or VB.Net (or VB6 if I had too). I am a
software developer.

Let me be specific as to what I want. I know how to create tables using SQL,
using the Create Table sql statement, I can execute a non query and create
tables. Is there an Access SQL equilivent of Create Procedure? Such as Create
Query. I will try the code you attached for creating a QueryDef to see if
that works.

Thanks for your help, I may not bother creating an Access version, I have an
Access version that uses Dynamic SQL, but I preferred using a Query if I can
create one without forcing users of my app to manually execute serveral dozen
queries.

Thanks,
Corby Nichols

 

[John W. Vinson]

Thanks for your help, I may not bother creating an Access version, I have an
Access version that uses Dynamic SQL, but I preferred using a Query if I can
create one without forcing users of my app to manually execute serveral dozen
queries.

You can use the CreateQuerydef method to create a Query (and you can store it
in the Queries collection or not as you choose); and you can certainly execute
as many queries as you wish from code, without needing to create a composite
query.

John W. Vinson [MVP]

 

[Klatuu]

Per my previous response, the CreateQueryDef method is what you use to create
a query.
Sounds to me like you have an attitude toward Access. Many software
developers have this attitude and it comes from a position of ignorance. I
too am a software developer and have been for 30 years working in multiple
languages and environments. Access is not designed to implement large scale
enterprise applications. I have seen places where an Access app would do
nicely and the powers that be have decided to spend a ton of money on "real"
software tools.

Because you don't know and understand the environment doesn't mean it isn't
as good as what you know.

 

[Corby Nichols]

First off (Dave Hargis) your use of the word ignorant was out of line.

I haved used Access for dozens of applications any time it is for either a
small company or a stand alone app. The reason I said I may not bother
creating an Access Version of my program is because I have a SQL Server
version already complete. I do not have the time or budget to create an
Access Version is what I meant. Access is a great database for small
projects, as long as you do not put more then 10 to 12 simultaneous users it
works great, other wise SQL is the way to go.

But thank you for your response, I will see how long it takes to create an
Access version.

Corby Nichols

 

[Klatuu]

Sorry if you are offended by the use of the word. FYI it means lack of
knowledge. There is a big difference between ignorant and stupid. You
estimation of the number of users is a little low. The company I work for
has several Access applications that are the heart of our business. There
are probably 100 simultaneous users. All apps are MDE frontends and MDB
backends. Only now are we migrating to C#, but that is only because the
powers that be buy into the "Access is only a toy" mindset.

 

[Corby Nichols]

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

 

[Klatuu]

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

 

[Corby Nichols]

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;
}

 

[Klatuu]

Thanks, Corby. I will keep this. I'm pretty sure I will need it in the not
too distant future.

 

[Ron]

Hi David,Contact me, Ron Binette

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)
*************************************

 

[Klatuu]

Ron,
I wondered why I had not heard back from you.
Email me, please.