Access: SQL export [SOLVED]
-
For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself. To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 10:12 AM
-
For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself. To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 10:12 AM
-
For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself. To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 10:12 AM
This article may help you out ... http://www.databasejournal.com/features/msaccess/article.php/3528491/Use-System-Tables-to-Manage-Objects.htm[^] It talks about the Sys Tables inside MS-Access. Good luck. :thumbsup:
-
For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself. To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 10:12 AM
Problem solved, using "good" old DAO from C# isn't that hard after all, and all that was required is this:
public override void GetQueries() {
DAO._DBEngine dbEng=new DAO.DBEngineClass();
DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
DAO.Database db = ws.OpenDatabase(File, false, false, "");
foreach(DAO.QueryDef q in db.QueryDefs) {
env.log(0, q.Name+" = "+q.SQL);
}
}Thanks for all the input! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Thanks a lot, I got it working right away. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
This article may help you out ... http://www.databasejournal.com/features/msaccess/article.php/3528491/Use-System-Tables-to-Manage-Objects.htm[^] It talks about the Sys Tables inside MS-Access. Good luck. :thumbsup:
Thanks for the quick response. I managed to solve my little problem with the first reply I got. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Problem solved, using "good" old DAO from C# isn't that hard after all, and all that was required is this:
public override void GetQueries() {
DAO._DBEngine dbEng=new DAO.DBEngineClass();
DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
DAO.Database db = ws.OpenDatabase(File, false, false, "");
foreach(DAO.QueryDef q in db.QueryDefs) {
env.log(0, q.Name+" = "+q.SQL);
}
}Thanks for all the input! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Problem solved, using "good" old DAO from C# isn't that hard after all, and all that was required is this:
public override void GetQueries() {
DAO._DBEngine dbEng=new DAO.DBEngineClass();
DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
DAO.Database db = ws.OpenDatabase(File, false, false, "");
foreach(DAO.QueryDef q in db.QueryDefs) {
env.log(0, q.Name+" = "+q.SQL);
}
}Thanks for all the input! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum