DataRow[] rows = dt.Select(); for (int i = 0; i < rows.Length; i++) { if (!rows[i]["TABLE_NAME"].ToString().Contains("MSys")) //Don't include all the system tables in Access { tempList.Add(rows[i]["TABLE_NAME"].ToString()); } } return tempList.IndexOf(TableName)>=0; }
Saavik
Member
467 Points
457 Posts
Is it possible to check existence of object before accesing?
Sep 12, 2011 11:24 PM|LINK
Is it possible to check the existence of a table or query before trying to access it?
I am specifically thinking of writing different version of a software where the database may be a littel different.
================
Saavik
PNasser
Contributor
2811 Points
531 Posts
Re: Is it possible to check existence of object before accesing?
Sep 13, 2011 04:23 AM|LINK
You could run a query to see if the table exists:
SELECT * FROM sys.tables Where Name = 'TableName'
If this returns a result, the table exists. Similiarly use sys.procedures for stored procedures
Saavik
Member
467 Points
457 Posts
Re: Is it possible to check existence of object before accesing?
Sep 13, 2011 05:09 AM|LINK
Thank you for your reply.
Exactly the kind of thing I was looking for. However, I am getting this error:
"Could not find file 'C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\10.0\sys.mdb."
Why is it looking for sys.mdb? And why in this path? The Access file I am using is in a completely different path - even a different drive!
The connection string is working fine for all other database accesses, except this test.
================
Saavik
PNasser
Contributor
2811 Points
531 Posts
Re: Is it possible to check existence of object before accesing?
Sep 13, 2011 06:17 AM|LINK
Ah, I made a poor assumption you were using ms sql server. For access check out this link: http://access.databases.aspfaq.com/how-do-i-determine-if-a-table-exists-in-an-access-database.html
hans_v
All-Star
35986 Points
6550 Posts
Re: Is it possible to check existence of object before accesing?
Sep 13, 2011 07:04 AM|LINK
For Access use:
Type=1 is a table. If you want to check if a query with a particular name exists, use [Type]=5
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Is it possible to check existence of object before accesing?
Sep 15, 2011 02:25 AM|LINK
Hello,
To find out whether a table name is included in an Access db, you can also try this:
public boolean ContainsTable (string OleDbConnectionStr,string TableName)
{
List<string> tempList = new List<string>();
OleDbConnection oleConn = new
OleDbConnection(OleDbConnectionStr);
oleConn.Open();
DataTable dt = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
oleConn.Close();
DataRow[] rows = dt.Select();
for (int i = 0; i < rows.Length; i++)
{
if (!rows[i]["TABLE_NAME"].ToString().Contains("MSys")) //Don't include all the system tables in Access
{
tempList.Add(rows[i]["TABLE_NAME"].ToString());
}
}
return tempList.IndexOf(TableName)>=0;
}
Saavik
Member
467 Points
457 Posts
Re: Is it possible to check existence of object before accesing?
Sep 15, 2011 03:19 AM|LINK
This seems so nice and simple - unfortunately, I am getting an error:
Record(s) cannot be read; no read permission on 'MSysObjects'.
There is no password on this Access file. How do I fix this?
================
Saavik
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Is it possible to check existence of object before accesing?
Sep 19, 2011 02:21 AM|LINK
Hello again：）
Have you tried mine?
Saavik
Member
467 Points
457 Posts
Re: Is it possible to check existence of object before accesing?
Sep 19, 2011 02:26 AM|LINK
Sorry - I forgot to reply.
Yes, I tried a modified version of yours, and it worked - thank you.
How about checking for specific fields in tables or queries?
================
Saavik
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Is it possible to check existence of object before accesing?
Sep 19, 2011 02:42 AM|LINK
Hello again:)
Suppose you have a DataTable instance filled by OleDbDataAdapter, and you can do simply like this:
1）DataTable.Columns.Contains("ColumnName")
Or
2）DataTable.Columns.IndexOf("ColumnName")>=0