0

This may be a very strange question but I am trying to 'obtain' the SQL command for the purpose of reporting.

So say my Microsoft Access Database has a query which when executed, runs this SQL statement:

SELECT Staff.SurName, Staff.ID, Staff.Salary, Staff.StartDate, Staff.JobTitle,
Manager.SurName AS [Manager Surname], Department.DeptName AS [Department Name]
FROM Employee AS Staff, Employee AS Manager, Department
WHERE (((Staff.ManagerID)=[Manager].[ID]) AND
           ((Staff.DeptID)=[Department].[DeptID]));

I would like my visual Basic application to be able to 'obtain' the SQL statement for ANY given database, provided there is a statement to be obtained(I.e. If the database has any queries, obtain the statement and return it to the application), if the database has no queries, inform the user with a message.

Is this actually possible?

UPDATE:

I am trying to complete this by using the 'GetSchema()' function:

Dim schema As DataTable = con.GetSchema("PROCEDURES")

But the "PROCEDURES" is causing an error, is anyone familiar with the GetSchema() function?

I've tried all sorts in my searching efforts but I've not had any luck, so I came here.

Thanks in advance

AdamWest
  • 29
  • 1
  • 9
  • Your use of the word "extract" is very confusing. Can you explain what you're trying to accomplish? – egrunin Aug 22 '12 at 15:52
  • Are you using MS Access? The reason I'm asking is that I'm confused by your terminology. What do you mean by "a query existing in the database". Unless you're using MS Access, this doesn't make sense, because a query is not a database object. If you're using Access, then it DOES make sense because a query, in Access, is a database object, aking to a view or a stored procedure in other systems. If I am guessing right, you simply mean that "I's like to get the SQL Statement behind every query saved in an Access database". Is that right? – David Aug 22 '12 at 15:53
  • Sorry for the confusion, I'm still learning terminology and such. I am using a MS Access Database which has a query named qryScores, the corresponding statement for that query is listed above. My intention is that I wish to be able to obtain the statement and display it in a rich text box – AdamWest Aug 22 '12 at 16:07
  • I have made amendments to help clarify my issue – AdamWest Aug 22 '12 at 16:14
  • The reason why I say "for ANY" given database is because I do not hardcode the database name or location in the program, this is a free choice of the user. They use a dialogue box to select the database they wish to create a report from, so obtaining the schema information has to be free of fixed named databases, which I am doing fine on as I simply use variables for their choices and refer to the variables when connecting (If this makes sense.. It does to me! ha) – AdamWest Aug 22 '12 at 16:18
  • Yes David you understood me correctly – AdamWest Aug 22 '12 at 16:21
  • OK. Thank you, that is much clearer! I've never done this, but using this new info, I was able to find a previous post that appears to answer what you're looking for, as far as extracting the queries. http://stackoverflow.com/questions/371404/how-do-i-list-all-the-queries-in-a-ms-access-file-using-oledb-in-c It looks like it's in C#, but converting it to VB should be no problem. – David Aug 22 '12 at 16:33
  • I'm a little stuck with the solution they offer, I am using GetSchema function to obtain the information I need, but I'm unsure what parameters to give the function in order for it to return the information I want. I tried: Dim schema As DataTable = con.GetSchema("PROCEDURE_DEFINITION") With no avail. What fields does Getschema return? – AdamWest Aug 22 '12 at 16:49

2 Answers2

0

each query has an .SQL property.

This code will loop through the current db, and give you the SQL for every query it finds:

Dim db As Database
Dim qr As QueryDef

Set db = CurrentDb

For Each qr In db.QueryDefs
    MsgBox qr.SQL
Next
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Database isn't a type I can use at it is not defined in my setup. Is this a problem? Why is this the case? I'm using visual studio 2010 – AdamWest Aug 22 '12 at 19:45
  • You could access this from VS2010 by adding a reference to Microsoft Access. You would need to declare db as `Dim db as Access.Database` – Hannah Vernon Aug 22 '12 at 19:47
  • sorry - misread question. this is access vba. I will find the vb equivalent – SeanC Aug 22 '12 at 20:03
  • Again. access.Database is unidentified. Is this because I have not installed visual studio 2010 correctly? Thank you for the attempts thus far anyway! – AdamWest Aug 22 '12 at 20:49
  • 1
    if you haven't got access in your references, then late binding using `dim db as Object` then `set db=getobject("c:\MyFiles\MyDatabase.mdb")` *should* work (at work right now, will test when I get home) – SeanC Aug 22 '12 at 21:01
  • In order to get Access added as a reference you will need to right-click the project in VS2010's Project Explorer window, then click "Add Reference", then click the "COM" tab, then find "Microsoft Access 12.0 Object Reference" in the list, select it, and click "OK". This adds a reference to your VS2010 project. Then `Dim db as Access.Database` will work. – Hannah Vernon Aug 22 '12 at 21:47
0

Assuming you have a textbox, TextBox1, and a command button named Command2, you might use:

Private Sub Command2_Click()

    Me.TextBox1 = CurrentDB.QueryDefs("qryScores").Sql

End Sub
Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48