Create multiple queries in Access with a single sql statement

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

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.
--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:

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

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

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

On Tue, 19 Jun 2007 10:13:16 -0700, Corby Nichols
wrote:

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]

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

On Tue, 19 Jun 2007 17:43:52 -0700, Corby Nichols
wrote:

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]

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.
--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:

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

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

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.

--
Dave Hargis, Microsoft Access MVP


"Corby Nichols" wrote:


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