Hi everyone
I am trying to display all the tables in a MS access database through a application im developing in c#. However im receiving errors. Here is part of my code. This method executes a SQL query and returns a OdbcDataReader object.
private
OdbcDataReader GetQueryReader(string query){
OdbcDataReader reader =
null;OdbcCommand command =
new OdbcCommand(query, serverConnection);Open();
try{
reader = command.ExecuteReader();
}
catch(NullReferenceException e){
Utility.CheckTool.ShowMsg("Error in getting the reader, null exception error" );
reader.Close();
}
catch(OdbcException e){
Utility.CheckTool.ShowMsg("Error in getting the reader, odbc exception error" );
foreach(OdbcError oErr in e.Errors){
Console.WriteLine("Message: " + oErr.Message);
Console.WriteLine("Native error: " + oErr.NativeError);
Console.WriteLine("SQLState: " + oErr.SQLState);
Console.WriteLine("Source: " + oErr.Source);
}
reader.Close();
}
return reader;}
Now I believe the query that im using is wrong. The query was "SHOW TABLES;". However this works with MySQL and not with access.THe error i receive is:
Message: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
What is the access equivalent for 'SHOW TABLES'.
DISPLAYING TABLES OF AN ACCESS DATABASE THROUGH C#
guaro
This select statement does allow me to see a list of the table names, however when i want to choose a specific table out of the list i get an error like the one below
Message: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Native error: -3500
SQLState: 42000
Source: odbcjt32.dll
ANy ideas
Thanks
Wayne.C
Woops, i understand where i went wrong.
THanks for the help with that one.
I had another query maybe you can help.
What is the MS ACCESS equivalent for the 'DESCRIBE table_name' query.
I want to retrieve all column names and their data types and to see which columns constitute the primary key etc
Thanks Again
Jeff Patton
Actually you would use OLEDB and not ODBC if your are using Visual Studio 2003.
Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable
DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\db1 XP.mdb"
DatabaseConnection.Open()
SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, Nothing})
'Retrieve schema information about Table1 Columns.
'SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
' New Object() {Nothing, Nothing, "Table1"})
Dim RowCount As Int32
For RowCount = 0 To SchemaTable.Rows.Count - 1
Next RowCount
'Shows what information is available
DataGrid1.DataSource = SchemaTable
DatabaseConnection.Close()
sl333
A bit of internet searching will get you the answer... for example this page has the exact SELECT statement you need:
http://groups.google.ro/group/microsoft.public.vb.database.odbc/browse_thread/thread/827076af3da603a7/d7394f27b7e88fc1%23d7394f27b7e88fc1
RAFIKABDAT
How would you do it if your using the Microsoft .NET 1.1
Thanks
Christopher Lusardi
You can use GetSchema in ADO.NET 2.0 which is really easy and semi-database independent when using the Provider Factories.
Here is a tutorial:
Get List of Tables in an Access Database - ADO.NET Tutorials
Here are a couple of tutorials using SQL Server:
Get List of Tables in a Database - Query INFORMATION_SCHEMA.Tables - ADO.NET
GetSchema and DbProviderFactories - List of Tables in a Database
Sample Code for Microsoft Access:
// Microsoft Access provider factory
[2].ToString());
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DataTable userTables = null;
using (DbConnection connection = factory.CreateConnection())
{
// c:\test\test.mdb
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test\\test.mdb";
// We only want user tables, not system tables
string[] restrictions = new string[4];
restrictions[3] = "Table";
connection.Open();
// Get list of user tables
userTables = connection.GetSchema("Tables", restrictions);
}
// Add list of table names to listBox
for (int i=0; i < userTables.Rows.Count; i++)
listBox1.Items.Add(userTables.Rows
Regards,
Dave
wazimu
I don't understand what do you mean by "i want to choose a specific table out of the list ". How do you choose a table What do you want to do with it