Avatar of GlobaLevel
GlobaLevel
Flag for United States of America asked on

vba excel...search 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
VB ScriptMicrosoft Access

Avatar of undefined
Last Comment
MikeToole

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
MikeToole

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GlobaLevel

ASKER
Mike,

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
MikeToole

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
to
     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:
    MyField='myValue'
For a number the quotes must be left out:
    MyField=myValue

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck