If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Create multiple queries in Access with a single sql statement
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 |
#13
|
|||
|
|||
Create multiple queries in Access with a single sql statement
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; } |
#14
|
|||
|
|||
Create multiple queries in Access with a single sql statement
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; } |
#15
|
|||
|
|||
Create multiple queries in Access with a single sql statement
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 |
#16
|
|||
|
|||
Create multiple queries in Access with a single sql statement
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 |
|
Thread Tools | |
Display Modes | |
|
|