VBA global variables and Access databases

Question

I'm trying to get data from a database using global variables like this...

In ThisWorkBook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DB.Close
End Sub

Private Sub Workbook_Open()
DBfile = Application.ThisWorkbook.Path & "\mydatabase.accdb"
Set DB = DBEngine.OpenDatabase(DBfile)
End Sub

IN MODULE1:

Option Explicit
Global DB As DAO.Database
Global RS As DAO.Recordset
Global DBfile As Variant

Public Function GetDataFromDB(TBL As String, COLMN As String, DTCLN As String, DT As String) As Variant

Set RS = DB.OpenRecordset("SELECT " & COLMN & " FROM " & TBL & " WHERE " & DTCLN & " = #" & Format(DT, "m\/d\/yyyy") & "#", dbOpenDynaset)

If RS.RecordCount > 0 Then
    RS.MoveFirst
    GetDataFromDB = RS(COLMN)
End If

End Function

On Set RS = DB.OpenRecordset ~ I get an error '91'.

Everything works fine when I open the database within the function, but I have a lot of functions like this, so if I open the database with every function Excel will work slower

Can anyone help, please.

Try watch in debugger is there 'DB' empty or not. Cause after error vba reset all global variables. You might get some error before GetDataFromDB running so DB is not set — brz
– brz, Commented Jul 12, 2015 at 6:02
Thanks for your answer. The problem was that Sub Workbook_Open hasn't been started,. When I started it , everything worked. ))) — Gedeon
– Gedeon, Commented Jul 12, 2015 at 6:09
i'm not figure how you do that. Is workbook_open run automatic when excel open file? But if it's help - good one, but be careful with global vars — brz
– brz, Commented Jul 12, 2015 at 6:15
I think it has been turned off somehow (maybe after error), so it worked when I run it again. — Gedeon
– Gedeon, Commented Jul 12, 2015 at 6:28

Excel Hero · Accepted Answer · 2015-07-12 05:57:05Z

1

DBEngine is an Access VBA method. It does not exist in Excel VBA.

You therefore cannot use it in Workbook_Open().

You get error 91 because the DB object variable is not set.

answered Jul 12, 2015 at 5:57

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

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

5 Comments

Gedeon Over a year ago

Thanks for your answer . how to do that then ?

Excel Hero Over a year ago

Use ADO from Excel VBA to connect to the database.

Gedeon Over a year ago

I think ADO is a bit slower than DAO. I have already managed the problem. But thanks again for your answer. )

Excel Hero Over a year ago

From Excel you will not see a difference in speed between ADO and DAO, but ADO is significantly newer technology and offers an upgrade path, while DAO does not. At any rate, if my answer helped, please accept it.

Gedeon Over a year ago

OK. Which one will work on MAC OS ? I'm going to test my code on mac, so I just don't know if any of these will work on it.

Collectives™ on Stack Overflow

VBA global variables and Access databases

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related