Link to home
Start Free TrialLog in
Avatar of GlobaLevel
GlobaLevelFlag for United States of America

asked on

vba an access .mdb to see if table exists, if table exists does a certain value in col

so here it is I need to search a table for a particular name, if that table name exists, then I need to search 'x' column for 'y' value....if tvalue doesnt exist return a message...


need to search access 2003 to see if a table exists, and if that table exists then does the certain registers exist....


I also need to search the table to see if the registers of the table are there...

if table x exist then search for x registers....I dont even know what registers are!!

from wike..Access Registers are hardware registers in the processor
Avatar of MikeToole
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GlobaLevel



I am getting a subscript out of range error with the following line, otherwise, it works like a gem...

I dont think that it can find the method .NZ....

If app.Nz(app.DLookup(field, TableName, "Cstr(" & field & ")='" & value & "'"), cNotFound) = cNotFound Then
Sorry, I missed your post somehow.

Nz() is definately a method of the Access application class -  you can verify this by a slight change in the code:
First, from the Tools menu choose Refernces and add a reference to the Microsoft Access xx.x Object Library (xx.x depends on the version installed)
Then, in the code change
     Dim app As Object
     Dim app As Access.Application

You'll then get Intellisense prompts on all the methods of the application object.

The only reason for declaring the variable as an object is that it then doesn't matter what version of Access is on any PC that the code runs on. Adding the reference ties the workbook code to the version in the reference.

The DLookUp() I coded will only work with Text fields in the table. If your target field might be some other type, e.g. Numeric or Date, you'll have to add logic to check the field type and format the third parameter accordingly.
For a string it's:
For a number the quotes must be left out:

I'm not sure how an Subscript out of Range error could be thrown