Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to obtain info about missing database elements

Posted on 2001-06-20
4
Medium Priority
?
159 Views
Last Modified: 2010-05-02
Hi,

With missing database elements I mean a table that the app expects to be there and that isn't or a column that is expected to be in a table and that isn't. I'm using the ADO functionality to access a database and what I want to achieve is to provide detailed information if database access fails due to a missing element. Is it possible to provide detailed information, for instance to say that table <tablename> is missing in database <databasename>, or that column <columnname> is missing in table <tablename> in database <databasename>, or that <columnname> is missing in view <viewname> in database <databasename> ? If I'm able to provide this detailed information, often it will be much easier to establish a proper diagnose, will in turn will result in quicker resolution of herefore mentioned problems. Please try to be precise if you come up with suggestions or solutions.

thanks,
Henk
0
Comment
Question by:hge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 1

Expert Comment

by:barendb
ID: 6213236
Henk,

I would use On Error Resume Next and then after calling the table or column check if error occurred.  You can do this once when opening the table or database so that you don't have to check it every time you read/write it.

Barend
0
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 400 total points
ID: 6214414
Hi, to find whether a table exists in a database, you can use the ADOX library:

Private Sub Command1_Click()
   Dim cnn As ADODB.Connection
   Dim cat As ADOX.Catalog
   Dim tblTable As ADOX.Table
   Dim blnExists As Boolean
   Set cnn = New ADODB.Connection
   Set cat = New ADOX.Catalog
   cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\cremosa2k.mdb;Persist
Security Info=False"
   cnn.Open
   cat.ActiveConnection = cnn.ConnectionString
   For Each tblTable In cat.Tables
       If tblTable.Name = "MyTable" Then
           blnExists = True
           Exit For 'as we have found it
       End If
   Next
   cnn.Close
   Set cat.ActiveConnection = Nothing
   Set cat = Nothing
   Set cnn = Nothing
   MsgBox "Table : MyTable " & IIf(blnExists, "Exists", "Does Not Exist")
End Sub

OR

Alternatively using Data Control

Data1.RecordSource = "Select * from Lead"
   Data1.Refresh
   For i = 1 To Data1.Database.TableDefs.Count
       List2.AddItem Data1.Database.TableDefs(i - 1).Name
   Next i

'Hope will help.

Original link: http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20134594
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7139601
Hi hge,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Accept ryancys's comment(s) as an answer.

hge, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7173277
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question