I was trying to build a small system/procedure using MS Access which will import excel file to my MS Access database. The field or column inside the Excel file is vary from file to file. So here I need to check if a field/column exist and then import the file into Access.

In order to check if a file exist, we can use a simple function which I call CheckExist function. Here is the code

[codesyntax lang=”vbnet”]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

Function CheckExists(ByValstrField AsString)AsBoolean

DimobjRecordset AsObject

DimiAsInteger

SetobjRecordset=CreateObject("ADODB.Recordset")

objRecordset.ActiveConnection=CurrentProject.Connection

objRecordset.Open("Excel_ITS_Assay_prelim")

'loop through table fields

Fori=0To objRecordset.Fields.Count-1

'check for a match

If strField=objRecordset.Fields.Item(i).Name Then

'exist function and return true

CheckExists=True

ExitFunction

EndIf

Nexti

'return false

CheckExists=False

EndFunction

[/codesyntax]

Then, we can use this function on other section to check if a field exist on a table