#external database

7. Pamper call us 1-866-304-3265, 1-269-605-4904, [email protected]. We say autochthon presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA and Canada nationwide and internationally via web sessions and phone conferences (Skype is welcomed)<\p>

Check If Field Exists in an External Access Database, VBA AutomationIn this article I will explain how you can check if a field exists in an external access database using VBA.Previously in the article below I’ve explained how you can get the name of all the fields in a table in an external access database using VBA:I will be using the code in that article here.Note: The examples used in this article are based on the following assumptions:The access database is located in the path “D:StuffBusinessTemp”.The name of the access database is “NewDB.accdb”.It is assumed the database has a table with the name “MyTable1”.Note: Although the code in this article was written in the Excel VBA editor, it can be used in any office application with VBA.–Late Binding:The function below receives as input the name of a table and field. It checks if the field exists in the table:Function  CheckExists1(ByVal  strTable As  String, _ByVal  strField As  String) As Boolean 'an Access object Dim  objAccess As Object Dim  objRecordset As Object Dim  i As Integer Set  objAccess = CreateObject("Access.Application")'open access database Call  objAccess.OpenCurrentDatabase( _"D:StuffBusinessTempNewDB.accdb")'get tables data Set  objRecordset = _objAccess.CurrentProject.Connection.Execute(strTable)'loop through table fields For  i = 0 To  objRecordset.Fields.Count - 1    'check for a match     If  objRecordset.Fields.Item(i).Name = strField Then         'exit function with the value True         CheckExists1 = True        Exit Function     End If Next  i'exit function with the value false CheckExists1 = FalseEnd Function It can be tested using the code below:Sub  test1()If  CheckExists1("MyTable1", "MyField1") = TrueThen     MsgBox ("Field Exists") Else     MsgBox ("Field Does not exist") End If End Sub Result:–Early Binding:In early binding you will need to add reference to the libraries below. This can be done in the VBA editor (Tools>>Reference):Microsoft Access Access Object LibraryMicrosoft ADO Ext. for DDL and SecurityNote: The versions of the libraries mentioned above installed on my computer might be different than what is installed on your computer. This will not effect the code.The function below receives as input the name of a table and field. It checks if the field exists in the table:Function  CheckExists2(ByVal  strTable As  String, _ByVal  strField As  String) As Boolean 'an Access object Dim  objAccess As  Access.ApplicationDim  objRecordset As  ADODB.RecordsetDim  i As Integer Set  objAccess = New Access.Application'open access database Call  objAccess.OpenCurrentDatabase( _"D:StuffBusinessTempNewDB.accdb")'get tables data Set  objRecordset = _objAccess.CurrentProject.Connection.Execute(strTable)'loop through table fields For  i = 0 To  objRecordset.Fields.Count - 1    'check for a match     If  objRecordset.Fields.Item(i).Name = strField Then         'exit function with the value True         CheckExists2 = True        Exit Function     End If Next  i'exit function with the value false CheckExists2 = FalseEnd Function It can be tested using the code below:Sub  test2()If  CheckExists2("MyTable1", "MyField1") = TrueThen     MsgBox ("Field Exists") Else     MsgBox ("Field Does not exist") End If End Sub Result:You can download the file and code used in this article from the link below:Note: Although the code was written in the Excel VBA Editor, it can be written in any other office application.See also:If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com