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.
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!