0

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

Erik A
  • 31,639
  • 12
  • 42
  • 67
user2937941
  • 37
  • 1
  • 1
  • 6
  • The DLookup function is specific to Access, I think. Can you show how you "built connections" to the Access databases from Excel? – Floris Nov 14 '13 at 13:32
  • Take a look at the code given in http://stackoverflow.com/questions/9083232/writing-excel-vba-to-receive-data-from-access - 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 Nov 14 '13 at 13:36
  • Thanks Floris. I updated my question. – user2937941 Nov 14 '13 at 13:47

1 Answers1

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.

Floris
  • 45,857
  • 6
  • 70
  • 122
  • 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. – user2937941 Nov 14 '13 at 15:16
  • You can simulate a TRY CATCH block in VBA Please see here http://stackoverflow.com/q/30991653/4413676 – HarveyFrench Jun 22 '15 at 23:50
  • @HarveyFrench I saw that - I don't think it is a particularly readable alternative to the above... – Floris Jun 22 '15 at 23:59
  • 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... – HarveyFrench Jun 23 '15 at 00:10
  • @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. – Floris Jun 23 '15 at 00:32