Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

checking if a database has certain tables or fields

Posted on 2006-05-27
10
Medium Priority
?
297 Views
Last Modified: 2010-04-30
Using just ADO (i don't want to have to include a new dll in my app) - how would i check if a local access database has:

1. certain tables; and
2. certain fields within existing tables?

If those fields or tables aren't found - how do i create them?

I basically want a routine that checks the local access database to see if its the latest version of that database.  If it can't find the tables and fields this version of the code needs - it'll add the fields and tables.

I know i can do some of this with raw SQL (i.e. creating new tables and adding fields to existing tables) - can i do it all with SQL?

how would you approach this?
0
Comment
Question by:kenshaw
  • 5
  • 4
10 Comments
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 1000 total points
ID: 16778730
What kind of database are you using?

There are a number of methods to checking if a Table (or a column) exists; some appropriate to Jet (e.g. MS-Access) databases, some to SQL Server.  Note that the Jet (MS-Access) method cannot use SQL statement syntax directly, but if using a SQL-Server database you can.

Taken from [ http://p2p.wrox.com/topic.asp?TOPIC_ID=38871 ] :

Public Function TableExists(strTableName As String) As Boolean
   
   On Error GoTo Errorhandler:

   TableExists = False

   'g_cnn is the global, default ADO connnection my app is
   'using to any OLE DB Provider (Jet, SQL Server, etc.).
   'OpenSchema returns a read-only, static cursor of db
   'schema (catalog) information. adSchemaTables specifies a schema
   'query that returns tables and views (or Jet saved queries).
   With g_cnn.OpenSchema(adSchemaTables)
      If (False = .BOF) And (False = .EOF) Then
         .MoveFirst
         Do While Not .EOF
            'TABLE_NAME is a column-name defined in the returned
            'TABLES rowset.
            If 0 = StrComp(.Fields("TABLE_NAME"), strTableName, vbTextCompare) Then
               TableExists = True
               Exit Do
            End If
            .MoveNext
         Loop
      End If
      .Close
   End With
   Exit Function

Errorhandler:
   Debug.Print "Error: " & Err.Number, Err.Description, "clsCreateTempTable.TableExists"
   Exit Function
   Resume
End Function



Test with:


Global g_cnn As ADODB.Connection

Sub Test()
   
   Dim blnTableExists As Boolean          
   Set g_cnn = New ADODB.Connection
   
   'Open unsecured Jet connection
   With g_cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Data Source") = "C:\Unsecured.mdb"
   End With
   
   'Open secured Jet connection
   'With g_cnn
   '   .Provider = "Microsoft.Jet.OLEDB.4.0"
   '   .Properties("Data Source") = "C:\SecuredDatabase.mdb"
   '   .Properties("Jet OLEDB:System Database") = "C:\WorkgroupInformationFile.mdw"
   '   .Properties("Mode") = adModeShareDenyNone
   '   .Properties("User ID") = "username"
   '   .Properties("Password") = "password"
   'End With

   'Open SQL Server connection
   'With g_cnn
   '   .Provider = "SQLOLEDB"
   '   .Properties("Data Source") = "(local)"
   '   .Properties("Initial Catalog") = "database"
   '   .Properties("User ID") = "sa"
   '   .Properties("Password") = "password"
   'End With

   g_cnn.Open
   
   blnTableExists = TableExists("tblRecords")
   
   
End Sub


===

Public Function JetTableExists(strTableName As String) As Boolean
   
   Dim strSQL As String
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   
   On Error GoTo Errorhandler:

   JetTableExists = False

   strSQL = "SELECT COUNT(*) FROM MSYSObjects WHERE Type = 1" & _
        " AND [Name]='" & strTableName & "'"
 
    Set rst = g_cnn.Execute(strSQL)
    JetTableExists = rst(0)
    rst.Close
    Set rst = Nothing
    Exit Function

Errorhandler:
   Debug.Print "Error: " & Err.Number, Err.Description, "clsCreateTempTable.TableExists"
   Exit Function
   Resume
End Function

===

Public Function SQLServerTableExists(strTableName As String) As Boolean
   
   Dim strSQL As String
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   
   On Error GoTo Errorhandler:

   SQLServerTableExists = False

   strSQL = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='" & strTableName & "'"
 
    Set rst = g_cnn.Execute(strSQL)
    If Not (rst.BOF And rst.EOF) Then SQLServerTableExists = True
    rst.Close
    Set rst = Nothing
    Exit Function

Errorhandler:
   Debug.Print "Error: " & Err.Number, Err.Description, "clsCreateTempTable.TableExists"
   Exit Function
   Resume
End Function

===

Function TableExists(strTableName As String) As Boolean
    Dim dbs As DAO.Database, tdf As DAO.TableDef

    On Error Resume Next
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    If Err = 3265 Then
        ' Table does not exist.
        TableExists = False
    Else
        ' Table exists.
        TableExists = True
    End If
    Err = 0
End Function

===

BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16778734
PS. This link may also be useful to you:

[ http://www.freevbcode.com/ShowCode.asp?ID=144 ]

Option Explicit
'**************
'* Procedure  * DBCatalog
'* Purpose    * Retrieve information on Access Tables
'* Parameters *
'* Remarks    * Be sure to set Project References to ADOX
'* Created    * 99/05/02 11:28:31 AM
'**************
'Properties of the Catalog
Private Catalog As ADOX.Catalog
Private Col     As ADOX.Column
Private Cols    As ADOX.Columns
Private Grp     As ADOX.Group
Private Grps    As ADOX.Groups
Private Ndx     As ADOX.Index
Private Ndxs    As ADOX.Indexes
Private Key     As ADOX.Key
Private Keys    As ADOX.Keys
Private Proc    As ADOX.Procedure
Private Procs   As ADOX.Procedures
Private Prop    As ADOX.Property
Private Props   As ADOX.Properties
Private Table   As ADOX.Table
Private Tables  As ADOX.Tables
Private User    As ADOX.User
Private Users   As ADOX.Users
Private View    As ADOX.View
Private Views   As ADOX.Views

Public Enum TblProps
    tblTempTable = 0
    tblValidationText = 1
    tblValidationRule = 2
    tblCacheLinkNamePassword = 3
    tblRemoteTableName = 4
    tblLinkProviderString = 5
    tblLinkDataSource = 6
    tblExclusiveLink = 7
    tblCreateLink = 8
    tblTableHiddenInAccess = 9
End Enum

Public Enum ColProps
    colAutoincrement = 0
    colDefault = 1
    colDescription = 2
    colNullable = 3
    colFixedLength = 4
    colSeed = 5
    colIncrement = 6
    colValidationText = 7
    colValidationRule = 8
    colIISNotLastColumn = 9
    colAutoGenerate = 10
    colOneBlobPerPage = 11
    colCompressedUnicode = 12
    colAllowZeroLength = 13
    colHyperlink = 14
End Enum
'####################
'# PUBLIC FUNCTIONS #
'####################
Public Function ColumnFormat(vTable As Variant, vCol As Variant) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    Set Col = Table.Columns(vCol)
    ColumnFormat = NumberFormat(Col.Type)
End Function

Public Function ColumnFormatStr(vTable As Variant, vCol As Variant) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    ColumnFormatStr = NumberFormatStr(Table.Columns(vCol).Type)
End Function

Public Function ColumnProperty(vTable As Variant, vCol As Variant, PropEnum As ColProps) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    Set Col = Table.Columns(vCol)
    If Col.Properties.Count > 0 Then
        ColumnProperty = Col.Properties(PropEnum).Value
    End If
End Function

Public Function ColumnType(vTable As Variant, vCol As Variant) As ADOX.DataTypeEnum
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    ColumnType = Tables(vTable).Columns(vCol).Type
End Function

Public Function CreateTmpTable(tmpTable As String, Optional CopyTable As String, Optional rsClone As ADODB.Recordset) As Boolean
    On Error GoTo ErrHandler
    'First thing we do is delete the table if it exists
    If TableExists(tmpTable) Then
        Tables.Delete tmpTable
    End If

    Set Table = New Table
    Set Table.ParentCatalog = Catalog

    With Table
        .Name = tmpTable
        .Keys.Append "pkID", adKeyPrimary
        Set Cols = Tables(CopyTable).Columns
        For Each Col In Cols
            .Columns.Append Col.Name, Col.Type, Col.DefinedSize
            .Columns.Item(Col.Name).Properties(colDescription).Value = Col.Properties(colDescription)
        Next Col
    End With
    Tables.Append Table
    CreateTmpTable = True
Exit Function
ErrHandler:
    CreateTmpTable = False
End Function

Public Function CreateDB()
    Select Case App.Title
        Case "GoldWorX": Create_GoldWorX
    End Select
End Function

Public Function Datashape(rsParent As ADODB.Recordset, _
                          tblParent As String, _
                          tblChild As String, _
                          fldParent As String, _
                          fldChild As String, _
                 Optional idParent As Variant = "") As Boolean
    '=========================================================
    'This function will return a DisConnected SHAPEd RecordSet
    'Assumptions:
    '
    '
    '=========================================================
    On Error GoTo LocalError
    Dim lSQL        As String
    Dim lcnADO      As New ADODB.Connection
    Dim lrsParent   As New ADODB.Recordset

    With lcnADO
        .Mode = adModeShareDenyNone
        .CursorLocation = adUseServer
        .Provider = "MSDataShape"
        .Open CNX
    End With
    'Define the SQL Statement
    lSQL = ""
    If idParent <> "" Then
        lSQL = "SHAPE  {select * from [@PARENT] WHERE [@PARENTFIELD] = " & idParent & "} " & vbCrLf
    Else
        lSQL = "SHAPE  {select * from [@PARENT]} " & vbCrLf
    End If
    lSQL = lSQL & "APPEND ({select * from [@CHILD]} RELATE [@PARENTFIELD] TO [@CHILDFIELD]) AS ChildItems"
    'Replace parsed parameters in the command string
    lSQL = Replace(lSQL, "[@PARENT]", tblParent)
    lSQL = Replace(lSQL, "[@PARENTFIELD]", fldParent)
    lSQL = Replace(lSQL, "[@CHILD]", tblChild)
    lSQL = Replace(lSQL, "[@CHILDFIELD]", fldChild)
    'Get the data
    rsParent.StayInSync = False
    rsParent.Open lSQL, lcnADO, adOpenStatic, adLockOptimistic
    Set rsParent.ActiveConnection = Nothing
    lcnADO.Close
    Set lcnADO = Nothing
    Datashape = True
Exit Function
LocalError:
    RaiseError Err.Number, Err.Source & " (DBCatalog.DataShape)", Err.Description
End Function

Public Function DSNFile() As String
    DSNFile = CNX
End Function

Public Function IsKey(vTable As Variant, vCol As Variant) As Boolean
    'On Error Resume Next
    Dim KeyCol
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    If Not ColumnExists(vCol) Then Exit Function    'No point in continuing
    'Check for Index property - Access Primary Key is ALWAYS Indexed
    For Each Ndx In Table.Indexes
        If Ndx.PrimaryKey Then
            For Each Col In Ndx.Columns
                If Col.Name = vCol Then
                    IsKey = True
                    Exit Function
                End If
            Next Col
        End If
    Next Ndx
End Function

Public Function TableProperty(vTable As Variant, PropEnum As TblProps) As Variant
    If Not TableExists(vTable) Then Exit Function   'No point in continuing
    Set Table = Tables(vTable)
    TableProperty = Table.Properties(PropEnum).Value
    Set Table = Nothing
End Function

Public Function ADOTables() As ADOX.Tables
    Set ADOTables = Tables
End Function

'#####################
'# PRIVATE FUNCTIONS #
'#####################
Private Function Create_GoldWorX() As Boolean
    'set the Table Names
    Const Addresses    As String = "Addresses"
    Const Categories   As String = "Categories"
    Const Contacts     As String = "Contacts"
    Const Countries    As String = "Countries"
    Const CreditCards  As String = "CreditCards"
    Const Products     As String = "Products"
    Const SystemInfo   As String = "SystemInfo"
    Const Transactions As String = "Transactions"
    Const TRXitems     As String = "TRXitems"

    Catalog.Tables.Append Addresses
    Set Table = Tables("Addresses")
    With Table
        '.Columns.Append "pkID", adNumeric
        '.Columns("pkID").Attributes
    End With
End Function

Private Function NumberFormat(ColType As ADODB.DataTypeEnum) As String
    Select Case ColType
        Case adEmpty                                                '  0 - No value was specified (DBTYPE_EMPTY).
        Case adSmallInt:         NumberFormat = "General Number"    '  2 - A 2-byte signed integer (DBTYPE_I2).
        Case adInteger:          NumberFormat = "General Number"    '  3 - A 4-byte signed integer (DBTYPE_I4).
        Case adSingle:           NumberFormat = "General Number"    '  4 - A single-precision floating point value (DBTYPE_R4).
        Case adDouble:           NumberFormat = "General Number"    '  5 - A double-precision floating point value (DBTYPE_R8).
        Case adCurrency:         NumberFormat = "Currency"          '  6 - A currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
        Case adDate:             NumberFormat = "General Date"      '  7 - A Date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
        Case adBSTR                                                 '  8 - A null-terminated character string (Unicode) (DBTYPE_BSTR).
        Case adIDispatch                                            '  9 - A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
        Case adError                                                ' 10 - A 32-bit error code (DBTYPE_ERROR).
        Case adBoolean:          NumberFormat = "True/False"        ' 11 - A Boolean value (DBTYPE_BOOL).
        Case adVariant                                              ' 12 - An Automation Variant (DBTYPE_VARIANT).
        Case adIUnknown                                             ' 13 - A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
        Case adDecimal:          NumberFormat = "Standard"          ' 14 - An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
        Case adTinyInt:          NumberFormat = "General Number"    ' 16 - A 1-byte signed integer (DBTYPE_I1).
        Case adUnsignedTinyInt:  NumberFormat = "General Number"    ' 17 - A 1-byte unsigned integer (DBTYPE_UI1).
        Case adUnsignedSmallInt: NumberFormat = "General Number"    ' 18 - A 2-byte unsigned integer (DBTYPE_UI2).
        Case adUnsignedInt:      NumberFormat = "General Number"    ' 19 - A 4-byte unsigned integer (DBTYPE_UI4).
        Case adBigInt:           NumberFormat = "General Number"    ' 20 - An 8-byte signed integer (DBTYPE_I8).
        Case adUnsignedBigInt:   NumberFormat = "General Number"    ' 21 - An 8-byte unsigned integer (DBTYPE_UI8).
        Case adGUID                                                 ' 72 - A globally unique identifier (GUID) (DBTYPE_GUID).
        Case adBinary                                               '128 - A binary value (DBTYPE_BYTES).
        Case adChar                                                 '129 - A String value (DBTYPE_STR).
        Case adWChar                                                '130 - A null-terminated Unicode character string (DBTYPE_WSTR).
        Case adNumeric:          NumberFormat = "General Number"    '131 - An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
        Case adUserDefined                                          '132 - A user-defined variable (DBTYPE_UDT).
        Case adDBDate:           NumberFormat = "General Date"      '133 - A date value (yyyymmdd) (DBTYPE_DBDATE).
        Case adDBTime:           NumberFormat = "Long Time"         '134 - A time value (hhmmss) (DBTYPE_DBTIME).
        Case adDBTimeStamp:      NumberFormat = "General Date"      '135 - A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
        Case adVarChar                                              '200 - A String value (Parameter object only).
        Case adLongVarChar                                          '201 - A long String value (Parameter object only).
        Case adVarWChar                                             '202 - A null-terminated Unicode character string (Parameter object only).
        Case adLongVarWChar                                         '203 - A long null-terminated string value (Parameter object only).
        Case adVarBinary                                            '204 - A binary value (Parameter object only).
        Case adLongVarBinary                                        '205 - A long binary value (Parameter object only).
    End Select
End Function

Private Function NumberFormatStr(ColType As ADODB.DataTypeEnum) As String
    Select Case ColType
        Case adEmpty:            NumberFormatStr = "Empty"              '  0 - No value was specified (DBTYPE_EMPTY).
        Case adSmallInt:         NumberFormatStr = "SmallInt"           '  2 - A 2-byte signed integer (DBTYPE_I2).
        Case adInteger:          NumberFormatStr = "Integer"            '  3 - A 4-byte signed integer (DBTYPE_I4).
        Case adSingle:           NumberFormatStr = "Single"             '  4 - A single-precision floating point value (DBTYPE_R4).
        Case adDouble:           NumberFormatStr = "Double"             '  5 - A double-precision floating point value (DBTYPE_R8).
        Case adCurrency:         NumberFormatStr = "Currerncy"          '  6 - A currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
        Case adDate:             NumberFormatStr = "Date"               '  7 - A Date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
        Case adBSTR:             NumberFormatStr = "BSTR"               '  8 - A null-terminated character string (Unicode) (DBTYPE_BSTR).
        Case adIDispatch:        NumberFormatStr = "IDispatch"          '  9 - A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
        Case adError:            NumberFormatStr = "Error"              ' 10 - A 32-bit error code (DBTYPE_ERROR).
        Case adBoolean:          NumberFormatStr = "Boolean"            ' 11 - A Boolean value (DBTYPE_BOOL).
        Case adVariant:          NumberFormatStr = "Variant"            ' 12 - An Automation Variant (DBTYPE_VARIANT).
        Case adIUnknown:         NumberFormatStr = "Unknown"            ' 13 - A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
        Case adDecimal:          NumberFormatStr = "Decimal"            ' 14 - An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
        Case adTinyInt:          NumberFormatStr = "TinyInt"            ' 16 - A 1-byte signed integer (DBTYPE_I1).
        Case adUnsignedTinyInt:  NumberFormatStr = "UnsignedTinyInt"    ' 17 - A 1-byte unsigned integer (DBTYPE_UI1).
        Case adUnsignedSmallInt: NumberFormatStr = "UnsignedSmallInt"   ' 18 - A 2-byte unsigned integer (DBTYPE_UI2).
        Case adUnsignedInt:      NumberFormatStr = "UnsignedInt"        ' 19 - A 4-byte unsigned integer (DBTYPE_UI4).
        Case adBigInt:           NumberFormatStr = "BigInt"             ' 20 - An 8-byte signed integer (DBTYPE_I8).
        Case adUnsignedBigInt:   NumberFormatStr = "UnsignedBigInt"     ' 21 - An 8-byte unsigned integer (DBTYPE_UI8).
        Case adGUID:             NumberFormatStr = "GUID"               ' 72 - A globally unique identifier (GUID) (DBTYPE_GUID).
        Case adBinary:           NumberFormatStr = "Binary"             '128 - A binary value (DBTYPE_BYTES).
        Case adChar:             NumberFormatStr = "Char"               '129 - A String value (DBTYPE_STR).
        Case adWChar:            NumberFormatStr = "WChar"              '130 - A null-terminated Unicode character string (DBTYPE_WSTR).
        Case adNumeric:          NumberFormatStr = "Numeric"            '131 - An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
        Case adUserDefined:      NumberFormatStr = "UserDefined"        '132 - A user-defined variable (DBTYPE_UDT).
        Case adDBDate:           NumberFormatStr = "DBDate"             '133 - A date value (yyyymmdd) (DBTYPE_DBDATE).
        Case adDBTime:           NumberFormatStr = "DBTime"             '134 - A time value (hhmmss) (DBTYPE_DBTIME).
        Case adDBTimeStamp:      NumberFormatStr = "DBTimeStamp"        '135 - A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
        Case adVarChar:          NumberFormatStr = "VarChar"            '200 - A String value (Parameter object only).
        Case adLongVarChar:      NumberFormatStr = "LongVarChar"        '201 - A long String value (Parameter object only).
        Case adVarWChar:         NumberFormatStr = "VarWChar"           '202 - A null-terminated Unicode character string (Parameter object only).
        Case adLongVarWChar:     NumberFormatStr = "LongVarWChar"       '203 - A long null-terminated string value (Parameter object only).
        Case adVarBinary:        NumberFormatStr = "VarBinary"          '204 - A binary value (Parameter object only).
        Case adLongVarBinary:    NumberFormatStr = "LongVarBinary"      '205 - A long binary value (Parameter object only).
        Case Else:               NumberFormatStr = "Ask Bill Gates"
    End Select
End Function

Private Function SetCatalog() As ADOX.Catalog
    'Retrieves the description of the field
    'Cat.Tables(1).Columns(1).Properties(2).Value
    'Set DBCatalog = Cat
    'Set Cat = Nothing
    If Not Catalog Is Nothing Then
    End If
End Function

Private Function TableExists(TableName) As Boolean
    On Error GoTo ErrHandler
    Set Table = Tables(TableName)
    TableExists = True
Exit Function
ErrHandler:
    TableExists = False
End Function

Private Function ColumnExists(ColName) As Boolean
    On Error GoTo ErrHandler
    Set Col = Table.Columns(ColName)
    ColumnExists = True
Exit Function
ErrHandler:
    ColumnExists = False
End Function

Private Sub Class_Initialize()
    'Create the Catlog
    Set Catalog = New ADOX.Catalog
    Catalog.ActiveConnection = CNX
    Set Tables = Catalog.Tables
    Set Users = Catalog.Users
    Set Views = Catalog.Views
    Set Procs = Catalog.Procedures
    Set Grps = Catalog.Groups
End Sub

Private Sub Class_Terminate()
    Set Col = Nothing
    Set Cols = Nothing
    Set Grp = Nothing
    Set Grps = Nothing
    Set Ndx = Nothing
    Set Ndxs = Nothing
    Set Key = Nothing
    Set Keys = Nothing
    Set Proc = Nothing
    Set Procs = Nothing
    Set Prop = Nothing
    Set Props = Nothing
    Set Table = Nothing
    Set Tables = Nothing
    Set User = Nothing
    Set Users = Nothing
    Set View = Nothing
    Set Views = Nothing
    Set Catalog = Nothing
End Sub

===

BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16778779
PPS.

Dim blnFound_Column As Boolean
Dim objDatabase As Object
Dim objField As Object

Set objDatabase = OpenDatabase("Database name etc", False, False)

blnFound_Column = False

For Each objField In objDatabase.TableDefs("YourTableName").Fields

    If UCase$(objField) = "THE_UPPERCASE_NAME_OF_THE_COLUMN" Then
       blnFound_Column = True
       Exit For
    End If

Next objField

If (blnFound_Column) Then
   MsgBox "[Column] Column found in Table [YourTableName]"
End If


BFN,

fp.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:kenshaw
ID: 16778945
wow - ok.

I'm using Access.  The second post you made uses ADOX - which i don't want to use.  I'm pretty certain I can use posts 1 and 3 to check for tables and fields respecitvely in access without using any extra references.  right?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16780110
Yup... both posts #1 & #3 should meet your requirements.

Have you had the opportunity to test either?

To check if a table exists in Access you can also query the [MSysObjects] (hidden/system) table:

SELECT [Name] FROM [MSysObjects] WHERE [Type] In (1,6) AND [Flags] In (0, 2097152) And [Name] = "Name_Of_Table_Required"

If "Name_Of_Table_Required" is found, the resultant recordset with contain a recordcount = 1, otherwise the resultset will be empty.

Additionally...

Dim blnFound_Table As Boolean
Dim objDatabase As Object

Set objDatabase = OpenDatabase("Database name etc", False, False)

On Error Resume Next

blnFound_Table = objDatabase.TableDefs("YourTableName").Name

If (blnFound_Table) And _
   (Err.Number = 0&) Then
   MsgBox "[Table] table exists"
End If

On Error GoTo 0 ' (or) On Error GoTo <label>

' Processing continues...



BFN,

fp.
0
 
LVL 28

Accepted Solution

by:
Ark earned 1000 total points
ID: 16788316
Public Function TableExists(oConn As ADODB.Connection, ByVal TableName As String) As Boolean
   Dim rst As ADODB.Recordset
   Set rst = oConn.OpenSchema(adSchemaTables, Array(Empty, Empty, TableName, Empty))
   TableExists = Not rst.EOF
   rst.Close
   Set rst = Nothing
End Function

Regards
Ark
0
 
LVL 28

Expert Comment

by:Ark
ID: 16788327
Public Function FieldExists(ByVal TableName As String, ByVal FieldName As String) As Boolean
   Dim strSQL As String
   Dim rst As ADODB.Recordset

   strSQL = "SELECT " & FieldName & " FROM " & TableName
   Set rst = mCN.Execute(strSQL, , adCmdText)
   FieldExists = Not rst.EOF
   rst.Close
   Set rst = Nothing
End Function


Public Function RecordExists(ByVal TableName As String, ByVal FieldName As String, ByVal varValue As Variant) As Boolean
   Dim strSQL As String, strCriteria As String
   Dim rst As ADODB.Recordset
   Select Case VarType(varValue)
      Case vbString, vbDate
         strCriteria = " = '" & varValue & "'"
      Case vbInteger, vbLong, vbSingle, vbDouble, vbByte, vbCurrency
         strCriteria = " = " & varValue
      Case Else
   End Select

   strSQL = "SELECT " & FieldName & " FROM " & TableName & _
            " WHERE " & FieldName & strCriteria
   Set rst = mCN.Execute(strSQL, , adCmdText)
   RecordExists = Not rst.EOF
    rst.Close
   Set rst = Nothing
End Function
0
 
LVL 28

Expert Comment

by:Ark
ID: 16788333
Oops, forget - mCN is my Public connection name
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16796194
...you'd also probably need to error-handling inside those routines too, Ark.
0
 
LVL 28

Expert Comment

by:Ark
ID: 16796325
The only place where error can occure is
rst.Close
Set rst = Nothing

Actually, I have

Public Sub CloseRS(rs As ADODB.Recordset)
    On Error Resume Next
    If Not rs Is Nothing Then
       If rs.State = adStateOpen Then rs.Close
       Set rs = Nothing
    End If
End Sub

So, I always use

CloseRS rst

instead of this 2 strings

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

580 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