If table not in Access database from Excel VBA

Question

How would I be able to check whether or not a given table (called Items) is in a specific Access database through Excel vba.

I have built connections from the excel sheet to two different databases, [A] & [B], through Excel vba and my other code is working fine.

So far, the closest I have been able to find online is:

If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='Items'")) Then

This code doesn't specify which database I would like to search. Is there a way I could write a statement to run only if table Items is not found in Database [B]? How would this code be written?

I have no problem referencing the database. Most of my code is SQL that is being run from Excel, and I'm able to referenece various entries in fields specific to each database. I'm just looking for a line that says "If this table doesn't exist in this database, then create a table with that name". Is there maybe an SQL string I could write, or even a try...catch method?

Any help would be greatly appreciated

The DLookup function is specific to Access, I think. Can you show how you "built connections" to the Access databases from Excel? — Floris
– Floris, Commented Nov 14, 2013 at 13:32
Take a look at the code given in stackoverflow.com/questions/9083232/… - it shows how Excel references a specific database (and there is no reason why you could not have two databases with that method). But it doesn't use DLookup... — Floris
– Floris, Commented Nov 14, 2013 at 13:36

Floris · Accepted Answer · 2013-11-14 14:55:34Z

0

You can probably use the On Error technique (it is a bit like try...catch, except the latter doesn't exist in VBA). For instance:

On Error GoTo whoa
Set db = OpenDatabase(DBFullName) ' use the name of [A] or [B] here.
Set rs = db.OpenRecordset( *** your expression to get something from db *** )
' if you get past this line, then reading the data went OK
MsgBox "Everything is fine"
' it is possible that the above will not throw an error, but returns something "empty"
' I would put a breakpoint in the code and step through, then examine `rs`
' both for the case where the table exists, and where it doesn't
Exit Function
whoa:
MsgBox "Something went horribly wrong: error is " & Err.Description
' this is where you would go if you were not able to get the data

Clearly you would do this for both [A] and [B] to figure out where the table exists (if at all). I don't have Access on my machine so I can't easily test this for you, sorry.

answered Nov 14, 2013 at 14:55

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2937941 Over a year ago

Thanks a lot, Floris! I modified this slightly so that the code tries to create the table. If creation doesn't work, then resume the code assuming that the table exists. It all works.

HarveyFrench Over a year ago

You can simulate a TRY CATCH block in VBA Please see here stackoverflow.com/q/30991653/4413676

Floris Over a year ago

@HarveyFrench I saw that - I don't think it is a particularly readable alternative to the above...

HarveyFrench Over a year ago

I commented as you can kind of do try catch in vba with a bit of effort... thought you might find it of use sometime...

Floris Over a year ago

@HarveyFrench thanks for pointing it out. Good code is always balancing between robustness, readability, and elegance. Alternatives are always good to have in your back pocket I suppose.

Collectives™ on Stack Overflow

If table not in Access database from Excel VBA

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related