Retrieving Access SQL syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is their a way to retrieve MS Access 2000 query syntax from a VB.NET app using ADO.NET? Want to check the syntax that is in the database for something before I run the syntax

Thanks

Bob
 
¤ Is their a way to retrieve MS Access 2000 query syntax from a VB.NET app using ADO.NET? Want to check the syntax that is in the database for something before I run the syntax.
¤

Could you be more specific? Are you referring to the SQL in an Access QueryDef?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Bob,

If it is available, i guess it will be through the
OleDbConnection.OleDbSchemaTable method. I never used it for this type of
information, but perhaps a value of OldeDbSchemaGuid.Procedures for the
Schema parameter would give the required info. You find more information
about the values returned with this and other parameters in MSDN (but a bit
hidden) at
http://msdn.microsoft.com/library/d.../vcrefschemarowsetsupportinoledbtemplates.asp

Regards,
Jan

Bob said:
I want to retrieve the syntax from the actual database query in MS Access 2000. For example:

qryMyQuery in Access- the syntax is "Select Field1, Field2 FROM testTable"

I want to retrieve through VB.NET the syntax "Select Field1, Field2 FROM testTable"

We have found that a certain syntax needs to be added to databases for an
Click to expand...
application that we are converting from VB6 to .NET. However, all the new
Access databases will have the syntax change incorporated into the queries
in Access so I want to read the syntax if an error has occured to see if I
need to manually submit the SQL syntax through ADO.NET.
 
Hi Bob,

You would need to catch error. I do not think there is an any way to check
syntax of the query against Access database. SQL Server has it.
 
¤ I want to retrieve the syntax from the actual database query in MS Access 2000. For example:
¤
¤ qryMyQuery in Access- the syntax is "Select Field1, Field2 FROM testTable"
¤
¤ I want to retrieve through VB.NET the syntax "Select Field1, Field2 FROM testTable"
¤
¤ We have found that a certain syntax needs to be added to databases for an application that we are converting from VB6 to .NET. However, all the new Access databases will have the syntax change incorporated into the queries in Access so I want to read the syntax if an error has occured to see if I need to manually submit the SQL syntax through ADO.NET.

I'm not aware of a method for retrieving the command text using the schema methods. However, you can
use ADO and the ADOX (Microsoft ADO Ext 2.x for DDL and Security) COM libraries or DAO.

Sub RetrieveQueryDefCommandText()

Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

Console.WriteLine(ADOXCatalog.Procedures("ValidateUser").Command.CommandText)

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try

End Sub

End Module


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top