Read SQL Statement from ACCESS View

Question

I have searched the net and cannot find a clue as to how to read the SQL statement from an ACCESS VIEW/QUERY using C#. Can anyone help please.

Thomas · Answer

ok ignore my above post. As far as I know, outside of access Views are just seen as other tables. I don't think you can get to the SQL behind the view unless you are physically in that access database. I have more experience with tables than views though. sorry.

Thomas · Answer

It kind of depends on what you want the SQL to do. If it is to view tables then an OleDbDataAdapter is your best option. If you want to execute SQL commands that manipulate tables within access but don't return data, then an OleDbCommand is your best bet. Either way you will need to open a connection to the DB with an OleDbConnection. the connection sting I use is OleDbConnection lConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + MYAccessDBPathAndName + ";Mode=Share Deny None;Extended Properties='';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"); lConn.Open(); //Just make sure to close it when you are done Examples of the command and adapter.... string MySQL = "Select * from MyTable"; DataTable MyCTable = new DataTable(); OleDbDataAdapter dadapt = new OleDbDataAdapter(MySQL, lConn); dadapt.Fill(MyCTable); (then you can do whatever you want with the data table now in C#) OR OleDbCommand lAccCmd = new OleDbCommand(); lAccCmd.Connection = lConn; MySQL = "Delete * from MyAccessTable; lAccCmd.CommandText = MySQL; lAccCmd.ExecuteNonQuery(); lConn.Close();

Michael Griffith · Answer

I feel most embarrassed admitting this, but 'readSQLStatements' was one of my own methods. So, please ignore completely the reference to it in my earlier reply. My apologies Michael

Michael Griffith · Answer

Thank you Vilas for your suggestion. I have looked at the link and I have no trouble at all in accessing the database and looking at columns and data etc. What I cannot find is the location of the actual SQL string in a View. I can find all kinds of information about columns and their fields but nothing that is the SQL string. I am now trying to go down the route of running Access from C# but it isn't really getting me anywhere although I have come across an interesting item 'oAccess.DoCmd.SelectObject(readSQLStatements)' but cannot seem to get any further with it and cannot find any reference to it on the web (googling). All I wish to do is to extract the SQL statement string and save it elsewhere. If i have missed something then I do apologise and can only plead being a complete amateur. Kind regards Michael

Michael Griffith · Answer

Thank you Vilas for your suggestion. I have looked at the link and I have no trouble at all in accessing the database and looking at columns and data etc. What I cannot find is the location of the actual SQL string in a View. I can find all kinds of information about columns and their fields but nothing that is the SQL string. I am now trying to go down the route of running Access from C# but it isn't really getting me anywhere although I have come across an interesting item 'oAccess.DoCmd.SelectObject(readSQLStatements)' but cannot seem to get any further with it and cannot find any reference to it on the web (googling). All I wish to do is to extract the SQL statement string and save it elsewhere. If i have missed something then I do apologise and can only plead being a complete amateur. Kind regards Michael

Vilas Gite · Answer

Hi Michael.. Refer this link, may be helpful for you… http://www.codeproject.com/KB/database/DatabaseSpy_CS.aspx Thanks! ---------------------------------------------------------------------- If this reply helps your post…then check " This is correct answer"