CODEWITHSUNDEEP
excelvbadatabasems-access
Mister Equis
Mister Equis

Reputation: 141

Struggling to open access database from excel - error 91

I'm struggling opening a database from excel.

I'm want to retrieve some information and populate comboboxes.

I tried pasting the code in a standard module, and running it inside the userform module.

I run the code when the userform initializes. First the code:

Dim dbe As DAO.DBEngine
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qry As String

On Error GoTo ERR_BBDD

'This is the line where the error is thrown.
Set db = dbe.OpenDatabase("E:\myDB.mdb")

I get error 91 "variable or with block not defined blah blah".

I checked if the .mdb file path is ok and it is. The code is really simple but I must be missing something and can't find a reason for it to be failing, since as I read in all the docs and the internet, my code should work fine.

Upvotes: 0

Views: 149

Answers (1)

S. MacKenzie
S. MacKenzie

Reputation: 335

It looks like you're trying to open Access (for DAO) as you would from inside of an Access application instance, but you're in Excel. I don't think Excel has a DBEngine for you to call. You could open Access as a new Access.Application instance, then do your operations, but it will literally open an Access instance in the background which is not really efficient for a few lookups.

Instead I would suggest creating an ADODB connection, load your lookups, then close it.

Set objCnn = CreateObject("ADODB.Connection")
objCnn.Provider = "Microsoft.Jet.OLEDB.4.0"
objCnn.Open strDir & "\MyDatabase.mdb"
Set rst = CreateObject("ADODB.Recordset")
rst.Open strSQL, objCnn, adOpenStatic

Do Until rst.EOF

    ... do some lookup loading stuff here

    rst.MoveNext
Loop
rst.Close

Hope that works!

Upvotes: 2

Related Questions